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.
To achieve the desired result of obtaining the previous scheduling month for each order line ID based on the run date, you can use Power Query's capabilities to add a custom column. Here's how you can do it:
1. Open the Power Query Editor.
2. Select the "Backlog Reports" query.
3. Go to the "Add Column" tab.
4. Click on "Custom Column".
5. In the Custom Column dialog, enter the following formula:
```m
= let
PreviousDate = Date.AddDays([Run Date], -1),
PreviousSchedulingMonth = List.Last(
Table.SelectRows(
#"Added Custom",
each [Order Line ID] = [Order Line ID] and [Run Date] = PreviousDate
)[[Scheduling Month]]
)
in
PreviousSchedulingMonth
```
This formula first calculates the previous date based on the run date using Date.AddDays(). Then, it filters the table to select rows with the same order line ID as the current row and the previous run date. Finally, it extracts the last scheduling month from the filtered rows using List.Last().
6. Click OK to add the custom column.
This should create a new column called "Previous Scheduling Month" with the desired values. Make sure to replace "Added Custom" with the actual name of your previous step in the query if it's different.
Once you've added the custom column, you can close and apply the changes to your query. Now, you should see the "Previous Scheduling Month" column populated with the correct values based on the previous run date for each order line ID.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Here is what it returned.
That last step in my query 'Custom' is just me clicking on the error to see what it was.
