Return value from prior date

[jwin2424]

Hello all. 

I am trying to get the results below in the "Previous Scheduling Month" via the Power Query Editor. 

I have a single query named 'Backlog Reports'. Each day [Run Date] I append the report. Each [Order #] has numerous lines with different [Scheduling Month] values. I have a column [Order Line ID] that is unique to each row on the [Order #]. I need to create a custom column that returns the [Scheduling Month] value from the previous [Run Date] for that [Order Line ID]. There is one caviate - we run the reports during the week, so the [Run Date] only provides the Mon-Fri work week. Can someone help me achieve the column [Previous Scheduling Month] via a custom column in the query?

Run Date	Scheduling Month	Order Line ID	Order #	Previous Scheduling Month
4/1/2024	JUL-24	123	55	JUL-24
4/2/2024	JUN-24	123	55	JUL-24
4/3/2024	JUN-24	123	55	JUN-24
4/4/2024	JUN-24	123	55	JUN-24
4/5/2024	JUN-24	123	55	JUN-24
4/8/2024	MAY-24	123	55	JUN-24
4/1/2024	JUN-24	124	55	JUL-24
4/2/2024	JUN-24	124	55	JUL-24
4/3/2024	JUN-24	124	55	JUN-24
4/4/2024	JUN-24	124	55	JUN-24
4/5/2024	JUN-24	124	55	JUN-24
4/8/2024	MAY-24	124	55	JUN-24

Thanks!

[v-stephen-msft]

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.

[jwin2424]

My appologies. I was just copy/pasting from the previous 123 line IDs. Those would all read JUN-24 if done correctly. 

[v-stephen-msft · Accepted solution]

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

Value from previous row – Power Query, M language – Trainings, consultancy, tutorials (exceltown.com...

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.

[jwin2424]

This is exactly what I needed. Thank you!

[johnbasha33]

@jwin2424 

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 !!

[jwin2424]

Here is what it returned. 

That last step in my query 'Custom' is just me clicking on the error to see what it was.