Overlapping Time Intervals

[Anonymous]

Hello Everyone

I have been struggling with a problem involving overlapping times and dates. I have a very large document with a ton of potential time clock overlapps. I have tried the solutions listed in the link below. How to Get Your Question Answered Quickly - Microsoft Fabric Community Sadly, this solution hasn't worked for me and I was looking for a solution hopefully through Power Query. I am trying to find a way to flag when there are overlapping times clocked in.  My data is listed below. This is a simplified document with the 3 columns of dupMeas_Payroll_ID_Date, StartTimeF, and EndTimeF. All 3 are constructed with combining multiple columns such as dates, times, and Payroll_ID. My data is listed below. If someone would prefer I repost the data constructed in a different way, I can do that.

Partition.DupMeas_Payroll_ID_Date	data.Partition.StartTimeF	data.Partition.EndTimeF
103573 , 1/11/2022	1/11/2022 8:00	1/11/2022 9:20
103573 , 1/11/2022	1/11/2022 9:20	1/11/2022 10:00
103573 , 1/11/2022	1/11/2022 10:00	1/11/2022 10:20
103573 , 1/11/2022	1/11/2022 10:20	1/11/2022 10:40
103573 , 1/11/2022	1/11/2022 10:40	1/11/2022 11:00
103573 , 1/11/2022	1/11/2022 11:00	1/11/2022 11:20
103573 , 1/11/2022	1/11/2022 11:20	1/11/2022 12:10
103573 , 1/11/2022	1/11/2022 12:10	1/11/2022 12:25
103573 , 1/11/2022	1/11/2022 12:40	1/11/2022 13:00
103893 , 1/11/2022	1/11/2022 8:25	1/11/2022 9:20
103893 , 1/11/2022	1/11/2022 9:20	1/11/2022 10:00
103893 , 1/11/2022	1/11/2022 10:00	1/11/2022 10:20
103893 , 1/11/2022	1/11/2022 10:20	1/11/2022 10:40
103893 , 1/11/2022	1/11/2022 10:40	1/11/2022 11:00
103893 , 1/11/2022	1/11/2022 11:00	1/11/2022 11:20
103893 , 1/11/2022	1/11/2022 11:20	1/11/2022 12:20
103893 , 1/11/2022	1/11/2022 12:10	1/11/2022 12:25
103893 , 1/11/2022	1/11/2022 12:40	1/11/2022 13:00

Partition.DupMeas_Payroll_ID_Date	data.Partition.StartTimeF	data.Partition.EndTimeF
103573 , 1/11/2022	1/11/2022 8:00	1/11/2022 9:20
103573 , 1/11/2022	1/11/2022 9:20	1/11/2022 10:00
103573 , 1/11/2022	1/11/2022 10:00	1/11/2022 10:20
103573 , 1/11/2022	1/11/2022 10:20	1/11/2022 10:40
103573 , 1/11/2022	1/11/2022 10:40	1/11/2022 11:00
103573 , 1/11/2022	1/11/2022 11:00	1/11/2022 11:20
103573 , 1/11/2022	1/11/2022 11:20	1/11/2022 12:10
103573 , 1/11/2022	1/11/2022 12:10	1/11/2022 12:25
103573 , 1/11/2022	1/11/2022 12:40	1/11/2022 13:00
103893 , 1/11/2022	1/11/2022 8:25	1/11/2022 9:20
103893 , 1/11/2022	1/11/2022 9:20	1/11/2022 10:00
103893 , 1/11/2022	1/11/2022 10:00	1/11/2022 10:20
103893 , 1/11/2022	1/11/2022 10:20	1/11/2022 10:40
103893 , 1/11/2022	1/11/2022 10:40	1/11/2022 11:00
103893 , 1/11/2022	1/11/2022 11:00	1/11/2022 11:20
103893 , 1/11/2022	1/11/2022 11:20	1/11/2022 12:20
103893 , 1/11/2022	1/11/2022 12:10	1/11/2022 12:25
103893 , 1/11/2022	1/11/2022 12:40	1/11/2022 13:00

My desired result would be a way to flag overllapping times for the specific day and Payroll Id. For example, the 2 lines highlighted in Blue have an overlapping time interval. Flagging these 2 lines so a simple filter could show a table with all overlapping times would be great. The end result for running it on this data would look like.

Partition.DupMeas_Payroll_ID_Date	data.Partition.StartTimeF	data.Partition.EndTimeF
103893 , 1/11/2022	1/11/2022 11:20	1/11/2022 12:20
103893 , 1/11/2022	1/11/2022 12:10	1/11/2022 12:25

There are a lot of clock in/out times for each payroll_id.

Thank you very much in advance

[ThxAlot · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZJBDoQgDEWvQliT2BaYUa5ivAfHn1EmEyzSdme+75VC/r57hJjf0QWHC+JCQOSD/3+7tQDcgq0Q+COo4sX1AcI5ymA2kCW2QxvIkmRVE1PRujAOC6N1YRwWpoI29QJZQtmo8rvG7q7rJvXhe8SsD5Io90EylT4oqtQHRZX6IKpyHxR17INRlfugqNM+1FrPPw/wjaYZHZ/onCd0+r1cF73ms/M4u9HHBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Partition.DupMeas_Payroll_ID_Date = _t, data.Partition.StartTimeF = _t, data.Partition.EndTimeF = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Partition.DupMeas_Payroll_ID_Date", type text}, {"data.Partition.StartTimeF", type datetime}, {"data.Partition.EndTimeF", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Partition.DupMeas_Payroll_ID_Date", Order.Ascending}, {"data.Partition.StartTimeF", Order.Ascending}}),
    Grouped = Table.Group(#"Sorted Rows", Table.ColumnNames(Source), {"grp", each _}, 0, (x,y) => Byte.From(x[Partition.DupMeas_Payroll_ID_Date] <> y[Partition.DupMeas_Payroll_ID_Date] or y[data.Partition.StartTimeF] >= x[data.Partition.EndTimeF])),
    Selected = Table.Combine(List.Select(Grouped[grp], each Table.RowCount(_) > 1))
in
    Selected