- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 …..
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you saved my day.
so simple, I wasn\t aware on the groupkind.
Thanks a lot

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
04-25-2024 07:02 AM | |||
02-27-2024 06:49 PM | |||
11-30-2023 08:56 PM | |||
12-17-2022 01:22 AM | |||
03-14-2024 10:50 AM |
User | Count |
---|---|
141 | |
112 | |
83 | |
63 | |
47 |