Conditional column multiple nested IFs

[Anonymous]

Hello,

I have the following table loaded in Power Query. I am trying to add a new column "Result" which would have specific text values based on the following conditions:

1. If Action date is null, then if "End date" is greater than or equal to "Today", Result value is "Not Delivered - On Time".

2. If Action date is null, then if "End date" is less than "Today", Result value is "Not Delivered - Late".

3. If Action date is not null, then if "End date" is greater than or equal to "Action date", Result value is "Delivered - On Time".

4. If Action date is not null, then if "End date" is less than "Action date", Result value is "Delivered - Late".

5. Otherwise, Result is "Undefined".

End date	Action date	Today
01/06/2022	01/07/2022	02/10/2022
27/06/2022	27/06/2022	02/10/2022
07/09/2022	07/08/2022	02/10/2022
11/08/2022		02/10/2022
08/10/2022		02/10/2022
20/09/2022	22/09/2022	02/10/2022
27/09/2022	23/08/2022	02/10/2022
10/11/2022		02/10/2022
02/09/2022	22/09/2022	02/10/2022
09/10/2022	02/10/2022	02/10/2022
15/10/2022		02/10/2022
17/09/2022		02/10/2022
06/10/2022	19/09/2022	02/10/2022
23/11/2022	15/11/2022	02/10/2022
02/12/2022	23/12/2022	02/10/2022
15/01/2023		02/10/2022
04/03/2023	17/09/2022	02/10/2022
13/03/2023	01/09/2022	02/10/2022
15/01/2023		02/10/2022

But i am not sure why this code in a custom column is not working. Any help is much appreciated!

if ([Action date] = null and [End date] >= [Today]) then "Not Delivered - On Time" else 
if ([Action date] = null and [End date] < [Today]) then "Not Delivered - Late" else 
if ([Action date] <> null and [End date] >= [Action date]) then "Delivered - On Time" else 
if ([Action date] <> null and [End date] < [Action date]) then "Delivered - Late" else "Undefined"

[jbwtp]

Hi @Anonymous,

It seems to work as intended when I tested it. May be check that null is null and not "null" (text) in your table?

Cheers,

John

[Anonymous · Accepted solution]

HI @jbwtp Thanks for your reply! I have updated my first post to remove the "null" text. I only put it there to make it clear but it is not included in the actual table.

The date columns comparison somehow works even if the data type is text. I double-checked and the results are correct. I was confused with all the comparisons. Thanks for confirming!