Hello world of Stack Overflow. Thank you for your attention. I have 2 tables with the following data in Power Query, shown below.
Table 1: Shift Patterns
| Day | Early Start Time | Early End Time | Late Start Time | Late End Time | Early Work Hours | Late Work Hours | Total Work Hours |
|---|---|---|---|---|---|---|---|
| Mon | 07:30:00 | 15:15:00 | 15:10:00 | 22:10:00 | 7.75 | 7.00 | 14.75 |
| Tue | 07:30:00 | 15:15:00 | 15:10:00 | 22:10:00 | 7.75 | 7.00 | 14.75 |
| Wed | 07:30:00 | 15:15:00 | 15:10:00 | 22:10:00 | 7.75 | 7.00 | 14.75 |
| Thu | 07:30:00 | 15:15:00 | 15:10:00 | 22:10:00 | 7.75 | 7.00 | 14.75 |
| Fri | 07:00:00 | 13:00:00 | 12:55:00 | 18:55:00 | 6.00 | 6.00 | 12.00 |
Table 2: Work Processes
| Process | Start DateTime | Start Day | End DateTime | End Day | Total Process Hours (expected result) |
|---|---|---|---|---|---|
| 1 | 03-Jun-2024 10:30:00 | Mon | 07-Jun-2024 18:25:00 | Fri | 67.50 |
| 2 | 04-Jun-2024 11:00:00 | Tue | 11-Jun-2024 10:00:00 | Tue | 84.75 |
| 3 | 05-Jun-2024 12:00:00 | Wed | 05-Jun-2024 22:10:00 | Wed | 10.25 |
I need a Power Query formula that calculates Total Process Hours in Table 2, which takes the shift patterns from Table 1 into account and excludes weekends. There are no shifts on weekends or public holidays. For example, in Table 2:
-
Process 1: Total Process Hours = Hours put into Process during work times from Mon 03-Jun to Fri 07-Jun
= Mon hours (10:30:00 to 22:10:00) 11.75 + Tue hours (full day) 14.75 + Wed hours (full day) 14.75 + Thu hours (full day) 14.75 + Fri hours (07:00:00 to 17:40:00) 11.50
= 67.50 hours -
Process 2: Total Process Hours = Hours put into Process during work times from Tue 04-Jun to Tue 11-Jun
= Mon hours (11:00:00 to 22:10:00) 11.25 + Tue hours (full day) 14.75 + Wed hours (full day) 14.75 + Thu hours (full day) 14.75 + Fri hours (full day) 12.00 + Mon hours (full day) 14.75 + Tue hours (07:30:00 to 10:00:00) 2.50
= 84.75 hours -
Process 3: Total Process Hours = Hours put into Process during work times on Wed 05-Jun
= (12:00:00 to 22:10:00)
= 10.25 hours
I have tried to hard code it and used various if/else statements, but nothing is producing accurate and repeatable results I need, no matter what the input from Table 2 is. Assume data format will always be correct and nulls are not allowed.
Any help will be immensely appreciated! Thank you <3
Mr Tea is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.