Forum Discussion
Conditional column based on dates and value comparisons
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...
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
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