Grouping multiple rows

Hi Anonymous  

you add a column where you check if "status" = "Off". Then use a an index-column like "Event" with unique values otherwise (a real null. That allows you to fill down the values found for the Off-values.

But as this will not return a continuous series, there is another version where you group on that new column and add an index on the grouped data instead.

Paste the following coded into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcpPS1OK1YlWMgKx88BMYwTTBEmFKULYDEnYHCFsAWXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Event = _t, Status = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Cycle", each if [Status] = "off" then [Event] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Cycle"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Event", Int64.Type}, {"Status", type text}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcpPS1OK1YlWMgKx88BMYwTTBEmFKULYDEnYHCFsAWXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Event = _t, Status = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Cycle", each if [Status] = "off" then [Event] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Cycle"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Event", Int64.Type}, {"Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Cycle"}, {{"CyclePartition", each _, type table [Event=number, Status=text, Cycle=text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Expanded CyclePartition" = Table.ExpandTableColumn(#"Added Index", "CyclePartition", {"Event", "Status"}, {"Event", "Status"})
in
    #"Expanded CyclePartition"