Forum Discussion
Solved
Power Query: Adding a helper column for comparing multiple rows
Good morning, I've got a set of data where there can be three different scenarios, as shown in the screenshots below: For the requistion number, there is one instance of "Submit" and one inst...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdG9CsIwEMDxVymZhdxX0ms2X8ChjqWL4OAgFlGf37SIaWokW4Yff+4uw2D6Xg8MwCyeyezM8Xm6Xh7xQa1FtAREDVBgNOPuV++n6X57nWeuiXdBOOMiXtt1HDoLi+YGJaAUdYrnnLASJwv60RCorcVzrttF4y7ls8yai/rfWTaLFniX6i4Q5NyTy2ZB+E4OGoSKOsXXPM5Si2dnQVeLc+LxR7XGXeJ+mWV8Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Requisition Number" = _t, #"Action Code" = _t, #"Action Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Requisition Number", type text}, {"Action Code", type text}, {"Action Date", type datetime}},"en-GB"), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Requisition Number", Order.Ascending}, {"Action Date", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Requisition Number", "Action Code"}, {{"Rows", each _, type table [Requisition Number=nullable text, Action Code=nullable text, Action Date=nullable datetime]}, {"Helper Column", each Table.RowCount(_), Int64.Type}},GroupKind.Local), #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Action Date"}, {"Action Date"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded Rows",each [Helper Column],each if [Helper Column]=1 then "No" else "Yes",Replacer.ReplaceValue,{"Helper Column"}), #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Helper Column", type text}}) in #"Changed Type1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
| Requisition Number | Document Status | Action Code | Action Date | Helper Column | Notes (Not part of data) | |
| RR8N300334632 | APPROVED | Submit | 27/11/2022 02:31 | No | No because submit line | |
| RR8N300334632 | APPROVED | Approve | 28/11/2022 09:43 | No | No because only one approval | |
| RR8N300344687 | APPROVED | Submit | 09/01/2023 14:14 | No | No because submit line | |
| RR8N300344687 | APPROVED | Approve | 09/01/2023 14:21 | No | No because prior to a later submit line | |
| RR8N300344687 | APPROVED | Submit | 02/08/2023 10:27 | No | No because submit line | |
| RR8N300344687 | APPROVED | Approve | 02/08/2023 10:28 | No | No because a single approval line after the latest submit line | |
| RR8N300334643 | APPROVED | Submit | 27/11/2022 10:23 | No | No because submit line | |
| RR8N300334643 | APPROVED | Approve | 28/11/2022 09:14 | Yes | Yes because multiple approvals after a submit line | |
| RR8N300334643 | APPROVED | Approve | 29/11/2022 15:20 | Yes | Yes because multiple approvals after a submit line | |
| RR8N300336253 | APPROVED | Submit | 10/08/2023 08:42 | No | No because submit line | |
| RR8N300336253 | APPROVED | Approve | 10/08/2023 09:10 | No | No because prior to a later submit line | |
| RR8N300336253 | APPROVED | Submit | 12/08/2023 10:15 | Yes | No because submit line | |
| RR8N300336253 | APPROVED | Approve | 13/08/2023 14:18 | Yes | Yes because multiple approvals after a submit line | |
| RR8N300336253 | APPROVED | Approve | 15/08/2023 16:10 | Yes | Yes because multiple approvals after a submit line |
The helper column is what I'm looking to produce, and I've included an extra column on the far right with notes on the logic that has decided whether each line is a Yes or a No in the helper column.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdG9CsIwEMDxVymZhdxX0ms2X8ChjqWL4OAgFlGf37SIaWokW4Yff+4uw2D6Xg8MwCyeyezM8Xm6Xh7xQa1FtAREDVBgNOPuV++n6X57nWeuiXdBOOMiXtt1HDoLi+YGJaAUdYrnnLASJwv60RCorcVzrttF4y7ls8yai/rfWTaLFniX6i4Q5NyTy2ZB+E4OGoSKOsXXPM5Si2dnQVeLc+LxR7XGXeJ+mWV8Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Requisition Number" = _t, #"Action Code" = _t, #"Action Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Requisition Number", type text}, {"Action Code", type text}, {"Action Date", type datetime}},"en-GB"), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Requisition Number", Order.Ascending}, {"Action Date", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Requisition Number", "Action Code"}, {{"Rows", each _, type table [Requisition Number=nullable text, Action Code=nullable text, Action Date=nullable datetime]}, {"Helper Column", each Table.RowCount(_), Int64.Type}},GroupKind.Local), #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Action Date"}, {"Action Date"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded Rows",each [Helper Column],each if [Helper Column]=1 then "No" else "Yes",Replacer.ReplaceValue,{"Helper Column"}), #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Helper Column", type text}}) in #"Changed Type1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".