Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.