Power Query Merge Columns to Remove Nulls-CodePudding

I am new to Power Query Editor, and I am having trouble with one statement.

I need an IF nested statement, where if a Column contains Null, THEN copy the next one over; if that column is Null, copy the next one over.

So far, I have and it's not working.

= Table.AddColumn(#"Removed Other Columns", "Custom", each if [Date3] = null then [Date2] else if [Date2] = null then [Date1] else if [Date1] = null then [Date3] else [Date3])

What I got

Date1	Date2	Date3	Custom
4/18/2023	Null	Null	Null
4/18/2023	2/18/2023	Null	2/18/2023
Null	2/18/2023	1/18/2023	1/18/2023
Null	Null	1/18/2023	Null

What I need

Date1	Date2	Date3	Custom
4/18/2023	Null	Null	4/18/2023
4/18/2023	2/18/2023	Null	2/18/2023
Null	2/18/2023	1/18/2023	1/18/2023
Null	Null	1/18/2023	1/18/2023

I tried different IF solutions, and I even used the Conditional Column, but that didn't work. Please help. Thank you!

CodePudding user response：

Note that if you want to display markdown tables in your question make sure you have blank lines before and after and that Null is null in PQ.

The logic of your nested if doesn't work: ´if [Date3] = null then [Date2]´ stops the execution of the remaining line.

Try this one instead:

#"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "Custom", 
    each if [Date3] <> null then [Date3] 
         else if [Date2] <> null then [Date2] 
         else [Date1]
)

CodePudding user response：

Use the coalesce operator. e.g.

= Table.AddColumn(#"Removed Other Columns", "Custom", each [Date3] ?? [Date2] ?? [Date1])

https://gorilla.bi/power-query/coalesce/