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.
#"Added Custom" = 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!
Use the coalesce operator. e.g.
= Table.AddColumn(#"Removed Other Columns", "Custom", each [Date3] ?? [Date2] ?? [Date1])