Forum Discussion
Aggregating identical trailing rows
Consider the following table
| Index | Timestamp | Text | Text Change | Duration |
| 1 | 01.01.2021 12:00:00 | Text B | true | 90 |
| 2 | 01.01.2021 13:30:00 | Text B | false | 30 |
| 3 | 01.01.2021 14:00:00 | true | 60 | |
| 4 | 01.01.2021 15:00:00 | Text A | true | 105 |
| 5 | 01.01.2021 16:45:00 | Text A | false | 15 |
| 6 | 01.01.2021 17:00:00 | Text A | false | 60 |
| 7 | 01.01.2021 18:00:00 | Text C | true | 80 |
| 8 | 01.01.2021 19:20:00 | Text B | true | 40 |
| 9 | 01.01.2021 20:00:00 | Text C | true | 60 |
What I want to achieve in the end is show the duration of each text before it changes. To do this I want to aggregate the duration value for identical trailing rows, based on the text field. Se below table:
| Index | Timestamp | Text | Text Change | Duration | Aggregated duration |
| 1 | 01.01.2021 12:00:00 | Text B | true | 90 | 120 |
| 2 | 01.01.2021 13:30:00 | Text B | false | 30 | 120 |
| 3 | 01.01.2021 14:00:00 | true | 60 | 60 | |
| 4 | 01.01.2021 15:00:00 | Text A | true | 105 | 180 |
| 5 | 01.01.2021 16:45:00 | Text A | false | 15 | 180 |
| 6 | 01.01.2021 17:00:00 | Text A | false | 60 | 180 |
| 7 | 01.01.2021 18:00:00 | Text C | true | 80 | 80 |
| 8 | 01.01.2021 19:20:00 | Text B | true | 40 | 40 |
| 9 | 01.01.2021 20:00:00 | Text C | true | 60 | 60 |
After this is done I can just filter on "Text Change = true" to hide text duplicates.
Any ideas?
3 Replies
Does that mean you don't care about the timestamp for index 2 for example?
As for the final outcome you are correct. In other words, any timestamp in rows with 'Text change' = false is irrelevant. Only difference in time between rows with 'Text change' = true is relevant.
E.g. difference in time between index 1 and 3, 3 and 4, 4 and 7 etc.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddDNCsIwDAfwVxk9D0nSj7W9qa/grezgYZ486QQf32xxlkaFQEvIr/nTUgya3gDuuAgIO6QMwMXd0/ScuwNf5ttj4iOBGftiSAGbrQaX8/W+CCvCKuE+K+rjQUadGvVtmn0FCH4VXomQnVdii4MighLD945NvFMNSsRWHGuqKCAqkDL9+VQnILVgnf69YYk0vgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Timestamp = _t, #" Text" = _t, #" Text Change" = _t, Duration = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Duration", Int64.Type}, {"Index", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.PositionOf(List.Skip(#"Changed Type"[#" Text Change"],[Index]),"true")), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Aggregated duration", each List.Sum(Table.SelectRows(#"Changed Type1",(k)=>k[Index]>=[Index] and k[Index]<=[Index]+[Custom])[Duration])), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Index", "Timestamp", " Text", " Text Change", "Duration", "Aggregated duration"}), #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([#" Text Change"] = "true")) in #"Filtered Rows"not very elegant but works.
I'll leave the handling of the null in the last row up to you.
