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

[William_Moreno]

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	100255	11.5
2021-07-01	New York	100255	12.5
2021-08-01	Berlim	100255	13.0
2021-08-01	Rome	100255	14.0
2021-08-01	New York	100255	17.0

expected results 

date	unit	sku	value	diference
2020-07-01	Berlim	100255	10.5	0
2020-07-01	Rome	100255	11.5	0
2020-07-01	New York	100255	12.5	0
2021-08-01	Berlim	100255	13.0	2.5
2021-08-01	Rome	100255	15.0	3.5
2021-08-01	New York	100255	18.0	5.5

Could you help?

Thanks in advanced!

William M.

[lbendlin · Accepted solution]

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.