Forum Discussion

ClemFandango's avatar
ClemFandango
Advocate II
2 years ago
Solved

Merge two columns into one column based on two condition

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     
555
545
null33
nullnullnull
null11

 

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

 

 

Need Help
Show and Tell
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    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.

2 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    2 years ago

    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.