Forum Discussion
thehalfboy
2 years agoHelper I
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...
- 2 years ago
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".
lbendlin
2 years agoSuper User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523