Eni has attended:
Power BI Modelling, Visualisation and Publishing course
Status over time
I have a data set which lists the status of a device either as active or deactivated. The data is structured as a log entry which makes the data manipulation difficult. The output is to be able to show the total number of days we had deactivated equipment each month.
Devices can be deactivated over multiple months which also makes the data difficult to manage
RE: Status over time
Hi Eni,
Thank you for the forum question.
I do not know how your time stamps are structured but here you will find a way of getting what you want.
Use Power Query Editor to transform your data. Convert the timestamp column to a datetime format if it's not already in that format.
- Add a custom column to extract the month from the timestamp using the `Date.Month` function.
Add a custom column to identify when a device transitions from active to deactivated or vice versa.
- Use Query editor function `List.Accumulate` to calculate the duration of each deactivation period for each device.
- Group the data by the month column created earlier.
- Calculate the sum of deactivation durations for each month using the "Group By" feature in the Query editor or by creating a new table with summarized data.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
