Power Query - Value from previous transaction (Other row)

[11097486]

Hello, I would like to be able to show the Date and Sales value from the previous transaction in Power Query M (Not DAX). The previous transaction is on another row. Thank you

[mahoneypat]

Here is one way to do it in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstV4jML8pW0lEy1DfUNzIwMgAxDZRidaKVfDMTczPBMsZwGVOwjI8jWNgEJmyErsEULmOMrMEcbo4hWBjFeguYpJlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Date = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Date", type date}, {"Sales", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each let thisdate = [Date], thiscity = [City] in Table.FirstN(Table.SelectRows(#"Changed Type", each [Date] < thisdate and [City] = thiscity),1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Date", "Sales"}, {"Date.1", "Sales.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Date.1", "Prev Date"}, {"Sales.1", "Prev Sales"}})
in
    #"Renamed Columns"

Regards,

Pat

[11097486]

Hi Pat, close but not exactly. Your solution gives me the minimum date and not previous date. Any ideas? Thank you

[11097486]

Hello, not really what I'm looking for. I need something that will be able to find the previous date of the same category (in this case city). Thank you

[v-yangliu-msft]

Hi  @11097486 ,

Here are the relevant contents for you to check and hope to help you:

https://community.powerbi.com/t5/Desktop/Power-Query-Use-Previous-Rows-Value-in-Current-Calculation/...

https://community.powerbi.com/t5/Desktop/Power-Query-M-to-Display-Correct-Start-Date-Script-Attached...

https://docs.microsoft.com/en-us/powerquery-m/quick-tour-of-the-power-query-m-formula-language

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.