Forum Discussion
Return value from prior date
OK, jwin2424 . I think I understand your requirements. You want to get previous row group by one or two fields.
Here's a blog about this which really helps. Please refer to
The second part from this blog(Value from previous row, without writing of M code) can fix this.
I made a sample for you also, you can download it.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE31DcyMDJR0lHyCvXRBTMMjYyBpKmpUqwOSIERQoEfVgXGhBSYEFJgSkiBBUyBr2MkVgWGWEwwIeQLE0K+MCHkCxNCvjAh5AuYglgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Run Date" = _t, #"Scheduling Month" = _t, #"Order Line ID" = _t, #"Order #" = _t]), #"Grouped Rows" = Table.Group(Source, {"Order Line ID"}, {{"allrows", each _, type table [Run Date=nullable text, Scheduling Month=nullable text, Order Line ID=nullable text, #"Order #"=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([allrows],"index",1,1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"allrows"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Run Date", "Scheduling Month", "Order #", "index"}, {"Run Date", "Scheduling Month", "Order #", "index"}), #"Duplicated Column" = Table.DuplicateColumn(#"Expanded Custom", "index", "index - Copy"), #"Added to Column" = Table.TransformColumns(#"Duplicated Column", {{"index - Copy", each _ + 1, type number}}), #"Merged Queries" = Table.NestedJoin(#"Added to Column", {"index", "Order Line ID"}, #"Added to Column", {"index - Copy", "Order Line ID"}, "Added to Column", JoinKind.LeftOuter), #"Expanded Added to Column" = Table.ExpandTableColumn(#"Merged Queries", "Added to Column", {"Scheduling Month"}, {"Scheduling Month.1"}) in #"Expanded Added to Column"Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your concern in this issue, johnbasha33 .
jwin2424 , I have reviewed your case and key information is needed to give you the best possible support, please send the following information/details at your earliest convenience.
Information Required:
1.Why is your red underline JUL-24, knowing that your [Order Line ID] has changed from 123 to 124?
2.The green line, [Order Line ID] is 124, [Run Date] is 4/2/2024, the previous line is 4/1/2024, and the corresponding [Scheduling Month] is JUN-24, why JUL-24?
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
My appologies. I was just copy/pasting from the previous 123 line IDs. Those would all read JUN-24 if done correctly.
OK, jwin2424 . I think I understand your requirements. You want to get previous row group by one or two fields.
Here's a blog about this which really helps. Please refer to
The second part from this blog(Value from previous row, without writing of M code) can fix this.
I made a sample for you also, you can download it.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE31DcyMDJR0lHyCvXRBTMMjYyBpKmpUqwOSIERQoEfVgXGhBSYEFJgSkiBBUyBr2MkVgWGWEwwIeQLE0K+MCHkCxNCvjAh5AuYglgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Run Date" = _t, #"Scheduling Month" = _t, #"Order Line ID" = _t, #"Order #" = _t]), #"Grouped Rows" = Table.Group(Source, {"Order Line ID"}, {{"allrows", each _, type table [Run Date=nullable text, Scheduling Month=nullable text, Order Line ID=nullable text, #"Order #"=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([allrows],"index",1,1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"allrows"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Run Date", "Scheduling Month", "Order #", "index"}, {"Run Date", "Scheduling Month", "Order #", "index"}), #"Duplicated Column" = Table.DuplicateColumn(#"Expanded Custom", "index", "index - Copy"), #"Added to Column" = Table.TransformColumns(#"Duplicated Column", {{"index - Copy", each _ + 1, type number}}), #"Merged Queries" = Table.NestedJoin(#"Added to Column", {"index", "Order Line ID"}, #"Added to Column", {"index - Copy", "Order Line ID"}, "Added to Column", JoinKind.LeftOuter), #"Expanded Added to Column" = Table.ExpandTableColumn(#"Merged Queries", "Added to Column", {"Scheduling Month"}, {"Scheduling Month.1"}) in #"Expanded Added to Column"Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is exactly what I needed. Thank you!