Conditional custom column based on dates comparison

[Anonymous]

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

[davehus · Accepted solution]

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.