Grouping multiple rows

[Anonymous]

Hello,

I have a device that will write data into a table whenever an event occurs. An event means either the device is turned on or turned off, or an abnormality happened or the abnormality disappeared. Here is a sample table:

For example, according to rows 5 to 9, the device was Off for 22 sec, then turned On for 155 sec, then encountered an abnormality for 78 sec while still being On, then the abnormality disappeared and the device went back to normal for 56 sec, and then it turned Off and waited for 220 sec before being turned On again.

My question is this:

I am trying to group different rows together to be considered as a single "cycle". The device is having a single "cycle" when it turns On until it turns Off. I will then need to analyze the durations for these "cycle"s, not single events. If I can make a conditional column that increments a variable based on the status like the picture below, then I can separate these events based on that conditional column.

Utimately, I want to add up all the duration based on each "cycle" (most likely putting it in a new table or so).

I don't know if the Power Query or DAX can do this conditional column, but if not, I am open to other suggestions.

Thank you very much!

[ImkeF · Accepted solution]

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"

[Anonymous]

Thank you. This solves my problem.

[amitchandak]

@ImkeF , can you help