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

[William_Moreno]

Hi everyone, I need help.

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!

[v-jingzhang · Accepted solution]

Hi @William_Moreno 

Here is another method.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1MNc1MFTSUXJKLcrJzAUyDA0MjExNwQw9U6VYHTR1Qfm5qSiqDLGp8kstV4jML8pGUWmEotICp73GegaY6jDsxWIWVlstlGJjAQ==", 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 with Locale" = Table.TransformColumnTypes(Source, {{"date", type date}, {"value", type number}}, "en-US"),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type with Locale", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"unit", "sku"}, {{"Groups", each _, type table [date=nullable text, unit=nullable text, sku=nullable text, value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Groups2", each Table.AddIndexColumn([Groups], "sort", 0, 1, Int64.Type)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Groups2"}),
    #"Expanded GroupsIndex" = Table.ExpandTableColumn(#"Removed Other Columns", "Groups2", {"date", "unit", "sku", "value", "Index", "sort"}, {"date", "unit", "sku", "value", "Index", "sort"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded GroupsIndex", "Difference", each if [sort] = 0 then 0 else [value] - Table.SelectRows(#"Expanded GroupsIndex", (x)=> x[unit]=[unit] and x[sku]=[sku] and x[sort]=[sort]-1){0}[value]),
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "sort"})
in
    #"Removed Columns"

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Jakinta]

Try steps below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1MNc1MFTSUXJKLcrJzAUyDA0MjExNwQw9U6VYHTR1Qfm5qSiqDLGp8kstV4jML8pGUWmEotICp73GegaY6jDsxWIWVlstlGJjAQ==", 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 = Table.TransformColumnTypes(Source,{{"value", type number}}),
    Index = Table.AddIndexColumn(Changed, "Index", 0, 1, Int64.Type),
    Grouped = Table.Group(Index, {"unit"}, {{"Gr", each let l=_[value], n={0..List.Count(l)-1}, x=List.Transform(n, each if _=0 then 0 else l{_}-l{_-1} ) in Table.FromColumns(Table.ToColumns(_)&{x}, Table.ColumnNames(_)&{"difference"}), type table }}),
    Removed = Table.RemoveColumns(Grouped,{"unit"}),
    Expanded = Table.ExpandTableColumn(Removed, "Gr", Table.ColumnNames(Removed[Gr]{0})),
    Sorted = Table.Sort(Expanded,{{"Index", Order.Ascending}}),
    FINAL = Table.RemoveColumns(Sorted,{"Index"})
in
    FINAL