Forum Discussion
Solved
difference between two rows in the power query (m-language)
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!
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.
2 Replies
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 FINALHere 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.
