Count Early, On Time, Late and Out of Limit for T2 against T1. Early : If started within 30 mins of Start Time Late: if started within 30 mins of End Time (if there is overlap of 30 mins, for both Early and Start for different intervals, then it can be counted as Late. This is case for C where 02:30PM is one End Time and 03:30PM is one Start Time. 3:00PM is exactly 30 mins from both 02:30PM and 03:30PM, hence 3:00PM will be counted as late) On Time: If started within Start Time and End Time Out of Limit: if it doesn’t meet Early, Late and On Time conditions
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 114
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Categorize Time Intervals Status with Power Query
Power Query solution 1 for Categorize Time Intervals Status, proposed by Bo Rydobon 🇹🇭:
let
T1 = Table.Buffer(
Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
"E",
each [Start Time] + 1 / 48
)
),
Has = (T, s, v, w, e) =>
Table.RowCount(Table.SelectRows(T, (T) => Record.Field(T, s) < v and w <= Record.Field(T, e)))
> 0,
T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Cat = Table.AddColumn(
T2,
"T",
each
let
f = Table.SelectRows(T1, (T) => T[Group] = [Group]),
t = [Start Time],
v = Number.Mod(t + 1 / 48, 1)
in
if Has(f, "Start Time", t, t, "End Time") then
"On Time"
else if Has(f, "End Time", t, Number.Round(t - 1 / 48, 6), "End Time") then
"Late"
else if Has(f, "Start Time", v, v, "E") then
"Early"
else
"Out of Limit"
),
Pivot = Table.SelectColumns(
Table.Pivot(Cat, List.Distinct(Cat[T]), "T", "Start Time", List.Count),
{"Group", "Early", "On Time", "Late", "Out of Limit"}
)
in
PivotPower Query solution 2 for Categorize Time Intervals Status, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
G = {"Early", "On Time", "Late", "Out of Limit"},
RC = (t) => Table.RowCount(t),
NR = (n) => Number.Round(n, 15),
T = (GR, ST, T) =>
Table.SelectRows(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
each
let
s = [Start Time],
e = [End Time]
in
[Group]
= GR
and (
(T = G{1} and ST >= s and ST <= e)
or (T = G{0} and NR(ST) >= NR(s - 3 / 144) and ST <= s)
or (T = G{2} and ST >= e and NR(ST) <= NR(e + 3 / 144))
)
),
R = Table.Group(
Table.AddColumn(
Source,
"C",
each
let
g = [Group],
s = [Start Time]
in
if RC(T(g, s, G{1})) > 0 then
G{1}
else if RC(T(g, s, G{2})) > 0 then
G{2}
else if RC(T(g, s, G{0})) > 0 then
G{0}
else
G{3}
),
{"Group", "C"},
{{"T", each RC(_)}}
),
S = Table.ReorderColumns(Table.Pivot(R, List.Distinct(R[C]), "C", "T"), {"Group"} & G)
in
SPower Query solution 3 for Categorize Time Intervals Status, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
MinDia = 60*24,
#"30Min" = Number.RoundDown(30/MinDia, 6),
Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Round1 = Table.TransformColumns(Table1,{{"Start Time", each Number.RoundDown(_, 6)}, {"End Time", each Number.Round(_, 6), type number}}),
Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Round2 = Table.TransformColumns(Table2,{{"Start Time", each
let
a = Number.RoundDown(_, 6),
b = if a < .9 then a else a-1
in b}}),
Merge = Table.NestedJoin(Round2, {"Group"}, Round1, {"Group"}, "Filtered Rows", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Merge, "Filtered Rows", {"Start Time", "End Time"}, {"Start Time.1", "End Time"}),
Power Query solution 4 for Categorize Time Intervals Status, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "t114"]}[Content],
T1 = Excel.CurrentWorkbook(){[Name = "t114_1"]}[Content],
CN = {"Early", "On Time", "Late", "Out of Limit"},
Group = Table.Group(
Source,
{"Group"},
{
"Data",
each
let
_G = _[Group]{0},
_TG = Table.SelectRows(T1, each [Group] = _G),
_LS = List.Accumulate(
_[Start Time],
{},
(s, c) =>
let
_AddS = Table.AddColumn(
_TG,
"Status",
each
let
st = [Start Time],
et = [End Time],
st30 = st - 1 / 48,
et30 = et + 1 / 48
in
if (c >= st and c <= et) then
"On Time"
else
(
if (
(st30 > 0 and c < st and c >= st30)
or (st30 < 0 and (c > 1 + st30 or c < st))
)
then
"Early"
else
(
if (
(et30 < 1 and c > et and c <= et30)
or (et30 > 1 and (c < 1 - et30 or c > et))
)
then
"Late"
else
"Out of Limit"
)
)
),
fxRC = (t, s) => Table.RowCount(Table.SelectRows(t, each [Status] = s)),
_S =
if (fxRC(_AddS, "On Time") > 0) then
"On Time"
else
(
if (fxRC(_AddS, "Late") > 0) then
"Late"
else
(if (fxRC(_AddS, "Early") > 0) then "Early" else "Out of Limit")
)
in
s & {_S}
),
_T = Table.FromColumns({_LS}, {"Status"})
in
Table.Pivot(_T, List.Distinct(_T[Status]), "Status", "Status", List.Count)
}
),
Exp = Table.ExpandTableColumn(Group, "Data", CN)
in
ExpPower Query solution 5 for Categorize Time Intervals Status, proposed by Szabolcs Phraner:
let
Source = Excel.CurrentWorkbook(),
//Transform data types for both tables
DataTypes = Table.TransformColumns( Source,
{{"Content", each Table.TransformColumnTypes(_, List.Transform(Table.ColumnNames(_), each {_, if _ ="Group" then type text else type time})) }}),
// Record of Tables
Table = Table.First( Table.Pivot(DataTypes, List.Distinct(DataTypes[Name]), "Name", "Content")),
//Join T2 to T1
T2_Join = Table.NestedJoin(Table[T_1], {"Group"}, Table[T_2], {"Group"}, "T2", JoinKind.LeftOuter),
ExpandT2 = Table.ExpandTableColumn(T2_Join, "T2", {"Start Time"}, {"T2"}),
Solving the challenge of Categorize Time Intervals Status with Excel
Excel solution 1 for Categorize Time Intervals Status, proposed by Bo Rydobon 🇹🇭:
=LET(a,A2:A9,b,B2:B9,c,C2:C9,gs,E2:E14,u,UNIQUE(gs),
t,MAP(gs,F2:F14,LAMBDA(g,t,LET(k,g=a,v,MOD(t+1/48,1),IFS(
OR(k*(t>=b)*(t<=c)),2,OR(k*(c=b)*(v Excel solution 2 for Categorize Time Intervals Status, proposed by محمد حلمي:
=LET(i,A2:A9,e,E2:E14,u,UNIQUE(i),aa,C2:C9,bb,B2:B9,p,30/1440,qq,bb-p,v,LAMBDA(q,MAP(u,LAMBDA(x,SUM(
(e=x)*MAP(e,F2:F14,LAMBDA(e,f,OR(MAP(i,bb,aa+q,LAMBDA(a,b,c,(a=e)*(f>=b)*(f<=c)))))))))),n,v(0),y,v(30/1440)-n,s,MAP(u,LAMBDA(x,SUM((e=x)*MAP(e,F2:F14,LAMBDA(e,f,OR(MAP(i,VSTACK(DROP(aa,1),1),aa,IF(qq>=0,qq,qq-1),IF(qq>=0,bb,qq+1),LAMBDA(a,mm,ww,b,c,(a=e)*(f>=b)*(f<=c)*(fExcel solution 3 for Categorize Time Intervals Status, proposed by LEONARD OCHEA 🇷🇴:
=LET(u,UNIQUE(A2:A9),h,HSTACK("Early","On Time","Late","Out of Limit"),HSTACK(VSTACK(A1,u),REDUCE(h,u,LAMBDA(a,b,LET(f,FILTER(B2:C9,A2:A9=b),e,TAKE(f,,1)-1/48,o,TAKE(f,,-1)+1/48,d,HSTACK(e,f,o),m,TOCOL(IF(d<>"",h,"")),n,TOCOL(d),p,IF(n<0,1+n,n),j,IF(p=VSTACK(DROP(p,1),""),"Late",m),k,FILTER(F2:F14,E2:E14=b),s,XLOOKUP(k,p+10^-9,j,"Out of Limit",-1),r,BYCOL(--(s=h),LAMBDA(a,SUM(a))),VSTACK(a,IF(r,r,"")))))))Excel solution 4 for Categorize Time Intervals Status, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=VSTACK(HSTACK("Group";"On Time";"Others");HSTACK(UNIQUE(E2:E14);MAP(UNIQUE(E2:E14);LAMBDA(p;SUM(IFERROR(MAP(E2:E14;IF(ISNUMBER(SEARCH(1;TEXTSPLIT(TEXTJOIN(",";;MAP(E2:E14;F2:F14;LAMBDA(a;b;TEXTJOIN(";";;MAP(A2:A9;B2:B9;C2:C9;LAMBDA(x;y;z;IF(AND(a=x;b>=y;b<=z);1;0)))))));;",";);1));1;0);LAMBDA(o;i;XLOOKUP(p;o;i)));0))));MAP(UNIQUE(E2:E14);LAMBDA(j;COUNTIF(E2:E14;j)))-MAP(UNIQUE(E2:E14);LAMBDA(p;SUM(IFERROR(MAP(E2:E14;IF(ISNUMBER(SEARCH(1;TEXTSPLIT(TEXTJOIN(",";;MAP(E2:E14;F2:F14;LAMBDA(a;b;TEXTJOIN(";";;MAP(A2:A9;B2:B9;C2:C9;LAMBDA(x;y;z;IF(AND(a=x;b>=y;b<=z);1;0)))))));;",";);1));1;0);LAMBDA(o;i;XLOOKUP(p;o;i)));0))))))Solving the challenge of Categorize Time Intervals Status with Python in Excel
Python in Excel solution 1 for Categorize Time Intervals Status, proposed by Bo Rydobon 🇹🇭:
from datetime import datetime, timedelta
T1= xl("A1:C9", headers=True)
T2=xl("E1:F14", headers=True)
early =lambda x: (datetime.combine(datetime.today(), x)+timedelta(minutes=30)).time()
T1['Early']=T1['Start Time'].apply(early)
T1['Late']=T1['End Time'].apply(early)
T2['c']=[2 if len((a:=T1[T1.Group==g])[T1['Start Time']<=t][t<=T1['End Time']]) else 3 if len(a[T1['End Time']Solving the challenge of Categorize Time Intervals Status with DAX
DAX solution 1 for Categorize Time Intervals Status, proposed by Szabolcs Phraner:
Calculate Column [Label] =
VAR gr = 'T_2'[Group]
VAR T2 = 'T_2'[Start Time]
VAR T1 = CALCULATETABLE(
'T_1';
FILTER('T_1'; gr = 'T_1'[Group])
)
VAR AddCols = ADDCOLUMNS(T1;
"Early"; HOUR([Start Time] -T2) * 60 + MINUTE([Start Time] - T2);
"Late"; HOUR(T2 - [End Time]) * 60 + MINUTE(T2 - [End Time])
)
VAR AddLabel = ADDCOLUMNS(AddCols ;"Label";
SWITCH(
TRUE();
T2 > [Start Time] && T2 < [End Time];"On Time";
[Late] > 0 && [Late] < 30;"Late";
[Early] > 0 && [Early] < 30;"Early";
"Out Of Limit"
)
)
RETURN MINX(AddLabel;[Label])
Add [Group] to rows, [Label] to Columns, and any field to Values
&&&