Grouping rows with similar, but not identical time values

[MarkRS]

Hi Everybody, I've got a query where I group rows which have similar values in several columns, including one coulmn which contains a time value. I need to expand this to also group rows where the time values are within a certain tolerance of one another - for example 15 or 30 minutes. It would also be good to aggregate the min and max time values into new columns.

This is a bit more complicated than the standard group by functionality, so I wondered how to achieve this?

Many thanks

[amitchandak]

Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[MarkRS]

Hi,

Thank you for helping  - Here's a simplified example of the input:

Client ID	Planned Start Date	Planned Start Time	Planned Duration
1	20/07/2020	09:00	30
1	21/07/2020	09:00	30
1	22/07/2020	09:00	30
1	23/07/2020	09:00	30
1	24/07/2020	09:00	30
1	25/07/2020	08:45	30
1	26/07/2020	09:15	30
1	20/07/2020	12:00	30
1	21/07/2020	12:00	30
1	22/07/2020	12:00	30
1	23/07/2020	12:00	30
1	24/07/2020	11:30	30
1	25/07/2020	12:30	30
1	26/07/2020	12:30	30

I can use Table.Group to group all rows which have the same Client ID, Planned Start Time and Planned Duration:

= Table.Group(#"Changed Type", {"Client ID", "Planned Start Time", "Planned Duration"}, {{"data", each _, type table [Client ID=nullable number, Planned Start Date=nullable date, Planned Start Time=nullable time, Planned Duration=nullable number]}, {"Count", each Table.RowCount(_), Int64.Type}, {"Median Start Time", each List.Median([Planned Start Time]), type nullable time}})

Which gives this output:

Client ID	Planned Start Time	Planned Duration	data	Count	Median Start Time
1	09:00:00	30	table	5	09:00:00
1	08:45:00	30	table	1	08:45:00
1	09:15:00	30	table	1	09:15:00
1	12:00:00	30	table	4	12:00:00
1	11:30:00	30	table	1	11:30:00
1	12:30:00	30	table	2	12:30:00

This is working as expected, but I would like to group rows where the Planned Start Time is within 30 minutes.

This would result in the first three rows being grouped into one: Planned Start Time 09:00, 08:45, 09:15.

The Median Start Time aggregation would then show the median of these times.

The last three rows are a bit more complicated since the start times are 12:00, 11:30 and 12:30.

The first two rows (12:00, 11:30) are within 30 minutes of each other, so they could be grouped to form a group of 5 rows.

However, if the 12:00 and 12:30 rows could be grouped that would be even better as it would form a group of 6 rows.

I probably need to add a helper column to identify which rows to merge. Any help would be greatly appreciated.

Although I've been doing this in power query, it would be fine, or even preferable, to do it in DAX.

I've uploaded a sample pbix here:

https://ufile.io/xyd0n3u9

Many thanks