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!
You may take a look at the following post.