For different dates, document the min and max time and corresponding employees.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 582
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Time Extremes by Date with Power Query
Power Query solution 1 for Find Time Extremes by Date, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.TransformColumnTypes(
A,
{{"Date", type date}, {"Time", type time}, {"Emp ID", type text}}
),
C = Table.Sort(B, {{"Date", 0}, {"Emp ID", 0}}),
D = Table.Combine(
Table.Group(
C,
{"Date"},
{
"All",
each
let
z = (v) =>
[
a = Table.SelectRows(_, (y) => y[Time] = v([Time])),
b = Text.Combine(a[Emp ID], ", "),
c = Table.FromColumns({{a[Date]{0}}, {a[Time]{0}}, {b}}, {"Date", "Time", "Emp ID"})
][c]
in
Table.Combine({z(List.Min), z(List.Max)})
}
)[All]
)
in
DPower Query solution 2 for Find Time Extremes by Date, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Type = Table.TransformColumnTypes(
Source,
{{"Date", type date}, {"Emp ID", type text}, {"Time", type time}}
),
Group1 = Table.Group(
Type,
{"Date", "Time"},
{"Emp", each Text.Combine(List.Sort([Emp ID]), ", ")}
),
Group2 = Table.Group(
Group1,
"Date",
{"A", each Table.MinN(_, "Time", 1) & Table.MaxN(_, "Time", 1)}
),
Combine = Table.Combine(Group2[A]),
Return = Table.Sort(Combine, {"Date", "Time"})
in
ReturnPower Query solution 3 for Find Time Extremes by Date, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Time = Table.TransformColumns(
Source,
{{"Time", each Time.ToText(Time.From(_), [Format = "hh:mm"])}}
),
Group = Table.Group(
Time,
{"Date"},
{
{
"A",
each
let
a = _,
b = Table.Combine(
List.Transform(
{List.Min, List.Max},
(x) => Table.SelectRows(a, each [Time] = x(a[Time]))
)
),
c = Table.Group(
b,
"Time",
{"Emp ID", each Text.Combine(List.Transform(List.Sort([Emp ID]), Text.From), ", ")}
)
in
c
}
}
),
Sol = Table.Sort(
Table.ExpandTableColumn(Group, "A", Table.ColumnNames(Group[A]{0})),
{"Date", "Time"}
)
in
SolPower Query solution 4 for Find Time Extremes by Date, proposed by Brian Julius:
let
Source = Table.TransformColumnTypes(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{"Date", Date.Type}
),
Group = Table.Group(
Source,
{"Date"},
{
{"All", each _},
{"Min", each List.Min([Time]), type time},
{"Max", each List.Max([Time]), type time}
}
),
UnpivOth = Table.RemoveColumns(
Table.UnpivotOtherColumns(Group, {"Date", "All"}, "Att", "Time"),
"Att"
),
Exp = Table.ExpandTableColumn(UnpivOth, "All", {"Emp ID", "Time"}, {"All.Emp ID", "All.Time"}),
Filt = Table.RemoveColumns(Table.SelectRows(Exp, each [All.Time] = [Time]), "All.Time"),
Sort = Table.Buffer(
Table.Sort(
Filt,
{{"Date", Order.Ascending}, {"Time", Order.Ascending}, {"All.Emp ID", Order.Ascending}}
)
),
ReGp = Table.Group(Sort, {"Date", "Time"}, {{"EmpID", each [All.Emp ID]}}),
Ext = Table.TransformColumns(
ReGp,
{"EmpID", each Text.Combine(List.Transform(_, Text.From), ", ")}
)
in
ExtPower Query solution 5 for Find Time Extremes by Date, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform1 = Table.TransformColumnTypes(Source, {{"Date", type date}, {"Emp ID", type text}}),
Group = Table.Group(
Transform1,
"Date",
{
"Data",
each [
a = {List.Min([Time]), List.Max([Time])},
b = Text.Combine(List.Sort(Table.SelectRows(_, (x) => x[Time] = a{0})[Emp ID]), ", "),
c = Text.Combine(List.Sort(Table.SelectRows(_, (x) => x[Time] = a{1})[Emp ID]), ", "),
d = List.Zip({a, {b, c}})
][d]
},
GroupKind.Global,
(x, y) => Comparer.Ordinal(x, y)
),
Expand = Table.ExpandListColumn(Group, "Data"),
Transform2 = Table.TransformColumns(
Expand,
{"Data", each Text.Combine(List.Transform(_, Text.From), ":")}
),
Split = Table.SplitColumn(Transform2, "Data", each Text.Split(_, ":"), {"Time", "Emp ID"}),
Result = Table.TransformColumns(Split, {"Time", each Time.From(Number.From(_)), Time.Type})
in
ResultPower Query solution 6 for Find Time Extremes by Date, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.TransformColumnTypes(S, {{"Date", type date}, {"Time", type time}}),
b = Table.TransformColumnTypes(a, {{"Emp ID", type text}, {"Time", type text}}),
c = Table.Group(
Table.Sort(b, {"Date", 0}),
{"Date"},
{"G", each List.Zip({[Date], [Time], [Emp ID]})}
)[G],
d = List.Transform(c, each Table.FromRows(List.Sort(_, {each _{1}, 0}))),
e = List.Transform(
d,
each Table.Group(
_,
{"Column1", "Column2"},
{"H", each Text.Combine(List.Sort([Column3]), ", ")}
)
),
f = Table.Combine(List.Transform(e, each Table.FirstN(_, 1) & Table.LastN(_, 1))),
Sol = Table.RenameColumns(f, List.Zip({Table.ColumnNames(f), {"Date", "Time", "Emp ID"}}))
in
SolPower Query solution 7 for Find Time Extremes by Date, proposed by Francesco Bianchi 🇮🇹:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ChangedType = Table.TransformColumnTypes(
Source,
{{"Date", type date}, {"Time", type time}, {"Emp ID", type text}}
),
GroupedRows = Table.Sort(
Table.Group(
ChangedType,
{"Date"},
{
{
"All",
each
let
a = _,
Min = List.Min(a[Time]),
Max = List.Max(a[Time]),
SelRws = Table.SelectRows(a, each _[Time] = Min or [Time] = Max)[[Emp ID], [Time]],
Grp = Table.Sort(
Table.Group(
SelRws,
{"Time"},
{{"Emp ID", each Text.Combine(List.Sort(_[Emp ID]), ", ")}}
),
{{"Time", Order.Ascending}}
)
in
Grp
}
}
),
{{"Date", Order.Ascending}}
),
Expanded = Table.ExpandTableColumn(GroupedRows, "All", {"Time", "Emp ID"}, {"Time", "Emp ID"}),
ChangeType = Table.TransformColumnTypes(Expanded, {{"Time", type time}, {"Emp ID", type text}})
in
ChangeTypePower Query solution 8 for Find Time Extremes by Date, proposed by Jasper Malgo:
let
Source = Excel.CurrentWorkbook(){[Name = "datatable"]}[Content],
datatypes = Table.TransformColumnTypes(
Source,
{{"Date", type date}, {"Emp ID", Int64.Type}, {"Time", type time}}
),
grouped_by_date = Table.Group(
datatypes,
{"Date"},
{
{
"data_per_date",
each [
all = _,
max_time = List.Max(all[Time]),
min_time = List.Min(all[Time]),
rows = Table.SelectRows(all, (inner) => inner[Time] = max_time or inner[Time] = min_time),
final = Table.Group(
rows,
{"Time"},
{
{
"Emp ID",
(innergroup) =>
[
employee_list = innergroup[Emp ID],
numbers_to_text = List.Transform(employee_list, Text.From),
result = Text.Combine(numbers_to_text, ", ")
][result]
}
}
)
][final],
type table [Time = time, Emp ID = text]
}
}
),
expand_rows = Table.ExpandTableColumn(
grouped_by_date,
"data_per_date",
Table.ColumnNames(grouped_by_date[data_per_date]{0})
),
sort_by_date_time = Table.Sort(
expand_rows,
{{"Date", Order.Ascending}, {"Time", Order.Ascending}}
)
in
sort_by_date_timeSolving the challenge of Find Time Extremes by Date with Excel
Excel solution 1 for Find Time Extremes by Date, proposed by Bo Rydobon 🇹🇭:
=LET(d,A2:A26,REDUCE(HSTACK(A1,C1,B1),SORT(UNIQUE(d)),LAMBDA(a,v,
VSTACK(a,IFNA(HSTACK(v,CHOOSEROWS(GROUPBY(C2:C26,B2:B26,ARRAYTOTEXT,,0,,d=v),1,-1)),v)))))Excel solution 2 for Find Time Extremes by Date, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
SORT(
A2:C26,
2
),
g,
GROUPBY(
CHOOSECOLS(
z,
1,
3
),
INDEX(
z,
,
2
),
ARRAYTOTEXT,
,
0
),
d,
TAKE(
g,
,
1
),
CHOOSEROWS(
g,
TOROW(
XMATCH(
UNIQUE(
d
),
d,
,
{1,
-1}
)
)
)
)Excel solution 3 for Find Time Extremes by Date, proposed by John V.:
=LET(t,C2:C26,g,CHOOSECOLS(GROUPBY(A2:A26,t,VSTACK(MIN,MAX),,0),1,3),HSTACK(g,MAP(DROP(g,,1),LAMBDA(x,ARRAYTOTEXT(SORT(FILTER(B2:B26,x=t)))))))Excel solution 4 for Find Time Extremes by Date, proposed by 🇰🇷 Taeyong Shin:
=LET(
d,
A2:A26,
e,
B2:B26,
t,
C2:C26,
F,
LAMBDA(
fn,
LAMBDA(
x,
LET(
n,
ROUND(
MOD(
x,
1
),
6
),
ARRAYTOTEXT(
INT(
FILTER(
x,
fn(
n
)=n
)
)
)
)
)
),
CHOOSECOLS(
HSTACK(
GROUPBY(
d,
t,
VSTACK(
MIN,
MAX
),
,
0
),
GROUPBY(
d,
e+t,
VSTACK(
F(
MIN
),
F(
MAX
)
),
,
0
)
),
1,
3,
6
)
)Excel solution 5 for Find Time Extremes by Date, proposed by Kris Jaganah:
=LET(a,PIVOTBY(A2:A26,B2:B26,C2:C26,VSTACK(MIN,MAX),,0),b,TAKE(a,,-1),VSTACK({"Date","Emp ID","Time"},DROP(HSTACK(TAKE(a,,1),b,BYROW(DROP(IF(a=b,TAKE(a,1),""),,-1),LAMBDA(x,TEXTJOIN(", ",,x)))),1)))Excel solution 6 for Find Time Extremes by Date, proposed by Julian Poeltl:
=LET(D,
A2:A26,
I,
B2:B26,
T,
C2:C26,
REDUCE(HSTACK(
"Date",
"Item",
"Emp ID"
),
SORT(
UNIQUE(
D
)
),
LAMBDA(A,
B,
VSTACK(A,
LET(MA,
MAXIFS(
T,
D,
B
),
MI,
MINIFS(
T,
D,
B
),
VSTACK(HSTACK(B,
MI,
TEXTJOIN(", ",
,
FILTER(I,
(D=B)*(T=MI)))),
HSTACK(B,
MA,
TEXTJOIN(", ",
,
FILTER(I,
(D=B)*(T=MA))))))))))Excel solution 7 for Find Time Extremes by Date, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_date,
A2:A26,
_emp,
B2:B26,
_time,
C2:C26,
_group,
GROUPBY(
_date,
_time,
VSTACK(
MIN,
MAX
),
0,
0
),
_ndate,
CHOOSECOLS(
_group,
1
),
_ntime,
CHOOSECOLS(
_group,
3
),
_nemp,
MAP(
_ndate,
_ntime,
LAMBDA(a,
b,
ARRAYTOTEXT(SORT(FILTER(_emp,
(_date = a) * (_time = b)))))
),
_rtrn,
HSTACK(
_ndate,
_ntime,
_nemp
),
_rtrn
)Excel solution 8 for Find Time Extremes by Date, proposed by Timothée BLIOT:
=LET(A,A2:A26,B,B2:B26,C,C2:C26,D,GROUPBY(A,C,VSTACK(MIN,MAX),,0,,),E,INDEX(D,,1),F,INDEX(D,,3),HSTACK(E,F,MAP(E,F,LAMBDA(x,y,ARRAYTOTEXT(SORT(FILTER(B,(A=x)*(C=y))))))))Excel solution 9 for Find Time Extremes by Date, proposed by Duy Tùng:
=LET(a,
A2:A26,
b,
B2:B26,
c,
C2:C26,
GROUPBY(HSTACK(
a,
c
),
b,
LAMBDA(
x,
ARRAYTOTEXT(
SORT(
x
)
)
),
,
0,
,
(MINIFS(
c,
a,
a
)=c)+(MAXIFS(
c,
a,
a
)=c)))Excel solution 10 for Find Time Extremes by Date, proposed by Sunny Baggu:
=LET(
_u,
SORT(
TOROW(
UNIQUE(
A2:A26
)
),
,
,
1
),
_t,
IF(
A2:A26 = _u,
C2:C26,
x
),
_a,
BYCOL(
_t,
LAMBDA(
a,
MIN(
TOCOL(
a,
3
)
)
)
),
_b,
BYCOL(
_t,
LAMBDA(
a,
MAX(
TOCOL(
a,
3
)
)
)
),
_c,
BYCOL(
_u & _a = A2:A26 & C2:C26,
LAMBDA(
a,
ARRAYTOTEXT(
SORT(
FILTER(
B2:B26,
a
)
)
)
)
),
_d,
BYCOL(
_u & _b = A2:A26 & C2:C26,
LAMBDA(
a,
ARRAYTOTEXT(
SORT(
FILTER(
B2:B26,
a
)
)
)
)
),
HSTACK(
TOCOL(
IF(
{1; 2},
_u
),
,
1
),
TOCOL(
VSTACK(
_a,
_b
),
,
1
),
& TOCOL(
VSTACK(
_c,
_d
),
,
1
)
)
)Excel solution 11 for Find Time Extremes by Date, proposed by Md. Zohurul Islam:
=LET(
dt,A2:A26,
id,B2:B26,
tm,C2:C26,
unqdt,SORT(UNIQUE(dt)),
A,DROP(REDUCE("",unqdt,LAMBDA(x,y,LET(a,SORT(FILTER(tm,dt=y)),mn,MIN(a),mx,MAX(a),b,HSTACK(y,mn),c,HSTACK(y,mx),d,VSTACK(x,b,c),d))),1),
B,MAP(TAKE(A,,1),TAKE(A,,-1),LAMBDA(x,y,LET(s,SORT(FILTER(id,(dt=x)*(tm=y))),t,ARRAYTOTEXT(s),t))),
rng,HSTACK(A,B),
hdr,HSTACK("Date","Time","Emp ID"),
result,VSTACK(hdr,rng),
result)Excel solution 12 for Find Time Extremes by Date, proposed by Hamidi Hamid:
=LET(z,
A2:A26,
c,
C2:C26,
x,
GROUPER.PAR(
z,
c,
MAX,
,
0
),
y,
GROUPER.PAR(
z,
c,
MIN,
,
0
),
v,
TRIER(
ASSEMB.V(
x,
y
),
{1.2},
{1.1}
),
vu,
PRENDRE(
v,
,
1
),
vd,
PRENDRE(
v,
,
-1
),
w,
MAP(vu,
vd,
LAMBDA(a,
b,
TABLEAU.EN.TEXTE(TRIER(FILTRE(B2:B26,
(z=a)*(c=b)),
1,
1)))),
ASSEMB.V(
{"Date"."Time"."Emp ID"},
ASSEMB.H(
v,
w
)
))Excel solution 13 for Find Time Extremes by Date, proposed by Asheesh Pahwa:
=LET(
d,
A2:A26,
id,
B2:B26,
tim,
C2:C26,
ud,
SORT(
UNIQUE(
d
)
),
uid,
UNIQUE(
id
),
REDUCE(
E1:G1,
ud,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
HSTACK(
id,
tim
),
d=y
),
t,
TAKE(
f,
,
-1
),
mn,
MIN(
t
),
mx,
MAX(
t
),
h,
HSTACK(
y,
mx,
TEXTJOIN(
",",
1,
SORT(
FILTER(
TAKE(
f,
,
1
),
t=mx
)
)
)
),
hm,
HSTACK(
y,
mn,
TEXTJOIN(
",",
1,
SORT(
FILTER(
TAKE(
f,
,
1
),
t=mn
)
)
)
),
VSTACK(
hm,
h
)
)
)
)
)
)Excel solution 14 for Find Time Extremes by Date, proposed by ferhat CK:
=LET(a,CHOOSECOLS(GROUPBY(A2:A26,C2:C26,VSTACK(MIN,MAX),,0),1,3),HSTACK(a,MAP(TAKE(a,,-1),LAMBDA(x,ARRAYTOTEXT(FILTER(B2:B26,C2:C26=x))))))Excel solution 15 for Find Time Extremes by Date, proposed by Ankur Sharma:
=LET(dt, A2:A26, t, C2:C26,
a, GROUPBY(dt, t, MIN, , 0),
b, GROUPBY(dt, t, MAX, , 0),
c, SORT(VSTACK(a, b), 1),
d, BYROW(c, LAMBDA(r,
TEXTJOIN(", ", , SORT(FILTER(B2:B26, (dt = TAKE(r, , 1)) * (t = TAKE(r, , -1))))))),
HSTACK(c, d))Excel solution 16 for Find Time Extremes by Date, proposed by Imam Hambali:
=LET(
d,
A2:A26,
e,
B2:B26,
tm,
C2:C26,
l,
LAMBDA(
x,
GROUPBY(
d,
tm,
x,
0,
0
)
),
a,
SORT(
VSTACK(
l(
MIN
),
l(
MAX
)
),
1,
1
),
tr,
TOROW,
tk,
TAKE,
HSTACK(a,
MAP(tk(
a,
,
1
),
tk(
a,
,
-1
),
LAMBDA(x,
y,
LET(
xx,
(x=tr(
d
))*(y=tr(
tm
))*tr(
e
),
ARRAYTOTEXT(
tr(
IF(
xx=0,
NA(),
xx
),
3
)
)
))))
)Excel solution 17 for Find Time Extremes by Date, proposed by Jeremy Freelove:
=REDUCE({"Date",
"Time",
"Emp ID"},
UNIQUE(
SORT(
t[Date]
)
),
LAMBDA(a,
d,
LET(
ti,
LAMBDA(
v,
TAKE(
SORT(
FILTER(
t[Time],
t[Date]=d
)
),
v
)
),
emp,
LAMBDA(v,
TEXTJOIN(", ",
,
SORT(FILTER(t[Emp ID],
(t[Date]=d)*(t[Time]=v))))),
VSTACK(
a,
HSTACK(
d,
ti(
1
),
emp(
ti(
1
)
)
),
HSTACK(
d,
ti(
-1
),
emp(
ti(
-1
)
)
)
)
)))Solving the challenge of Find Time Extremes by Date with Python in Excel
Python in Excel solution 1 for Find Time Extremes by Date, proposed by Alejandro Campos:
df = xl("A1:C26", headers=True)
df['Date'] = pd.to_datetime(df['Date'], format="%d/%m/%Y")
time_summary = df.groupby('Date')['Time'].agg(['min', 'max']).reset_index().rename(columns={'min': 'Min_Time', 'max': 'Max_Time'})
get_employees = lambda t: df[df.set_index(['Date', 'Time']).index.isin(time_summary.set_index(['Date', t]).index)].groupby(['Date', 'Time'])['Emp ID'].apply(lambda x: ', '.join(map(str, x))).reset_index()
min_employees, max_employees = get_employees('Min_Time'), get_employees('Max_Time')
final_summary = pd.concat([
time_summary[['Date', 'Min_Time']].merge(min_employees, left_on=['Date', 'Min_Time'],
right_on=['Date', 'Time']).drop(columns='Time').rename(columns={'Min_Time': 'Time'}),
time_summary[['Date', 'Max_Time']].merge(max_employees, left_on=['Date', 'Max_Time'],
right_on=['Date', 'Time']).drop(columns='Time').rename(columns={'Max_Time': 'Time'})
]).sort_values(by=['Date', 'Time']).reset_index(drop=True)
final_summary
&&