Search code examples
datetimepowerbidaxpowerquery

In Power Query, how do I calculate total time worked over multiple days based on work shift patterns and excluding weekends?

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 Total Work Hours
Mon 07:30:00 15:15:00 15:10:00 22:10:00 14.67
Tue 07:30:00 15:15:00 15:10:00 22:10:00 14.67
Wed 07:30:00 15:15:00 15:10:00 22:10:00 14.67
Thu 07:30:00 15:15:00 15:10:00 22:10:00 14.67
Fri 07:00:00 13:00:00 12:55:00 18:55:00 11.92

Table 2: Work Processes

Process Start DateTime Start Day End DateTime End Day Process Duration Hours (expected result)
1 03-Jun-2024 10:30:00 Mon 07-Jun-2024 18:25:00 Fri 67.10
2 04-Jun-2024 11:00:00 Tue 11-Jun-2024 10:00:00 Tue 69.60
3 05-Jun-2024 11:55:00 Wed 05-Jun-2024 22:10:00 Wed 10.25
4 06-Jun-2024 14:00:00 Thu 06-Jun-2024 14:00:01 Thu 0.00028
5 01-Nov-2021 23:38:59 Mon 02-Nov-2021 00:35:51 Tue 0.95

I need a Power Query formula that calculates Process Duration Hours in Table 2, which takes the shift hours from Table 1 and overtime into account. There are no shifts or overtime on weekends or public holidays. For example, in Table 2:

  1. Process 1: Duration = 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.67 + Tue hours (full day) 14.67 + Wed hours (full day) 14.67 + Thu hours (full day) 14.67 + Fri hours (07:00:00 to 17:40:00) 11.42 = 67.10 hours

  2. Process 2: Duration = Hours put into Process during work times from Tue 04-Jun to Tue 11-Jun = Tue hours (11:00:00 to 22:10:00) 11.17 + Wed hours (full day) 14.67 + Thu hours (full day) 14.67 + Fri hours (full day) 11.92 + Mon hours (full day) 14.67 + Tue hours (07:30:00 to 10:00:00) 2.50 = 69.60 hours

  3. Process 3: Duration = Hours put into Process during work times on Wed 05-Jun = (11:55:00 to 22:10:00) = 10.25 hours

  4. Process 4: Duration = Hours put into Process during work times on Thu 06-Jun = (14:00:00 to 14:00:01) = 0.00028 hours

  5. Process 5: Duration = Hours put into Process during overtime (outside shift hours) from Mon 01-Nov to Tue 02-Nov = (23:38:59 to 00:35:51) = 0.95 hours

(All values above rounded up for simplicity)

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

Solution

  • Powerquery code for Work Processes table

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Process", Int64.Type}, {"Start DateTime", type datetime}, {"Start Day", type text}, {"End DateTime", type datetime}, {"End Day", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "date", each {Number.IntegerDivide(Number.From([Start DateTime]),1)..Number.IntegerDivide(Number.From([End DateTime]),1)}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"date", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "day", each Text.Start(Date.DayOfWeekName([date], "en-US"),3),type text),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"day"}, #"Shift Patterns", {"Day"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Early Start Time", "Late End Time", "Total Work Hours"}, {"Early Start Time", "Late End Time", "Total Work Hours"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each ([Early Start Time] <> null)),
#"Added Custom2" = Table.AddColumn(#"Filtered Rows", "duration", each 
    if Time.From([Start DateTime]) > Time.From ([Late End Time]) and Number.IntegerDivide(Number.From([Start DateTime]),1) = Number.IntegerDivide(Number.From([date]),1)  // start after end time -- you are working overtime
    then Duration.TotalHours(#time(23,59,59)-Time.From([Start DateTime])) else 
    if Time.From([End DateTime]) < Time.From ([Early Start Time])  // start before start  time -- you are working overtime
    then Duration.TotalHours(Time.From([End DateTime])-#time(0,0,0)) else 
    if Number.IntegerDivide(Number.From([Start DateTime]),1) = Number.IntegerDivide(Number.From([End DateTime]),1)  // end date = start date
    then Duration.TotalHours(Time.From(Time.From([End DateTime]))-Time.From([Start DateTime])) else 
    if Number.IntegerDivide(Number.From([End DateTime]),1) = Number.IntegerDivide(Number.From([date]),1)  // last date of multiple dates
    then Duration.TotalHours(Time.From(Time.From([End DateTime]))-Time.From([Early Start Time])) else 
    if Number.IntegerDivide(Number.From([Start DateTime]),1) = Number.IntegerDivide(Number.From([date]),1)  // first date of multiple dates
    then Duration.TotalHours(Time.From(Time.From([Late End Time]))-Time.From([Start DateTime])) else 
    [Total Work Hours]),
#"Grouped Rows" = Table.Group(#"Added Custom2", {"Process", "Start DateTime", "Start Day", "End DateTime", "End Day"}, {{"duration", each List.Sum([duration]), type number}})
in  #"Grouped Rows"

    enter image description here