Conditional columns and null values

[DavidS-RI]

I am transforming data from Dataverse, and I have rows that look like this:

Field1	Field2
ABC	null
null	DEF
GHI	null

I want to create a new column to combine these two:

Field1	Field2	Field3
ABC	null	ABC
null	DEF	DEF
GHI	null	GHI

The Conditional Column "wizard" doesn't have a "contains data" or "does not contain data" option available, so I try using the equals condition with a blank value. The resulting formula for my column becomes:

=Table.AddColumn(#"PreviousStepName", "Field3," each if [Field1] <> "" then [Field1] else [Field2])

That seems simple enough but in practice I get unexpected results, with different .pbix files generating different outcomes. Sometimes the above formula works, but sometimes the result for Field3 is null and sometimes it's Error. I've had some luck replacing if [Field1] <> "" with if Text.Length([Field1]) >0, but that doesn't always seem to work either.

What is the best way to say "set Field 3 to the value of Field 1, unless Field 1 has a null value, in which case use the value of Field 2?"

In case it matters, the fields in question are not coming from the same table in the Dataverse. My queries start with one table that's related to two other tables. In previous steps within my query, I expand those two related tables, selecting Field1 from RelatedTable1 and Field2 from RelatedTable2. Sometimes I maintain the source table name as a prefix when I expand it, sometimes I don't. Could that inconsistency explain why my conditional column formula produces inconsistent results? 

[AlienSx]

@DavidS-RI 

Table.AddColumn(#"PreviousStepName", "Field3", each [Field1] ?? "" & [Field2] ?? "")

[Anonymous]

Hi @DavidS-RI 

You can use List.Max() function, you can create a custom column.

List.Max({[Field1],[Field2]})

 Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DavidS-RI]

This did not work for me. The value of the custom column was Error for all rows.

[DavidS-RI]

Your first option does not work. "null" in quotation marks seems to look for the actual word null as a text value, but null without quotation marks seems to check for the absence of any data, as intended. So your second option seems to work, at least in my first test of this. I wonder why the GUI inserts "" into the formula rather than null. 

[PijushRoy]

Hi @DavidS-RI 

I have inserted "" in the last, please change it if required.


If solved your requirement, please mark this answer as SOLUTION.
If this comment helps you, appreciate your KUDOS

[PijushRoy]

Hi @DavidS-RI 

Please try this in custom column in PQ

if [Field1]="null" and [Field2]<>"null" then [Field2] else if [Field1]<>"null" and [Field2]="null" then [Field1] else ""

or 

if [Field1]=null and [Field2]<>null then [Field2] else if [Field1]<>null and [Field2]=null then [Field1] else ""

If solved your requirement, please mark this answer as SOLUTION.
If this comment helps you, appreciate your KUDOS

Pijush