Forum Discussion

Kahrax's avatar
Kahrax
Frequent Visitor
5 years ago

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.202...
lbendlin's avatar
lbendlin
Super User
5 years ago

 

 

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.