Forum Discussion
Solved
Pls help with M Code/DAX: Identifying Overlaps in Work Time Entries
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 |
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
2 Replies
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 Custom2Thank 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?
