1

I have following data containing

  • a device
  • a timestamp of when the deviced logged the event
  • an event: ON or OFF

Device data

I would like to get a graph that shows per day the duration a device was active (duration = OFF timestamp - ON timestamp)

Duration graph

I can't figure out how to do this without manually creating a new table from my inputs like this

Device duration

Raw testdata Edited

device  timestamp   action
frig1   21/07/2022 10:30    ON
frig2   21/07/2022 10:32    ON
frig2   21/07/2022 10:35    OFF
frig1   21/07/2022 10:38    OFF
frig1   22/07/2022 10:30    ON
frig1   22/07/2022 10:37    OFF
frig2   22/07/2022 11:30    ON
frig2   22/07/2022 11:35    OFF
frig2   22/07/2022 11:59    ON
frig2   22/07/2022 12:06    OFF
Improve this question
2
  • 1
    Without an additional computation of the duration I do not see how this can fly. But is your issue that you do not want to create a table or is it the manual procedure of creating it?
    – karl
    Jul 22, 2022 at 11:47
  • @karl - My issue is mainly the manual procedure of creating it. If I can create the second table and have it synced with the source table (adding items on the fly), that would do too.
    – Lieven Keersmaekers
    Jul 22, 2022 at 12:01

1 Answer 1

Reset to default
1

Suppose your table is called 'raw':

enter image description here

Select any cell in the table and Use Data>Get & Transform Data>From Table/Range.

This will open the Power Query Editor with your new query:

enter image description here

Change the data types of "timestamp" to 'Date' and "action" to 'time' by clicking the icon in the top-left of the column header.

Select the 'State' column by clicking the column header.

Use Transform>Pivot column and configure it like this:

enter image description here

You should now have one row per device/date:

enter image description here

Use Add Column>Custom Column> and configure it like this:

enter image description here

Change the data type of the elapsed_time column to 'Duration'.

You should now have this:

enter image description here

Use Home>Close & Load To and select your preferred option. Either put the data into a worksheet or directly to a pivot table or pivot chart.

When you need to refresh this query, you just use Data>Refresh All or Ctrl+Alt+F5 or, if you've put the data into a worksheet, right-click the query result and select 'Refresh':

enter image description here

Improve this answer
5
  • 1
    This.is.awesome! I only had to make minor adjustment because I actually start with 3 columns io the 4 you used but at least, that is something I could handle myself. Thank you very much.
    – Lieven Keersmaekers
    Jul 22, 2022 at 14:04
  • I already know I'm going to come back to this answer so many times you should be getting royalties for it.
    – Lieven Keersmaekers
    Jul 22, 2022 at 14:06
  • 1
    @LievenKeersmaekers I see what happened now. I pasted your data into my spreadsheet and used text to columns. I guess it should've been timestamp with a date/time and action should be ON/OFF. Oh well, I guess the gist of the answer is still OK :)
    – FlexYourData
    Jul 22, 2022 at 16:21
  • my sample data contained just one ON/OFF per device per day. My actual data contains multiple ON/OFF per device per day. Do you happen to know how I can adjust for that? I have added a sample to the raw testdata to better show my intent.
    – Lieven Keersmaekers
    Jul 23, 2022 at 9:59
  • @FlexYourdate - figured it out (perhaps convoluted but it works). 1. Change type to decimal number io time. 2. Aggregate with sum. 3. Add new column [OFF] - [ON]. 4. Change type to time.
    – Lieven Keersmaekers
    Jul 24, 2022 at 17:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .