Pls help with M Code/DAX: Identifying Overlaps in Work Time Entries

[bertferschner]

My goal is to identify overlaps in timesheet entries, but I'm struggling to find the correct logic structure for a solution. I prefer to work mostly in Power Query to reduce measures, but also realize it could also be more effective/simpler with DAX. For each Resource ID, need to check if the Work # schedules overlap. I do not need to know if separate Resource IDs schedules overlap.

Resource ID	Work #	Monday Schedule Begin	Monday Schedule End	Monday Schedule
7701	K1
7701	K2
7701	K3	9:00:00	11:00:00	09:00a - 11:00a
7701	K4	7:00:00	9:00:00	07:00a - 09:00a
7701	K5	10:00:00	17:00:00	10:00a - 05:00p
7701	K6	15:00:00	16:00:00	03:00p - 04:00p
7703	K1
7703	Holiday	6:00:00	16:00:00	06:00a - 04:00p
7703	SAFETY TRAINING
7780	K1	7:00:00	11:00:00	7:00a - 11:00a
7780	K2	10:00:00	16:00:00	10:00a - 04:00p
7781	K1	7:00:00	16:00:00	07:00a - 04:00p

[wdx223_Daniel · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBRC4IwEMe/yrBXg01zs96KrCTyQYUI6UF0VBQuih769t0s3bQJ23H873532z/LLMvWztHOrJg/xetRcBQuQdqLxxWNINmJqszfKCnOvHzdOFrw06Uy6EFV/qv1YMYwgdJWBtReveIMVlwI0xnGcCAjpE2xVHM0RrWWd6AJBNZ2KhyzH/OFO4wnx2O1SfG1WlMeJPcORWXdU61U7XJls6QmOuUOGiErG3G7gH2QUeNM2rzkb2YyXwXpAaXxPIzCaG1Y4ONmNTP5yUx2fhmnZw01WKM/yCeGTdovWA86fgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    Custom1 = Table.PromoteHeaders(Table.Skip(Source)),
    #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Monday Schedule Begin", type time}, {"Monday Schedule End", type time}}),
    Custom2 = Table.Group(#"Changed Type","Resource ID",{"overlap Y/N",each let a=List.RemoveNulls(List.Combine(Table.ToRows(Table.Sort(_,each [#"Work #"])[[Monday Schedule Begin],[Monday Schedule End]]))) in a<>List.Sort(a)})
in
    Custom2

[bertferschner]

Thank you for the quick reply! After applying to my non-anonymized dataset, I realized I did a poor job of characterizing my business requirement. However, you have provided the solution to my posted requirement, so I will mark as solved 🙂 May I reach out to you with an different scenario?