Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |