Solved: How to Group Consecutive Datetimes

JDR100 · ‎10-03-2023

I have a series of date times.

I want to group consecutive datetimes where there is a maximum of 10 minutes between a period.

I can easily find the time diff between consectuve times by adding index and subtracting. The challenge is from there to add a column to group these by.

ImkeF · ‎10-03-2023

Hi @JDR100 ,
you can add a column where you check if the calculated time is larger than your threshold and if so, return the index else return null. This will return a table with a number in it whenever a new group must be created.
Then check that new column and fill down values. That will fill down the index value and you can then group on it.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

ImkeF · ‎10-03-2023

Hi @JDR100 ,
you can add a column where you check if the calculated time is larger than your threshold and if so, return the index else return null. This will return a table with a number in it whenever a new group must be created.
Then check that new column and fill down values. That will fill down the index value and you can then group on it.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries