Common Digits in Numbers

Work out the total hours spent by an employee (Table T1). Every Weekday has different Start and End Time for which employees can charge (Table T2). If they work overtime or on weekends, they are not allowed to charge for any additional work. Weekday – 1 : Mon….Weekday – 5 : Fri Dates are in MDY format. Ex. Kathryn started on 5-Jan-24 at 11 AM which is a Friday. But billing is allowed from 12 PM only for Friday, hence she won’t get this 1 hour additional billing. She finished on 6-Jan-24 which is Sunday, hence she won’t get anything for Sunday work.

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 151
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Billable Hours by Weekday with Power Query

Power Query solution 1 for Billable Hours by Weekday, proposed by Zoran Milokanović:

let
  Source = each Excel.CurrentWorkbook(){[Name = _]}[Content], 
  S = Table.CombineColumns(
    Source("Table1"), 
    {"Start Date", "Start Time", "End Date", "End Time"}, 
    each List.Sum(
      List.Transform(
        List.DateTimes(_{0}, Duration.Days(_{2} - _{0}) + 1, Duration.From(1)), 
        (d) =>
          let
            r = Source("Table2"){[Weekday = Date.DayOfWeek(d, 1) + 1]}?
              ?? [Start Time = 0, End Time = 0], 
            g = (v, t, f) => if d = v and t >= r[Start Time] and t <= r[End Time] then t else f(r)
          in
            (g(_{2}, _{3}, each [End Time]) - g(_{0}, _{1}, each [Start Time])) * 24
      )
    ), 
    "Total Hours"
  )
in
  S

Power Query solution 2 for Billable Hours by Weekday, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  A = Table.AddColumn(
    T1, 
    "B", 
    each 
      let
        a = List.Transform(
          {Number.From(Date.From([Start Date])) .. Number.From(Date.From([End Date]))}, 
          each Date.DayOfWeek(Date.From(_))
        ), 
        b = List.Select(a, each _ <> 6 and _ <> 0), 
        c = Table.FromRows(
          List.Transform(b, each {_} & Record.ToList(T2{[Weekday = _]}[[Start Time], [End Time]])), 
          {"Day", "T2ST", "T2ET"}
        )
      in
        c
  ), 
  Expand = Table.ExpandTableColumn(A, "B", Table.ColumnNames(A[B]{0})), 
  TS = Table.AddColumn(
    Expand, 
    "TS", 
    each if [Start Time] > [T2ST] then [Start Time] * 24 else [T2ST] * 24
  ), 
  TE = Table.AddColumn(TS, "TE", each if [End Time] < [T2ET] then [End Time] * 24 else [T2ET] * 24), 
  Sol = Table.Group(TE, {"Employee"}, {{"Total Hours", each List.Sum([TE]) - List.Sum([TS])}})
in
  Sol

Power Query solution 3 for Billable Hours by Weekday, proposed by An Nguyen:

let
 Employee = Excel.CurrentWorkbook () { [Name = "Employee"] } [Content] ,
 WeekdayTbl = Excel.CurrentWorkbook () { [Name = "WeekdayTbl" ] } [Content] ,
 Employee_AsType = Table.TransformColumnTypes ( Employee , { {"Start Date", type date} , {"End Date" , type date} ,
 {"Start Time" , type time } , {"End Time", type time } } ) ,
 Weekday_AsType = Table.TransformColumnTypes ( WeekdayTbl , { {"Start Time" , type time} , {"End Time", type time} } ) ,
 CreateDates = Table.AddColumn ( Employee_AsType , "Dates" , each 
 List.Dates ( [Start Date] , Number.From( [End Date] ) - Number.From ( [ Start Date] ) + 1 ,
 hashtag#duration(1,0,0,0) 
 ) ) ,

Power Query solution 4 for Billable Hours by Weekday, proposed by An Nguyen:

let
 EndTime = if [Dates] = [End Date] then List.Min({[Scheduled End Time],[End Time]}) else [Scheduled End Time] ,
 Result = Duration.TotalHours( EndTime - List.Max({[Scheduled Start Time] , [Start Time]}) )
 in Result ) ,
 Groupby = Table.Group ( GetDuration , "Employee" , {"Total Hours", each List.Sum([Duration] ) } )
in
 Groupby

Power Query solution 5 for Billable Hours by Weekday, proposed by Eric Laforce:

let
 T2 = Table.Buffer(
 Excel.CurrentWorkbook(){[Name="tData151_2"]}[Content]),
 T1 = Table.TransformColumnTypes(
 Excel.CurrentWorkbook(){[Name="tData151_1"]}[Content],
 {{"Start Date", type date}, {"End Date", type date}}),
 Transform = Table.AddColumn(T1, "Total Hours", each let
 _V = Record.FieldValues(_),
 _D = List.Dates([Start Date], 
 Duration.Days([End Date]-[Start Date])+1, hashtag#duration(1,0,0,0)),
 _DD = List.TransformMany(_D, 
 (x)=>{Date.DayOfWeek(x)}, (x,y)=>T2{y-1}),
 _T = Table.RemoveRowsWithErrors(
 Table.FromColumns({_D, _DD}, {"Date", "DD"})),
 Add_Hours = Table.AddColumn(_T, "Hours", each let
 _TS = List.Max({[DD][Start Time], 
 if ([Date]=_V{1}) then _V{2} else null}),
 _TE = List.Min({[DD][End Time], 
 if ([Date]=_V{3}) then _V{4} else null})
 in Duration.TotalHours(Time.From(_TE) - Time.From(_TS)) )
 in List.Sum(Add_Hours[Hours]) ),
 Result =Transform[[Employee], [Total Hours]] 
in
 Result

Power Query solution 6 for Billable Hours by Weekday, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
  S2 = Excel.CurrentWorkbook(){[Name = "T_2"]}[Content], 
  Tbl2 = Table.TransformColumnTypes(
    S2, 
    {
      {"WeekDay", Int64.Type}, 
      {"Start Time", type time}, 
      {"End Time", type time}, 
      {"Duration", Int64.Type}
    }
  ), 
  S1 = Excel.CurrentWorkbook(){[Name = "T_1"]}[Content], 
  A2 = Table.AddColumn(S1, "WeekDay", each {Number.From([Start Date]) .. Number.From([End Date])}), 
  E2 = Table.ExpandListColumn(A2, "WeekDay"), 
  C2 = Table.TransformColumnTypes(
    E2, 
    {{"WeekDay", type date}, {"End Time", type time}, {"Start Time", type time}}
  ), 
  R = Table.SelectColumns(C2, {"Employee", "WeekDay", "Start Time", "End Time"}), 
  Tbl1 = Table.TransformColumns(R, {{"WeekDay", Date.DayOfWeek, Int64.Type}}), 
  C1 = Table.NestedJoin(Tbl1, {"WeekDay"}, Tbl2, {"WeekDay"}, "N", JoinKind.LeftOuter), 
  E = Table.ExpandTableColumn(C1, "N", {"Start Time", "End Time"}, {"N.Start Time", "N.End Time"}), 
  A = Table.AddColumn(
    E, 
    "Duration", 
    each try
      (if [End Time] > [N.End Time] then [N.End Time] else [End Time])
        - (if [Start Time] > [N.Start Time] then [Start Time] else [N.Start Time])
    otherwise
      null
  ), 
  C = Table.TransformColumns(A, {{"Duration", Duration.TotalHours, type number}}), 
  Sol = Table.Group(C, {"Employee"}, {{"Total Hour", each List.Sum([Duration]), type number}})
in
  Sol

Power Query solution 7 for Billable Hours by Weekday, proposed by Glyn Willis:

let
  t2 = Table.TransformColumnTypes(
    Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
    {{"Start Time", type time}, {"End Time", type time}}
  ), 
  t1 = Table.TransformColumnTypes(
    Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
    {
      {"Start Date", type date}, 
      {"Start Time", type time}, 
      {"End Date", type date}, 
      {"End Time", type time}
    }
  ), 
  #"Added Custom" = Table.AddColumn(
    t1, 
    "Custom", 
    each [
      ed = Date.DayOfWeek([End Date], Day.Sunday), 
      et = [End Time], 
      sd = Date.DayOfWeek([Start Date], Day.Sunday), 
      st = [Start Time], 
      d = Table.Combine(
        List.Transform(
          {Int64.From([Start Date]) .. Int64.From([End Date])}, 
          (x) => Table.SelectRows(t2, (y) => y[Weekday] = Date.DayOfWeek(Date.From(x), Day.Sunday))
        )
      ), 
      s = Table.ReplaceValue(
        d, 
        (x) => x[Start Time], 
        (y) => if y[Weekday] = sd and y[Start Time] < st then st else y[Start Time], 
        Replacer.ReplaceValue, 
        {"Start Time"}
      ), 
      e = Table.ReplaceValue(
        s, 
        (x) => x[End Time], 
        (y) => if y[Weekday] = ed and y[End Time] > et then et else y[End Time], 
        Replacer.ReplaceValue, 
        {"End Time"}
      ), 
      r = List.Sum(
        Table.AddColumn(
          e[[Start Time], [End Time]], 
          "d", 
          (x) => Duration.Hours(x[End Time] - x[Start Time])
        )[d]
      )
    ][r]
  )
in
  #"Added Custom"

Power Query solution 8 for Billable Hours by Weekday, proposed by Arden Nguyen, CPA:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 a = List.TransformMany(
 Table.ToRecords(Source), 
 each List.DateTimes([Start Date],Duration.TotalDays([End Date] - [Start Date])+1, hashtag#duration(1,0,0,0)), 
 (x,y) => x 
 & [ Date = y,
 Day of Week = Date.DayOfWeek(y), 
 First Time = if y <> x[Start Date] then null else x[Start Time],
 Last Time = if y <> x[End Date] then null else x[End Time]
 ]),
 b = Table.FromRecords(a),
 c = Table.NestedJoin(b,"Day of Week",Table2,"Weekday","Schedule",JoinKind.Inner),
 d = Table.ExpandTableColumn(c, "Schedule", {"Start Time", "End Time", "Duration"}, {"Schedule.Start Time", "Schedule.End Time", "Schedule.Duration"}),
 e = Table.AddColumn(d, "Hours", each if [First Time] <> null then 24*([Schedule.End Time]-List.Max({[First Time],[Schedule.Start Time]}) ) 
 else if [Last Time] <> null then 24*(List.Min({[Last Time],[Schedule.End Time]}) - [Schedule.Start Time])
 else [Schedule.Duration]
 ),
 f = Table.Group(e, {"Employee"}, {{"Total", each List.Sum([Hours]), type number}})
in
 f

Solving the challenge of Billable Hours by Weekday with Excel

Excel solution 1 for Billable Hours by Weekday, proposed by Bo Rydobon 🇹🇭:

=HSTACK(A2:A6,MAP(B2:B6,C2:C6,D2:D6,E2:E6,LAMBDA(b,c,d,e,LET(w,WEEKDAY(SEQUENCE(d-b+1,,b),2),
SUM(IFERROR(INDEX(D10:D14,w),))-24*(MAX(,IFERROR(c-INDEX(B10:B14,@w),))+MAX(,IFERROR(INDEX(C10:C14,TAKE(w,-1))-e,)))))))

Excel solution 2 for Billable Hours by Weekday, proposed by محمد حلمي:

=LET(x,B2:B6,i,D2:D6,b,IF(WEEKDAY(x,2)>5,WORKDAY(+x,1),x),d,IF(WEEKDAY(i,2)>5,WORKDAY(+i,-1),i),MAP(b,IF(
x=b,C2:C6,B10),d,IF(i=d,E2:E6,C14),LAMBDA(b,c,d,e,LET(
j,WORKDAY(b-1,SEQUENCE(NETWORKDAYS(b,d))),
w,WEEKDAY(j)-1,v,LOOKUP(w,A10:C14),
r,LOOKUP(w,A10:B14),SUM(IF(j=MAX(j),
MIN(e,TAKE(v,-1)),v)-IF(j=@j,MAX(c,@r),r))*24))))

Excel solution 3 for Billable Hours by Weekday, proposed by LEONARD OCHEA 🇷🇴:

=VSTACK({"Employee","Total Hours"},HSTACK(A2:A6,MAP(B2:B6,C2:C6,D2:D6,E2:E6,LAMBDA(m,n,o,p,LET(s,SEQUENCE(o-m+1),d,WEEKDAY(m+s-1,2),F,LAMBDA(x,IFNA(VLOOKUP(d,A10:D14,x,0),0)),u,F(2),v,F(3),w,F(4),SUM(IFS((s=1)*w,w-IF(n>u,(n-u)*24,0),(s=MAX(s))*w,w-IF(v>p,(v-p)*24,0),1,w)))))))

Excel solution 4 for Billable Hours by Weekday, proposed by Edwin Tisnado:

=HSTACK(A2:A6,MAP(B2:B6,C2:C6,D2:D6,E2:E6,LAMBDA(a,b,c,d,LET(t,WEEKDAY(SEQUENCE(1+c-a,,a),2),j,INDEX(D10:D14,t),f,FILTER(t,NOT(ISERROR(j))),SUM(TOCOL(j,2))-24*(MAX(,b-INDEX(B10:B14,@f))+MAX(,INDEX(C10:C14,TAKE(f,-1))-d))))))

Excel solution 5 for Billable Hours by Weekday, proposed by LUIS FLORENTINO COUTO CORTEGOSO:

=HSTACK(A2:A6,MAP(B2:B6,C2:C6,D2:D6,E2:E6,LAMBDA(sd,st,ed,et,LET(s,WEEKDAY(SEQUENCE(ed-sd+1,,sd),2),t,SUM(TOCOL(XLOOKUP(s,A10:A14,D10:D14),3)),di,IFERROR(INDEX((st-B10:B14),@s),0),df,IFERROR(INDEX((C10:C14-et),CHOOSEROWS(s,-1)),0),t-((di>0)*di+(df>0)*df)*24))))

Solving the challenge of Billable Hours by Weekday with Python in Excel

Python in Excel solution 1 for Billable Hours by Weekday, proposed by An Nguyen:

Python in Excel : 
from datetime import datetime
def GetDuration(sd, ed, d, st, et, sst, set):
 start_time = max(st, sst) if d == sd else sst
 end_time = min(et, set) if d == ed else set
 return (datetime.combine(datetime.min, end_time) - datetime.combine(datetime.min, start_time)).seconds / 3600
employer_df = xl("A1:E6", headers=True)
weekday = xl("A9:D14", headers=True)
employer_df['Date'] = employer_df.apply(lambda x: pd.date_range(start=x['Start Date'], end=x['End Date']), axis=1)
employer_df = employer_df.explode("Date")
employer_df['Weekday'] = employer_df['Date'].dt.dayofweek + 1
merge_df = employer_df.merge(weekday, left_on='Weekday', right_on='Weekday', how='left')
merge_df['Total Hours'] = merge_df.apply(lambda x: 0 if pd.isnull(x['Duration']) else GetDuration(x['Start Date'], x['End Date'], x['Date'], x['Start Time_x'], x['End Time_x'], x['Start Time_y'], x['End Time_y']), axis=1)
groupby = merge_df.groupby("Employee", sort=False).agg({"Duration": "sum"}).reset_index()

Solving the challenge of Billable Hours by Weekday with R

R solution 1 for Billable Hours by Weekday, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
library(hms)
test = read_excel("Power Query/PQ_Challenge_151.xlsx", range = "G1:H6") %>%
 janitor::clean_names()
read_excel_range <- function(file, range) {
 read_excel(file, range = range) %>%
 mutate(across(c(starts_with("Start Time"), starts_with("End Time")), as_hms),
 across(c(starts_with("Start Date"), starts_with("End Date")), as_date)) %>%
 janitor::clean_names()
}
input1 <- read_excel_range("Power Query/PQ_Challenge_151.xlsx", "A1:E6")
input2 <- read_excel_range("Power Query/PQ_Challenge_151.xlsx", "A9:D14")
result <- input1 %>%
 mutate(
 start = as_datetime(start_date) + start_time,
 end = as_datetime(end_date) + end_time,
 datetime = map2(start, end, seq, by = "hour")
 ) %>%
 unnest(datetime) %>%
 mutate(
 weekday = wday(datetime, week_start = 1),
 time = as_hms(datetime)
 ) %>%
 left_join(input2, by = "weekday") %>%
 filter(datetime >= start & datetime <= end, 
 time >= start_time.y & time < end_time.y) %>%
 group_by(employee) %>%
 summarise(total_hours = n() %>% as.numeric())

&&&

Solving the challenge of Billable Hours by Weekday with Power Query

Solving the challenge of Billable Hours by Weekday with Excel

Solving the challenge of Billable Hours by Weekday with Python in Excel

Solving the challenge of Billable Hours by Weekday with R

Leave a Reply Cancel reply