Grouping Similar DateTimes with tolerance of +/- 5 seconds

[tatmaninov]

I am working with a table of data and within this table there is a datetime value that logs when certain actions are created.  Within my datetimes column some of the values are different by a matter of seconds e.g. below.

What I want to do is where say for example the values are within 5 seconds of each other, I want to essentially treat this as the same datetime.  Whether this is achieved by actually changing the times or just assigning a group ID (example in second column) is not so important.

I have tried a couple different ways involving rounding the datetimes directly and also rounding the delta of the datetimes to a fixed point in time.  This seems to work for like 95% of the instances, but there are always some exceptions e.g. 47 seconds rounds down to 45 and 48 seconds rounds up to 50.  If I solve this by adjusting the rounding another exception occurs elsewhere.

Really appreciate any help to find a solution which is 100% reliable.

Original List	Group
01/01/2025 10:32:05	1
01/01/2025 10:32:06	1
01/01/2025 10:32:07	1
01/01/2025 10:32:08	1
01/01/2025 10:35:59	2
01/01/2025 10:36:00	2
01/01/2025 10:36:01	2

[ronrsnfld · Accepted solution]

Assuming your data is sorted Ascending as you show, you can use the 4th and 5th arguments of the Table.Group function for this purpose:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNTBUMDK2MjKwNTpVgdbOJmOMTNcYhbYBM3tTK1xCZuZmVggEPcUCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Original List" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Original List", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Original List"}, {
        {"Count", each Table.RowCount(_), Int64.Type}},
        GroupKind.Local,(x,y)=>Number.From(Duration.Seconds(y[Original List] -x[Original List])>5)
        )
in
    #"Grouped Rows"

From your data, the above code produces:

Note: If you are only grouping by a single column, then you don't need to have the grouped columns in a List, and you can remove the column specifiers from the 5th argument.

[v-venuppu]

Hi @tatmaninov ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @tatmaninov ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu]

Hi @tatmaninov ,

Thank you @PwerQueryKees @ronrsnfld @p45cal  for the prompt response.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[PwerQueryKees]

Question: How do you want to group 3 times, each 4 seconds apart?

1. All in the same group? Or
2. 1 and 2 in the same group and 3 in a new group? Or
3. Reduce to granularity of time keeping so every observation that falles within a pre-defined time-bucket of 5 seconds will fall in the same bucket?

Beacuse I don't get what you are trying to achieve. The solution @ronrsnfld provides implements scenario A: It will group every time <= 5 after the previous time in the same group. In the extreme, all you times will be in the same group regardless of the difference between the first and the last time.

I would choose for scenario C, by just rounding to 5 seconds. Nearest, Up or Down depending on what you want. You appear to have explore this route already, so I guess this is not what you want.

Kees Stolker

A big fan of Power Query and Excel

[p45cal]

re: "The solution @ronrsnfld provides implements scenario A: It will group every time <= 5 after the previous time in the same group. In the extreme, all you times will be in the same group regardless of the difference between the first and the last time."

I've been trying to understand (not very well yet) the use of the 5th Table.Group argument and from my experimentation find that what you say may not be true (I think)! Here I've used @ronrsnfld 's code on a different source data, 4 seconds apart as you suggest, and discover that they're not all in one group:

So what does x[Original List] and y[Original List] actually refer to at each row?

[ronrsnfld · Accepted solution]

See Imke Feldman's article Table.Group: Exploring the 5th element in Power BI and Power Query  for an explanation.

So far as this code, x[Original List] refers to the first Item in the Group.

y[Original List] refers to each subsequent item in the data.

When y[Original List] is more than 5 seconds distant from x[Original List], a new group is started and x[Original List] is updated to the start of the new Group.

So far as your original question, is what you get with the four second intervals using my code what you want? If so, please mark my response as an Answer. If not, please clarify exactly how you want things to be grouped.

The method I show starts at whatever is the "first" item, and then groups the subsequent items until it gets one that is more than 5 seconds distant from the "first". That ungrouped item then becomes the first item of the next group. And so on.

[PwerQueryKees]

Thanks @ronrsnfld , @for the reference to Imkes article! Enlightning! 

And sorry I was about to lead @tatmaninov @ in the wrong drirection. @ronrsnfld implements scenario A. 

Kees Stolker

A big fan of Power Query and Excel

[p45cal]

Re: "So far as this code, x[Original List] refers to the first Item in the Group.

y[Original List] refers to each subsequent item in the data."

Yes, that is the conclusion I was coming to despite all that I've seen so far is that x and y always referred to adjacent records. Thank you. I'll read Imke's article soonest.

Re:"So far as your original question, is what you get with the four second intervals using my code what you want?"

I'm not the OP!

[ronrsnfld]

Sorry about mistaking you for the OP.

Yes, Imke's article gives a pretty good explanation.

[ronrsnfld · Accepted solution]

Assuming your data is sorted Ascending as you show, you can use the 4th and 5th arguments of the Table.Group function for this purpose:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNTBUMDK2MjKwNTpVgdbOJmOMTNcYhbYBM3tTK1xCZuZmVggEPcUCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Original List" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Original List", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Original List"}, {
        {"Count", each Table.RowCount(_), Int64.Type}},
        GroupKind.Local,(x,y)=>Number.From(Duration.Seconds(y[Original List] -x[Original List])>5)
        )
in
    #"Grouped Rows"

From your data, the above code produces:

Note: If you are only grouping by a single column, then you don't need to have the grouped columns in a List, and you can remove the column specifiers from the 5th argument.