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

power query group-by data until state change

Hello
I have a problem that I hope someone can help with a solution.
I have a table looks like>
Device,       StartUtc,                        EndUtc,                        State
ABC         2/5/2019 9:08:21 AM     2/5/2019 9:13:26 AM     Idle
ABC         2/5/2019 9:13:27 AM     2/5/2019 9:18:31 AM     Idle
ABC         2/5/2019 9:18:32 AM     2/5/2019 9:23:37 AM     Idle
ABC        2/5/2019 9:23:38 AM      2/5/2019 9:28:43 AM     Idle
ABC        2/5/2019 9:28:44 AM      2/5/2019 9:33:48 AM     Idle
ABC        2/5/2019 9:33:49 AM      2/5/2019 9:36:31 AM     Idle
ABC        2/5/2019 9:36:31 AM      2/5/2019 9:38:54 AM    Stand-by
ABC        2/5/2019 9:38:55 AM      2/5/2019 9:44:00 AM    Stand-by
ABC        2/5/2019 9:44:01 AM      2/5/2019 9:49:05 AM    Stand-by
ABC        2/5/2019 9:49:06 AM      2/5/2019 9:53:32 AM    Stand-by
ABC        2/5/2019 9:53:32 AM      2/5/2019 9:54:11 AM     Idle
ABC        2/5/2019 9:54:12 AM      2/5/2019 9:59:16 AM     Idle
ABC        2/5/2019 9:59:17 AM      2/5/2019 10:04:22 AM   Idle
ABC        2/5/2019 10:04:23 AM    2/5/2019 10:09:28 AM    Idle
ABC        2/5/2019 10:09:29 AM    2/5/2019 10:14:34 AM    Idle
ABC        2/5/2019 10:14:35 AM    2/5/2019 10:19:39 AM    Idle
ABC        2/5/2019 10:19:40 AM    2/5/2019 10:22:54 AM    Idle

 

I have 3 different states- Operational, Idle and Stand-By.

 

I would like to group-by the data to look like>
Device          StartUtc                           EndUtc                     State
ABC       2/5/2019 9:08:21 AM       2/5/2019 9:36:31 AM      Idle
ABC       2/5/2019 9:36:31 AM       2/5/2019 9:53:32 AM      Stand-by
ABC       2/5/2019 9:53:32 AM       2/5/2019 10:22:54 AM     Idle

 

In real life I have a lot of devices so the ABC is just one device example.
I sorted the table in Power Query to first have the device ascending and after have the StartUTC ascending so I get for each device the state in right order.
I added and index from 0 and I copied the table to a new one when on the copied table I changed the index to start from 1.
I merge the table on the Index and I got the previous state.
I deleted rows that the two states are same…..

My problem is that the table is partition by the current month and I do not want to have 3 different tables to each month as I will result in 36 tables.
The table used as data in report can be a Function call using month as parameter, but I am not sure I can pass such parameter on the merged tables.

Do you know any better way to aggregate the data to the desire result? maybe in DAX or should I use direct SQL query as source …..

 

 

1 ACCEPTED SOLUTION
LivioLanzo
Solution Sage
Solution Sage

Hi @Anonymous 

 

in the Group formula, for the last argument use GroupKind.Local instead of GroupKind.Global

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

2 REPLIES 2
LivioLanzo
Solution Sage
Solution Sage

Hi @Anonymous 

 

in the Group formula, for the last argument use GroupKind.Local instead of GroupKind.Global

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

you saved my day.

so simple, I wasn\t aware on the groupkind.

 

Thanks a lot

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.