Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Grouping multiple rows

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:

 

testData1.PNG

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.

testData2.PNG

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!

 

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

3 REPLIES 3
ImkeF
Super User
Super User

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"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

Thank you. This solves my problem.

 

amitchandak
Super User
Super User

@ImkeF , can you help

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.