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,
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.
Solved! Go to 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.
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.
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |