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