Power Query - Nested Previous Row

[Anonymous]

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

[Jpss]

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=a81b77b3...
Sharing here incase someone finds it helpful

[v-deddai1-msft]

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

[amitchandak]

@Anonymous , Use this a a new colum

maxx(filter(table,[memberID]=earlier([memberID]) && [order] <earlier([Order])),[Order])

Measure and column options discussed here

https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

[AllisonKennedy · Accepted solution]

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