Time Difference in between rows with condition

[johnny_green]

Hello,

I have some raw data csv file that is logging the access time on a kiosk. Is there any formula can do below logic?

- Time difference between row. (eg. Row 2 - Row 3 = Result display on Column4)

- If the difference let's say bigger than 12 hours. Output "Null" on Column4

Thank you all for the great help.

Johnny

[dearwatson · Accepted solution]

This can get tricky.

To resolve this I normally do a "self join" here's the steps:

1 .Merge the 2 columns with a space seperator and change type into a DATETIME column.. also sort it Ascending.. (Oldest at the top) u will need this later.

2. Create a duplicate of the query... in fact make two... usually I will reference the original query TWICE then disable load on the original query - this means I can go back and change the original easily if I want without breaking stuff..

3. create an index column on the new querys.. the first index must start with 0 and then the second one starts with 1. you now have 2 queries with an index OFFSET by 1... see screenshot:

Index starting at 0

Index starting at 1:

4. Now select the query with the index that starts with 0 - this is your main query now... you use this to "Merge Queries" to the other one by the index field...

5. Expand the datetime from the merged query and this will give you the yesterdays value for all records except the first one.

6. Then do the simple equation to get the difference...

I'm sure there's a cooler way... I think you can do some fancy DAX with EARLIER etc... to do this too but I prefer this method as it gives me better data lineage... as in I can work out issues more easily.

Cheers

Greg

[v-huizhn-msft · Accepted solution]

Hi @johnny_green,

Apart from the solution @dearwatson posted, you also can use the following solution.

1. Merge the column2 and column3 in Power Query.  Add a index column in Power Query according to @dearwatson posted.

2. Create a calculated column using the following formula.

Column4 = IF(DATEDIFF(LOOKUPVALUE(Test[Merged],Test[Index],Test[Index]-1),Test[Merged],MINUTE)>720,BLANK(),DATEDIFF(LOOKUPVALUE(Test[Merged],Test[Index],Test[Index]-1),Test[Merged],MINUTE))

Please see the expected result.



Best Regards,
Angelia

[dearwatson · Accepted solution]

This can get tricky.

To resolve this I normally do a "self join" here's the steps:

1 .Merge the 2 columns with a space seperator and change type into a DATETIME column.. also sort it Ascending.. (Oldest at the top) u will need this later.

2. Create a duplicate of the query... in fact make two... usually I will reference the original query TWICE then disable load on the original query - this means I can go back and change the original easily if I want without breaking stuff..

3. create an index column on the new querys.. the first index must start with 0 and then the second one starts with 1. you now have 2 queries with an index OFFSET by 1... see screenshot:

Index starting at 0

Index starting at 1:

4. Now select the query with the index that starts with 0 - this is your main query now... you use this to "Merge Queries" to the other one by the index field...

5. Expand the datetime from the merged query and this will give you the yesterdays value for all records except the first one.

6. Then do the simple equation to get the difference...

I'm sure there's a cooler way... I think you can do some fancy DAX with EARLIER etc... to do this too but I prefer this method as it gives me better data lineage... as in I can work out issues more easily.

Cheers

Greg