cancel
Showing results for 
Search instead for 
Did you mean: 
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 II
Super User II

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 II
Super User II

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

Anonymous
Not applicable

Thank you. This solves my problem.

 

amitchandak
Super User IV
Super User IV

@ImkeF , can you help

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors