Forum Discussion
Difference between rows by group
Hello I have a column that records the progressive incremental value associated with an attribute. I would like to calculate the difference between the value of two rows to find the total value for each day and of each attribute.
The simplified example of the table is as follows.
The 5th column [kw] is the difference between two consecutive rows of the same attribute.
The target is to calculate the kw consumed every day by each equipment
| ID | Equipment | DateHourStop | kwEnd | kw |
| 1 | 4 | 12/1/24 10.50 | 125789 | |
| 2 | 4 | 14/1/24 12.52 | 128882 | 3093 |
| 3 | 5 | 11/1/24 15.18 | 5241 | |
| 4 | 4 | 12/1/24 15.18 | 132854 | 3972 |
| 5 | 6 | 13/1/24 15.18 | 21521 | |
| 6 | 6 | 14/1/24 15.18 | 24364 | 2843 |
| 7 | 6 | 15/1/24 15.18 | 28457 | 4093 |
| 8 | 4 | 16/1/24 15.18 | 138452 | 5598 |
| 9 | 6 | 17/1/24 15.18 | 30214 | 1757 |
| 10 | 7 | 18/1/24 15.18 | 22542 | |
| 11 | 7 | 19/1/24 15.18 | 23452 | 910 |
| 12 | 6 | 20/1/24 15.18 | 31245 | 1031 |
you mean row 4?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdDBDcUgDAPQVb44Vy02CaSzoO6/xi8BDs2BA9HDBnpPSEeSd4EXLsoP+dTse212p+foiZvIIjyVTsyMTsq71THCInrCxowCBxIzFkChqTgZ56uPvoRQzpC6RQihlDoz2hYahIk2F7YvUuNFXjIfc++Q9iUlE7MG44dGFyz0UGWGAJvcgZTdA64i5lAEiqbn+QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Equipment = _t, DateHourStop = _t, kwEnd = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Equipment", Int64.Type}, {"DateHourStop", type text}, {"kwEnd", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Equipment"}, {{"Rows", each Table.AddColumn(_, "kw", (k)=> try k[kwEnd]-Table.Last(Table.SelectRows(_, each [DateHourStop]<k[DateHourStop]))[kwEnd] otherwise null,Int64.Type), type table [ID=nullable number, Equipment=nullable number, DateHourStop=nullable text, kwEnd=nullable number, kw=nullable number]}}), #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"ID", "DateHourStop", "kwEnd", "kw"}, {"ID", "DateHourStop", "kwEnd", "kw"}) in #"Expanded Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
3 Replies
Your sample data is inconsistent for equipment 4 (rows 2 and 4). Please check.
Yes sorry (the table is more complicated, this is just a sample) the date of rows 2 is 14/01. Thanks
you mean row 4?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdDBDcUgDAPQVb44Vy02CaSzoO6/xi8BDs2BA9HDBnpPSEeSd4EXLsoP+dTse212p+foiZvIIjyVTsyMTsq71THCInrCxowCBxIzFkChqTgZ56uPvoRQzpC6RQihlDoz2hYahIk2F7YvUuNFXjIfc++Q9iUlE7MG44dGFyz0UGWGAJvcgZTdA64i5lAEiqbn+QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Equipment = _t, DateHourStop = _t, kwEnd = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Equipment", Int64.Type}, {"DateHourStop", type text}, {"kwEnd", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Equipment"}, {{"Rows", each Table.AddColumn(_, "kw", (k)=> try k[kwEnd]-Table.Last(Table.SelectRows(_, each [DateHourStop]<k[DateHourStop]))[kwEnd] otherwise null,Int64.Type), type table [ID=nullable number, Equipment=nullable number, DateHourStop=nullable text, kwEnd=nullable number, kw=nullable number]}}), #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"ID", "DateHourStop", "kwEnd", "kw"}, {"ID", "DateHourStop", "kwEnd", "kw"}) in #"Expanded Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
