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 realiz...
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
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
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?