Forum Discussion

William_Moreno's avatar
5 years ago
Solved

difference between two rows in the power query (m-language)

Hi everyone, Today, I have this situation "difference between two rows in the power query (m-language)" table - 1 date unit sku value 2021-07-01 Berlim 100255 10.5 2021-07-01 Rome ...
  • lbendlin's avatar
    5 years ago

    Yes, familiarize yourself with how to reference tables (remember that each Power Query step result is a table), columns and rows. Your data needs to be sortable, and ideally (although not strictly required) have a zero-based index column. Columns are addressed by [ ], rows by { } .

     

    Then use functions like Table.SelectRows() and custom column generators to identify the desired previous rows and pull their column data in. And finally learn about "try...otherwise" to properly handle the errors that you will hit for the rows that don't have a "prior" row.

     

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1MNc1MFTSUXJKLcrJzAMyDA0MjExNwQw9U6VYHTR1Qfm5qSiqDLGp8kstV4jML8pGUWmEotICp73GegaY6jDsNcGmCqu95iCVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, unit = _t, sku = _t, value = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"unit", type text}, {"sku", type text}, {"value", type number}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "difference", each try [value]-Table.SelectRows(#"Changed Type",(k)=> [unit]=k[unit] and [date]>k[date])[value]{0} otherwise 0)
    in
        #"Added Custom"

    Note:  This code doesn't do proper sorting, but your sample data is not sufficent to test that anyway.