Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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
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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
User | Count |
---|---|
73 | |
70 | |
38 | |
24 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
41 | |
40 |