Conditional column based on dates and value comparisons

[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 than or equal to End date, then display text "On time".

3. If Status is 2, then display text "No action". This step disregards any other columns, so it depends only on the Status value.

4. Otherwise, display text "Undefined".

Document ID	Status	Start date	End date	Action date	Stock	Returns	Defective
3998	3	01/05/2021	01/06/2022	01/07/2022	6	9	17
4050	5	27/05/2022	27/06/2022	27/06/2022	2	5	10
4050	4	07/08/2022	07/09/2022	07/08/2022	5	6	15
4052	3	14/07/2022	11/08/2022		5	6	15
4060	2	08/08/2022	08/09/2022		3	4	8
4060	1	12/08/2022	20/09/2022	22/09/2022	2	2	10
4139	3	17/08/2022	27/09/2022	23/08/2022	7	8	22
4145	2	20/08/2022	10/10/2022		3	3	13
4149	5	22/08/2022	02/09/2022	22/09/2022	6	3	18
4157	6	29/08/2022	09/10/2022	02/10/2022	3	3	14
4158	7	18/09/2022	15/10/2022		3	3	14
4160	2	07/09/2022	17/10/2022		2	7	17
4160	6	16/09/2022	06/10/2022	19/09/2022	3	7	22
4164	8	20/10/2022	23/11/2022	15/11/2022	9	3	16
4166	1	23/11/2022	02/12/2022	23/12/2022	7	5	20
4187	2	11/12/2022	15/01/2023		9	8	18
4187	5	04/01/2023	04/03/2023	17/02/2023	6	7	16
4187	6	17/02/2023	13/03/2023	01/03/2023	7	14	34
4189	2	11/12/2022	15/01/2023		9	8	18

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVRbDsMwCLtLvycVSELCWard/xojLWTutH1MGh9usDEP7Ti2Yja2x1Y8iHdqu5BwAJ1AAvQE6mEe3Lfn49gqNXLUPKSHgATQ7yDSmVCgzjqeNlZRB4ZgvbRwwS0FJFrgCkaZgfONphRuPA3qDqgbutPcQNYcEQuwhIAlgiAi2+Vi6RabEmxXCrz0s/hUDYHaUpYgjWn33933WaUkzXJPaJzkl3FNgeicW4+vYihgUNfV3mA5qCkwoh3GGXP7YTxp7z3hjHx6N1qOKu/yop0LV7wkBRobvJQQWHPWmpPH0fpu/LLA+wJrr5oCGqdy48wZCaoJbvpcUJ7K6Hk8DGlelE61cnVuYXMtaqQQVcicoCSYtycJNEenKKCfaVxAYP4nLNCvdXn7ubNh/zp/vgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document ID" = _t, Status = _t, #"Start date" = _t, #"End date" = _t, #"Action date" = _t, Stock = _t, Returns = _t, Defective = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document ID", Int64.Type}, {"Status", Int64.Type}, {"Start date", type date}, {"End date", type date}, {"Action date", type date}, {"Stock", Int64.Type}, {"Returns", Int64.Type}, {"Defective", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Status] = 2 then "No action" else if [Action date] <= [End date] then "On time" else if [Action date] > [End date] then "Late" else "Undefined")
in
    #"Added Conditional Column"

I have made an attempt but it is giving me this error:

Any help is much appreciated!

Expected results:

Document ID	Status	Start date	End date	Action date	Stock	Returns	Defective	Category
3998	3	01/05/2021	01/06/2022	01/07/2022	6	9	17	Late
4050	5	27/05/2022	27/06/2022	27/06/2022	2	5	10	On time
4050	4	07/08/2022	07/09/2022	07/08/2022	5	6	15	On time
4052	3	14/07/2022	11/08/2022		5	6	15	Undefined
4060	2	08/08/2022	08/09/2022		3	4	8	No action
4060	1	12/08/2022	20/09/2022	22/09/2022	2	2	10	Late
4139	3	17/08/2022	27/09/2022	23/08/2022	7	8	22	On time
4145	2	20/08/2022	10/10/2022		3	3	13	No action
4149	5	22/08/2022	02/09/2022	22/09/2022	6	3	18	Late
4157	6	29/08/2022	09/10/2022	02/10/2022	3	3	14	On time
4158	7	18/09/2022	15/10/2022		3	3	14	Undefined
4160	2	07/09/2022	17/10/2022		2	7	17	No action
4160	6	16/09/2022	06/10/2022	19/09/2022	3	7	22	On time
4164	8	20/10/2022	23/11/2022	15/11/2022	9	3	16	On time
4166	1	23/11/2022	02/12/2022	23/12/2022	7	5	20	Late
4187	2	11/12/2022	15/01/2023		9	8	18	No action
4187	5	04/01/2023	04/03/2023	17/02/2023	6	7	16	On time
4187	6	17/02/2023	13/03/2023	01/03/2023	7	14	34	On time
4189	2	11/12/2022	15/01/2023		9	8	18	No action

[Vijay_A_Verma · Accepted solution]

Use this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVRbDsMwCLtLvycVSELCWard/xojLWTutH1MGh9usDEP7Ti2Yja2x1Y8iHdqu5BwAJ1AAvQE6mEe3Lfn49gqNXLUPKSHgATQ7yDSmVCgzjqeNlZRB4ZgvbRwwS0FJFrgCkaZgfONphRuPA3qDqgbutPcQNYcEQuwhIAlgiAi2+Vi6RabEmxXCrz0s/hUDYHaUpYgjWn33933WaUkzXJPaJzkl3FNgeicW4+vYihgUNfV3mA5qCkwoh3GGXP7YTxp7z3hjHx6N1qOKu/yop0LV7wkBRobvJQQWHPWmpPH0fpu/LLA+wJrr5oCGqdy48wZCaoJbvpcUJ7K6Hk8DGlelE61cnVuYXMtaqQQVcicoCSYtycJNEenKKCfaVxAYP4nLNCvdXn7ubNh/zp/vgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document ID" = _t, Status = _t, #"Start date" = _t, #"End date" = _t, #"Action date" = _t, Stock = _t, Returns = _t, Defective = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document ID", Int64.Type}, {"Status", Int64.Type}, {"Start date", type date}, {"End date", type date}, {"Action date", type date}, {"Stock", Int64.Type}, {"Returns", Int64.Type}, {"Defective", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Status] = 2 then "No action" else if [Action date] = null then "Undefined" else if [Action date] <= [End date] then "On time" else if [Action date] > [End date] then "Late" else "Undefined")
in
    #"Added Conditional Column"

👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data