Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to 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!
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.