Elapsed time from a single column defined by state

[Anonymous]

Hi all,

I'm having an issue creating a column/measure that tells me the elapsed time during 2 different data states. 

I have a single column with date/time, and another with a detected state: "MOTION_DETECTED" and "NO_MOTION_DETECTED". 

Ideally, i'd like a column/measure that tells me the elapsed minutes from a motion has been detected, until it returns that no motion has been detected. Similarly, i would like a column/measure that tells me the elapsed time from no detected motion, until motion is detected. 

Any tips? Data sample can be seen below: 

[v-luwang-msft]

Hi @Anonymous ,

Could you pls show what you want expected output reusult according your base data?

Best Regards

Lucien

[Anonymous · Accepted solution]

Sorry for the late reply!

I found a solution to my problem by grouping the DeviceID together, and then indexing it by the timestamp. This guide was helpful in case others have a similar wish: 
https://radacad.com/power-query-transformation-pattern-convert-status-log-dates-table-to-start-and-e...