Counting rows for event sequences

Hi Johannes_Swarts ,

If you're looking for something like the screenshot below, you can make use of an extra parameter when grouping a table in Power Query which groups rows by sequence. In a sequence of A-B-C-A-A-B-C-C-C, A-A and C-C-C will each own group, the rest will each have their own as well. After grouping you can then add an index column to identify the groups.

Here's a sample code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJJCoAwEETRq0jWQnoyRncOtwje/xoquJL89YOuoujWktSskk0sBo1VljSmLV3jH1QIlMAJgqAQzA/sPagEC4C9dY8eGIETTFDXCoVXOvXWPTvgtK4bAa3rE4R7IaC6TusGfUl86143", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, Event = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Timestamp", type datetime}, {"Event", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Event"}, {{"Grouped", each _, type table [Timestamp=nullable datetime, Event=nullable text]}}, GroupKind.Local),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "RowCount", each Table.RowCount([Grouped])),
    #"Expanded Grouped" = Table.ExpandTableColumn(#"Added Custom", "Grouped", {"Timestamp"}, {"Timestamp"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Grouped",{"Timestamp", "Event", "Index", "RowCount"})
in
    #"Reordered Columns"

You may also try this approach that uses DAX instead:

https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211