Aggregating identical trailing rows

[Kahrax]

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?

[lbendlin]

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.

[lbendlin]

Does that mean you don't care about the timestamp for index 2 for example?

[Kahrax]

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.