Generate the time series between 9AM to 9PM at 15 minutes interval. Count the number of employees present during a given interval. If start time is 11AM, it will be counted in 11:00AM-11:15AM not in 10:45AM-11:00AM If end time is 11AM, it will be counted in 10:45AM-11:00AM not in 11:00AM-11:15AM.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 142
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Employee Count in Intervals with Power Query
Power Query solution 1 for Employee Count in Intervals, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Type = Table.TransformColumnTypes(Source,{{"Start Time", type time}, {"End Time", type time}}),
Time1 = List.Times(hashtag#time(9,0,0), 12*4+1, hashtag#duration(0,0,15,0)),
Time2 = List.Skip(Time1)&{ hashtag#time(24,0,0)},
Table = Table.FromColumns({Time1,Time2}, {"A","B"}),
Sol = Table.AddColumn(Table, "Count", (x)=>
let
a = List.Zip(Table.ToColumns(Type)),
b = List.Count(List.RemoveNulls(List.Transform({0..List.Count(a)-1}, each
if a{_}{1} >= x[B] then null else
if a{_}{1} >= x[A] and a{_}{1} <= x[B] then 1 else
if a{_}{2} >= x[A] and a{_}{2} <= x[B] then 1 else
if x[A] > a{_}{1} and x[B] < a{_}{2} then 1 else
null)))
in b)[Count]
in
Sol
Power Query solution 2 for Employee Count in Intervals, proposed by Alexis Olson:
let
EmployeeSchedules = Table.TransformColumnTypes(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{{"Employee", type text}, {"Start Time", type time}, {"End Time", type time}}
),
Times = List.Times(hashtag#time(9, 0, 0), 4 * 12 + 1, hashtag#duration(0, 0, 15, 0)),
Intervals = Table.FromColumns({Times, List.Skip(Times)}, {"Start", "End"}),
AddTimeCol = Table.AddColumn(
Intervals,
"Time",
each Time.ToText([Start], [Format = "hh:mm:ss tt-"])
& Time.ToText([End] ?? hashtag#time(0, 0, 0), [Format = "hh:mm:ss tt"]),
type text
),
AddCountCol = Table.AddColumn(
AddTimeCol,
"Count",
(intvl) =>
Table.RowCount(
Table.SelectRows(
EmployeeSchedules,
(sched) =>
sched[Start Time] < intvl[End] and
sched[Start Time] <= intvl[Start] and
sched[End Time] > intvl[Start] and
sched[End Time] >= intvl[End]
)
),
Int64.Type
),
Result = Table.SelectColumns(AddCountCol, {"Time", "Count"})
in
Result
Power Query solution 3 for Employee Count in Intervals, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
L = Table.FromList(List.Times(hashtag#time(9,0,0),Duration.TotalMinutes(hashtag#time(21,15,0)-hashtag#time(9,0,0))/15,hashtag#duration(0,0,15,0)), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
A = Table.AddIndexColumn(L, "Index", 1, 1, Int64.Type),
Z = Table.AddColumn(A, "To", each try A[Column1]{[Index]} otherwise hashtag#time(24,0,0)),
T = Table.AddColumn(Z, "Time", each Text.Combine({Text.From([Column1], "en-US"), Text.From([To], "en-US")}, "-"), type text),
C = Table.TransformColumnTypes(S1,{{"Start Time", type time}, {"End Time", type time}, {"Employee", type text}}),
P = Table.AddColumn(C, "T", each let
S=Number.Round(Number.From([Start Time])* 96)/96,
E=Number.RoundUp (Number.From([End Time])* 96)/96,
D=Duration.TotalMinutes(Time.From(E)-Time.From(S))/15,
I=hashtag#duration(0,0,15,0),
L=List.Times(Time.From(S),D,I)
in
L),
E = Table.ExpandListColumn(P, "T"),
G = Table.Group(E, {"T"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
Q = Table.NestedJoin(T,{"Column1"},G,{"T"},"N",JoinKind.LeftOuter),
E2 = Table.ExpandTableColumn(Q, "N", {"Count"}, {"Count"}),
S = Table.Sort(E2,{{"Column1", Order.Ascending}}),
X = Table.SelectColumns(S,{"Time", "Count"})
in
X
Solving the challenge of Employee Count in Intervals with Excel
Excel solution 1 for Employee Count in Intervals, proposed by Bo Rydobon 🇹🇭:
=LET(f,SEQUENCE(49,,36)/96,t,VSTACK(DROP(f,1),1),m,"hh:mm:ss AM/PM",
HSTACK(TEXT(f,m)&"-"&TEXT(t,m),COUNTIF(B2:B4,"<"&t)-COUNTIF(C2:C4,"<="&f)))Excel solution 2 for Employee Count in Intervals, proposed by محمد حلمي:
=LET(q,1/96,i,SEQUENCE(49,,9/24,q),x,"[$-F400]",
HSTACK(TEXT(i,x)&"-"&TEXT(IF(i+q>7/8,1,i+q),x),
MAP(i,LAMBDA(a,SUM(N((B2:B4a)))))) Excel solution 3 for Employee Count in Intervals, proposed by Kris Jaganah:
=LET(a,1/24/4,b,ROUND(INT(B2:B4*24*4)*a,5),c,C2:C4,d,ROUND(SEQUENCE(49,,9/24,a),5),e,"hh:mm:ss am/pm",VSTACK({"Time","Count"},HSTACK(TEXT(d,e)&"-"&TEXT(VSTACK(DROP(d,1),INT(@d)+1),e),MAP(d,LAMBDA(x,SUM((x>=b)*(xExcel solution 4 for Employee Count in Intervals, proposed by LEONARD OCHEA 🇷🇴:
=LET(q,1/96,s,SEQUENCE(49,,9/24,q),r,s+q,t,IF(r=MAX(r),1,r),F,LAMBDA(x,TEXT(x,"hh:mm:ss AM/PM")),VSTACK({"Time","Count"},HSTACK(F(s)&"-"&F(t),COUNTIFS(B2:B4,"<"&t,C2:C4,">"&s))))Excel solution 5 for Employee Count in Intervals, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(w;LAMBDA(a;b;a+b);q;SEQUENCE(49;;TEXT("00:15";"s:dd");0);TOCOL(BYCOL(VALUE(TEXTSPLIT(TEXTJOIN("?";;TRANSPOSE(MAP(B2:B4;C2:C4;LAMBDA(x;y;TEXTJOIN(";";;MAP(SCAN(VALUE(TEXT("08:45";"s:dd"));q;w);SCAN(VALUE(TEXT("09:00";"s:dd"));q;w);LAMBDA(a;b;IF(AND(a>=x;b<=y);1;0))))))));";";"?";;));LAMBDA(a;SUM(a)))))Excel solution 6 for Employee Count in Intervals, proposed by Edwin Tisnado:
=LET(s,SEQUENCE(49,,9/24,1/96),v,VSTACK(DROP(s,1),1),f,"hh:mm am/pm",HSTACK(TEXT(s,f)&"-"&TEXT(v,f),MAP(s,v,LAMBDA(x,y,SUM((B2:B4Solving the challenge of Employee Count in Intervals with R
R solution 1 for Employee Count in Intervals, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(lubridate)
input = read_excel("Power Query/PQ_Challenge_142.xlsx", range = "A1:C4")
%>% janitor::clean_names()
test = read_excel("Power Query/PQ_Challenge_142.xlsx", range = "E1:F49")
input <- input %>%
mutate(interval = interval(ymd_hms(start_time), ymd_hms(end_time)))
quarter_table <- tibble(
interval = interval(
seq(ymd_hms("1899-12-31 09:00:00"),
ymd_hms("1899-12-31 20:45:00"),
by = "15 mins"),
seq(ymd_hms("1899-12-31 09:14:59"),
ymd_hms("1899-12-31 20:59:59"),
by = "15 mins")
)
)
head_count <- quarter_table %>%
mutate(
Count = map_dbl(interval, ~sum(int_overlaps(.x, input$interval))),
Time = paste(format(int_start(interval), "%I:%M:%S %p"),
format(int_end(interval), "%I:%M:%S %p"),
sep = " - ")
) %>%
select(Time, Count)
&&&