power query group-by data until state change

[Anonymous]

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 …..

[LivioLanzo · Accepted solution]

Hi @Anonymous 

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

[Anonymous]

you saved my day.

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

Thanks a lot