Merge two columns into one column based on two condition

[ClemFandango]

Hi there,

I am hoping someone can help me with a conditional column that I have created in Power Query. I have the following table (called This Month) and I am trying to combine the first two columns and create the conditional column.

The logic is that the ConditionalColumn = Active.a column, however if Active.a column = null then the ConditionalColumn = Inactive.b column.

Please see my example below:-

This Month

Table1 (Active a).AC Cust Num	Table1 (Inactive b).AC Cust Num	ConditionalColumn
5	5	5
5	4	5
null	3	3
null	null	null
null	1	1

I have used the following in the ConditionalColumn:-

= Table.AddColumn(#"Removed Duplicates1", "Custom.2", each if Text.From([[#"Table1 (Active a).AC Cust Num"]])<> null then [#"Table1 (Active a).AC Cust Num"]
else if Text.From([[#"Table1 (Active a).AC Cust Num"]])= null then [#"Table1 (Inactive b).AC Cust Num"]
else "")

However this results in the error message ‘We cannot convert a value of type Record to type Text’. All columns are currently formatted as text as it may include alphanumeric characters in the future.

Any ideas on how I can merge the values from two columns into one will be greatfully received!

Many thanks

CF

[m_dekorte · Accepted solution]

Hi @ClemFandango,

When dealing with null (the absense of a value) you can use coalesce (??), to illustrate:

Table.AddColumn(#"Replaced Value", "Custom", each [#"Table1 (Active a).AC Cust Num"] ?? [#"Table1 (Inactive b).AC Cust Num"])

The error you reported is most likely due to the double set of square brackets within your syntax.

[ClemFandango]

Amazing - @m_dekorte you are my hero!