Difference between rows by group

[caseski]

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

[lbendlin]

Your sample data is inconsistent for equipment 4 (rows 2 and 4).  Please check.

[caseski]

Yes sorry (the table is more complicated, this is just a sample) the date of rows 2 is 14/01. Thanks

[lbendlin · Accepted solution]

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.