Forum Discussion
Conditional custom column based on dates comparison
Hello,
I have the following table loaded in Power Query. I am trying to add a new column called "Category" which would have specific text values based on the following conditions:
1. If Action date is greater than End date, then display text "Late".
2. If Action date is less or equal to End date, then display text "On time".
3. If Action date is missing (null), then display text "No action".
Any help is much appreciated!
| Start date | End date | Action date | Stock | Returns | Defective |
| 01/05/2021 | 01/06/2022 | 01/07/2022 | 6 | 9 | 17 |
| 27/05/2022 | 27/06/2022 | 27/06/2022 | 2 | 5 | 10 |
| 07/08/2022 | 07/09/2022 | 07/08/2022 | 5 | 6 | 15 |
| 08/08/2022 | 08/09/2022 | 3 | 4 | 8 | |
| 12/08/2022 | 20/09/2022 | 22/09/2022 | 2 | 2 | 10 |
| 17/08/2022 | 27/09/2022 | 23/08/2022 | 7 | 8 | 22 |
| 20/08/2022 | 10/10/2022 | 3 | 3 | 13 | |
| 22/08/2022 | 02/09/2022 | 22/09/2022 | 6 | 3 | 18 |
| 29/08/2022 | 09/10/2022 | 02/10/2022 | 3 | 3 | 14 |
| 07/09/2022 | 17/10/2022 | 2 | 7 | 17 | |
| 16/09/2022 | 06/10/2022 | 19/09/2022 | 3 | 7 | 22 |
| 20/10/2022 | 23/11/2022 | 15/11/2022 | 9 | 3 | 16 |
| 23/11/2022 | 02/12/2022 | 23/12/2022 | 7 | 5 | 20 |
| 11/12/2022 | 15/01/2023 | 9 | 8 | 18 | |
| 04/01/2023 | 04/03/2023 | 17/02/2023 | 6 | 7 | 16 |
| 17/02/2023 | 13/03/2023 | 01/03/2023 | 7 | 14 | 34 |
Expected results:
| Start date | End date | Action date | Stock | Returns | Defective | Category |
| 01/05/2021 | 01/06/2022 | 01/07/2022 | 6 | 9 | 17 | Late |
| 27/05/2022 | 27/06/2022 | 27/06/2022 | 2 | 5 | 10 | On time |
| 07/08/2022 | 07/09/2022 | 07/08/2022 | 5 | 6 | 15 | On time |
| 08/08/2022 | 08/09/2022 | 3 | 4 | 8 | No action | |
| 12/08/2022 | 20/09/2022 | 22/09/2022 | 2 | 2 | 10 | Late |
| 17/08/2022 | 27/09/2022 | 23/08/2022 | 7 | 8 | 22 | On time |
| 20/08/2022 | 10/10/2022 | 3 | 3 | 13 | No action | |
| 22/08/2022 | 02/09/2022 | 22/09/2022 | 6 | 3 | 18 | Late |
| 29/08/2022 | 09/10/2022 | 02/10/2022 | 3 | 3 | 14 | On time |
| 07/09/2022 | 17/10/2022 | 2 | 7 | 17 | No action | |
| 16/09/2022 | 06/10/2022 | 19/09/2022 | 3 | 7 | 22 | On time |
| 20/10/2022 | 23/11/2022 | 15/11/2022 | 9 | 3 | 16 | On time |
| 23/11/2022 | 02/12/2022 | 23/12/2022 | 7 | 5 | 20 | Late |
| 11/12/2022 | 15/01/2023 | 9 | 8 | 18 | No action | |
| 04/01/2023 | 04/03/2023 | 17/02/2023 | 6 | 7 | 16 | On time |
| 17/02/2023 | 13/03/2023 | 01/03/2023 | 7 | 14 | 34 | On time |
Hi Anonymous ,
Try the custom column below.
if [Action date] = null then "No Action" else if [Action date] > [End date] then "Late" else if [Action date] <= [End date] then "On Time" else "No Action"
Did I help you today? Please accept my solution and hit the Kudos button.
1 Reply
Hi Anonymous ,
Try the custom column below.
if [Action date] = null then "No Action" else if [Action date] > [End date] then "Late" else if [Action date] <= [End date] then "On Time" else "No Action"
Did I help you today? Please accept my solution and hit the Kudos button.