Power Query - Value from previous transaction (Other row)

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

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/m-p/1242932

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

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.

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