Forum Discussion
Power Query - Nested Previous Row
Hello,
I am trying to get the Previous Order date in the next column for each client in Power Query (not DAX).
I read one tutorial and they suggested using this syntax, but it is not for nested (grouped) data. Could you please help me find the solution? Thanks
- This is the post I used to first reference previous row in Power Query: https://www.myonlinetraininghub.com/referencing-next-row-power-query
You will need an Index column and you will also need to add a condition that MemberID = prev MemberID
4 Replies
- This is the post I used to first reference previous row in Power Query: https://www.myonlinetraininghub.com/referencing-next-row-power-query
You will need an Index column and you will also need to add a condition that MemberID = prev MemberID Hi Anonymous ,
Would you please refer to :
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMLTUN9Q3VIrVQREwQhcwBgsYwQRM9A1NUUVMMUTMoMYaIykxQBUxB4vEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MemberID = _t, Order = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"MemberID", Int64.Type}, {"Order", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"MemberID"}, {{"Data", each _, type table [MemberID=number, Order=date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"index",0,1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"MemberID", "Data"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"MemberID", "Order", "index"}, {"Custom.MemberID", "Custom.Order", "Custom.index"}), #"Duplicated Column" = Table.DuplicateColumn(#"Expanded Custom", "Custom.index", "Custom.index - Copy"), #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Custom.index - Copy", "index+1"}}), #"Added to Column" = Table.TransformColumns(#"Renamed Columns", {{"index+1", each _ + 1, type number}}), #"Merged Queries" = Table.NestedJoin(#"Added to Column", {"Custom.index", "Custom.MemberID"}, #"Added to Column", {"index+1", "Custom.MemberID"}, "Added to Column", JoinKind.LeftOuter), #"Expanded Added to Column" = Table.ExpandTableColumn(#"Merged Queries", "Added to Column", {"Custom.Order"}, {"Added to Column.Custom.Order"}), #"Sorted Rows" = Table.Sort(#"Expanded Added to Column",{{"Custom.MemberID", Order.Ascending}, {"Custom.index", Order.Ascending}}), #"Renamed Columns1" = Table.RenameColumns(#"Sorted Rows",{{"Custom.MemberID", "MemberID"}, {"Custom.Order", "Order"}, {"Added to Column.Custom.Order", "PreviousOrder"}}), #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"Custom.index", "index+1"}) in #"Removed Columns1"If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Anonymous , Use this a a new colum
maxx(filter(table,[memberID]=earlier([memberID]) && [order] <earlier([Order])),[Order])
Measure and column options discussed here
I was looking for a similar solution but for extracting next row and with several references, managed to create a solution.
I have documented it in my post here:
https://medium.com/microsoft-power-bi/get-next-row-values-in-a-nested-table-c90622f53d4d?sk=a81b77b345797b9491052dc36a50d8e2
Sharing here incase someone finds it helpful
