Forum Discussion
Solved
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 e...
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.
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.