Combining two Columns in Power Query Based on a Condition on another column

[ReDDozer]

Hi all,

the following code combines 2 columns ColumnB1 and ColumnB2 based on the condition if ColumnB1 = null then ColumnB2 else ColumnB1:

Table.CombineColumns ( PreviousStep,{"ColumnB1", "ColumnB2"}, each if _{0} = null then _{1} else _{0}, "ColumnB")

is there a way to edit the code in order the condition to be based on another column, not part of the merge? if ColumnA = null then ColumnB2 else ColumnB1.

Thank you

 

[JEC]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    nCol = Table.AddColumn(Source, "Merged", each if [ColumnA] = null then [ColumnB2] else [ColumnB1]),
    dCols = Table.RemoveColumns(nCol,{"ColumnB1", "ColumnB2"})
in
    dCols

Book1
	A	B	C	D	E	F
1	ColumnA	ColumnB1	ColumnB2		ColumnA	Merged
2	a	e	h		a	e
3		f	i			i
4		g	j			j
5	d	e	k		d	e
Sheet1

 

[ReDDozer]

Hi JET,

thank you for your response.

I was hoping there was a way to solve the problem in two steps via the Table.CombineColumns function and without the need to add an extra column.

can you confirm that it is not possible to solve it with Table.CombineColumns?

thank you

 

[JEC]

I don't think it's possible with just using CombinColumns.
Like below you won't notice the extra step. Although I like your urge to do it with less steps/code. Motto: More with less

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    dCols = Table.RemoveColumns(Table.AddColumn(Source, "Merged", each if [ColumnA] = null then [ColumnB2] else [ColumnB1]),{"ColumnB1", "ColumnB2"})
in
    dCols

 

[ReDDozer]

Great, thanks for the feedback!
eheheh yes, "do more with less" it's definitely my motto!
Cheers

 

[JEC]

You're welcome!

 

[JGordon11]

Here's a way using Table.CombineColumns. I don't think it has any advantage over JEC's method though.

let
    t = Table.FromRows({{"a","e","h"},{null,"f","i"},{null,"g","j"},{"d","e","k"}}, {"ColumnA","ColumnB1","ColumnB2"}),
    Result = Table.FromRecords(Table.CombineColumns(t, Table.ColumnNames(t), (x)=> [ColumnA = x{0}, Merged = if x{0} = null then x{2} else x{1}], "r")[r])
in
    Result

 

[ReDDozer]

Awesome! thank you JGordon11

 

[JEC]

Nice though!