Hi, I'm trying to calculate how many days products stays in a specific state, so I think converting open events to data range will help me with that, however, I have no idea how to aproach this problem in Power BI .
I have a table that logs the current state of a product once every day.
For example, I have the following table that grows every day with new data about my products:
rock_id | state_name | date (dd/mm/year)
1 | dry | 01/06/2018
1 | dry | 02/06/2018
1 | wet | 03/06/2018
1 | wet | 04/06/2018
1 | dry | 05/06/2018
2 | wet | 02/06/2018
2 | wet | 03/06/2018
Converting the above "open events" table to a new table, based on data ranges, I would theoretically have the following:
rock_id | event_start | event_end | state
1 | 01/06/2018 | 02/06/2018 | dry
1 | 03/06/2018 | 04/06/2018 | wet
1 | 05/06/2018 | 05/06/2018 | dry
2 | 02/06/2018 | 03/06/2018 | wet
So if the state of a rock does not change from one day to another, I need that data to be "grouped".
Can I do this in Power Query? Is it possible to treat each line, combining them if they are of the same state, respecting the cronological order?
Is there a better way to treat this data so that I can visualize and show these ranges in a View without having to do so much work?
I could also have an events table with elapsed days in that state, if that was easier.
Also, all my tables are using date, not datetime.
Thanks in advance!
