Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 nes...
Need Help
v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
6 years ago

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