Pivot the given table for Date / Emp ID combinations with Min and Max Time. Min Time and Max Time will appear in alternate rows. First Min time will appear and then Max time in other row will appear.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 418
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Pivot for Time Entries by Date with Power Query
Power Query solution 1 for Pivot for Time Entries by Date, proposed by John V.:
let
S = Excel.CurrentWorkbook(){0}[Content],
d = "Date", i = "Emp ID", t = "Min & Max Time",
G = Table.Group(S, {d, i}, {t, each {List.Min([Time]), List.Max([Time])}})
in
Table.ExpandListColumn(Table.Sort(G, {{d, 0}, {i, 0}}), t)
Blessings!
Power Query solution 2 for Pivot for Time Entries by Date, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Type = Table.TransformColumnTypes(
Source,
{{"Emp ID", type text}, {"Time", type time}, {"Date", type date}}
),
Group = Table.Group(
Type,
{"Date", "Emp ID"},
{"Min Max", each {List.Min([Time]), List.Max([Time])}}
),
Sort = Table.Sort(Group, {"Date", "Emp ID"}),
Return = Table.ExpandListColumn(Sort, "Min Max")
in
ReturnPower Query solution 3 for Pivot for Time Entries by Date, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Date", "Emp ID"},
{
{
"Min & Max Time",
each
let
a = [Time],
b = {List.Min(a), List.Max(a)}
in
b
}
}
),
Expand = Table.ExpandListColumn(Group, "Min & Max Time"),
Sol = Table.Sort(Expand, {{"Date", 0}, {"Emp ID", 0}})
in
SolPower Query solution 4 for Pivot for Time Entries by Date, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
gp = Table.Group(
Fonte,
{"Emp ID", "Date"},
{
"Min_Max",
each Table.Transpose(
Table.FromRecords(
{[Min = Time.From(List.Min(_[Time])), Max = Time.From(List.Max(_[Time]))]}
)
)
}
),
mm = Table.ExpandTableColumn(gp, "Min_Max", {"Column1"}, {"Min & Max Time"}),
res = Table.Sort(mm, {{"Date", 0}, {"Emp ID", 0}})
in
resPower Query solution 5 for Pivot for Time Entries by Date, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ReType = Table.TransformColumnTypes(
Source,
{{"Emp ID", Int64.Type}, {"Time", type time}, {"Date", type date}}
),
Group = Table.Group(
ReType,
{"Date", "Emp ID"},
{
{"Min", each List.Min([Time]), type nullable time},
{"Max", each List.Max([Time]), type nullable time}
}
),
Sort = Table.Sort(Group, {{"Date", Order.Ascending}, {"Emp ID", Order.Ascending}}),
UnpivotOther = Table.UnpivotOtherColumns(Sort, {"Date", "Emp ID"}, "Attribute", "Value"),
Clean = Table.RenameColumns(
Table.RemoveColumns(UnpivotOther, {"Attribute"}),
{"Value", "Min & Max Time"}
)
in
CleanPower Query solution 6 for Pivot for Time Entries by Date, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T = Table.TransformColumns,
a = Table.Group(S, {"Emp ID", "Date"}, {{"G", each _}}),
b = T(a, {"G", each Table.SelectColumns(_, {"Date", "Emp ID", "Time"})}),
c = T(b, {"G", each T(_, {"Time", (x) => {List.Min([Time])} & {List.Max([Time])}})}),
d = T(c, {"G", each Table.Distinct(_)}),
e = Table.Sort(Table.Combine(d[G]), {{"Date", 0}, {"Emp ID", 0}}),
Sol = Table.RenameColumns(Table.ExpandListColumn(e, "Time"), {"Time", "Min & Max Time"})
in
SolPower Query solution 7 for Pivot for Time Entries by Date, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
A = Table.TransformColumnTypes(
S,
{{"Emp ID", Int64.Type}, {"Time", type time}, {"Date", type date}}
),
B = Table.Sort(
Table.Group(
A,
{"Emp ID", "Date"},
{{"Min & Max Time", each List.Min([Time]), type nullable time}}
),
{{"Date", Order.Ascending}, {"Emp ID", Order.Ascending}}
),
C = Table.Sort(
Table.Group(
A,
{"Emp ID", "Date"},
{{"Min & Max Time", each List.Max([Time]), type nullable time}}
),
{{"Date", Order.Ascending}, {"Emp ID", Order.Ascending}}
),
D = Table.Combine({B, C}),
R = Table.SelectColumns(D, {"Date", "Emp ID", "Min & Max Time"}),
Sol = Table.Sort(R, {{"Date", Order.Ascending}, {"Emp ID", Order.Ascending}})
in
SolPower Query solution 8 for Pivot for Time Entries by Date, proposed by Udit Chatterjee:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"dZJRDoQgDETvwreJ7bR1sVcx3v8a4hZ1F/CLhJdpHxO2LQFIU+LFScsps84gaNqnG2VXGSPANbeImQNJpPAg+V6RUxB7iOq5fXVwmwlSxlU/tBLLQO92qHo/ITM7ETt9WomKnNaW1CacMd50O3SLWF3y+LX62hDbVUTnUMqj1iFC5oQ2cxWOTiG8eVRDReaiY5QdXUO1cXHuxAMVcXvZVOb9f4j9AA==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [#"Emp ID" = _t, Time = _t, Date = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Emp ID", Int64.Type}, {"Time", type time}, {"Date", type date}}
),
#"Grouped Rows" = Table.Group(
#"Changed Type",
{"Date", "Emp ID"},
{
{"Min", each List.Min([Time]), type nullable time},
{"Max", each List.Max([Time]), type nullable time}
}
),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
#"Grouped Rows",
{"Date", "Emp ID"},
"Attribute",
"Min & Max Time"
),
#"Sorted Rows" = Table.Sort(
#"Unpivoted Other Columns",
{{"Date", Order.Ascending}, {"Emp ID", Order.Ascending}, {"Attribute", Order.Descending}}
),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows", {"Attribute"})
in
#"Removed Columns"Solving the challenge of Pivot for Time Entries by Date with Excel
Excel solution 1 for Pivot for Time Entries by Date, proposed by Bo Rydobon 🇹🇭:
=CHOOSECOLS(GROUPBY(HSTACK(C2:C26,A2:A26),B2:B26,VSTACK(MIN,MAX),0,0),{1,2,4})Excel solution 2 for Pivot for Time Entries by Date, proposed by John V.:
=CHOOSECOLS(GROUPBY(HSTACK(C2:C26,A2:A26),B2:B26,VSTACK(MIN,MAX),,0),1,2,4)Excel solution 3 for Pivot for Time Entries by Date, proposed by محمد حلمي:
=LET(
k,
A2:A26,
b,
B2:B26,
c,
C2:C26,
i,
UNIQUE(
HSTACK(
c,
A2:A26
)
),
x,
SORT(
VSTACK(
i,
i
),
{1,
2}
),
v,
TAKE(
x,
,
1
),
j,
DROP(
x,
,
1
),
HSTACK(
x,
IF(
ISODD(
SEQUENCE(
ROWS(
x
)
)
),
MINIFS(
b,
c,
v,
k,
j
),
MAXIFS(
b,
c,
v,
k,
j
)
)
)
)Excel solution 4 for Pivot for Time Entries by Date, proposed by 🇰🇷 Taeyong Shin:
=CHOOSECOLS(GROUPBY(HSTACK(C1:C26,A1:A26),B1:B26,VSTACK(MIN,MAX),3,0),{1,2,4})Excel solution 5 for Pivot for Time Entries by Date, proposed by Kris Jaganah:
=CHOOSECOLS(GROUPBY(HSTACK(C1:C26,A1:A26),B1:B26,VSTACK(MIN,MAX),3,0),1,2,4)Excel solution 6 for Pivot for Time Entries by Date, proposed by Julian Poeltl:
=LET(
T,
A2:C26,
ID,
TAKE(
T,
,
1
),
Ti,
DROP(
TAKE(
T,
,
2
),
,
1
),
D,
TAKE(
T,
,
-1
),
UNS,
SORT(
UNIQUE(
D&ID
)
),
DAT,
LEFT(
UNS,
5
),
IDS,
RIGHT(
UNS,
3
),
MI,
MINIFS(
Ti,
D,
DAT,
ID,
IDS
),
MA,
MAXIFS(
Ti,
D,
DAT,
ID,
IDS
),
VSTACK(
HSTACK(
"Date",
"Emp ID",
"Min & Max Time"
),
WRAPROWS(
TOROW(
HSTACK(
DAT*1,
IDS*1,
MI,
DAT*1,
IDS*1,
MA
)
),
3
)
)
)Excel solution 7 for Pivot for Time Entries by Date, proposed by Timothée BLIOT:
=CHOOSECOLS(GROUPBY(HSTACK(C2:C26,A2:A26),B2:B26,VSTACK(MIN,MAX),,0,,),{1,2,4})Excel solution 8 for Pivot for Time Entries by Date, proposed by Hussein SATOUR:
=CHOOSECOLS(GROUPBY(HSTACK(C2:C26,A2:A26),B2:B26,VSTACK(MIN,MAX),,0),1,2,4)Excel solution 9 for Pivot for Time Entries by Date, proposed by Sunny Baggu:
=LET(
_ud,
UNIQUE(
C2:C26
),
_eid,
UNIQUE(
A2:A26
),
_c,
SORT(
DROP(
REDUCE(
"",
_eid,
LAMBDA(
a,
v,
VSTACK(
a,
IFNA(
HSTACK(
_ud,
v
),
v
)
)
)
),
1
),
{1,
2}
),
_d,
MAKEARRAY(
ROWS(
_c
),
2,
LAMBDA(
r,
c,
INDEX(
TAKE(
SORT(
FILTER(
B2:B26,
C2:C26 & A2:A26 = INDEX(
_c,
r,
1
) & INDEX(
TAKE(
_c,
,
-1
),
r,
1
)
)
),
{1,
-1}
),
c
)
)
),
_e,
FILTER(
_c,
BYROW(
_d,
LAMBDA(
a,
NOT(
OR(
ISERR(
a
)
)
)
)
)
),
_f,
TOCOL(
IF(
{1,
2},
SEQUENCE(
ROWS(
_e
)
)
)
),
HSTACK(
INDEX(
_e,
_f,
{1,
2}
),
TOCOL(
_d,
3
)
)
)Excel solution 10 for Pivot for Time Entries by Date, proposed by Asheesh Pahwa:
=LET(
emt,
A2:B26,
dt,
C2:C26,
DROP(
REDUCE(
"",
SORT(
UNIQUE(
dt
)
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
emt,
dt=y
),
t,
TAKE(
f,
,
1
),
DROP(
REDUCE(
"",
SORT(
UNIQUE(
t
)
),
LAMBDA(
a,
v,
VSTACK(
a,
LET(
m,
FILTER(
f,
t=v,
""
),
b,
TAKE(
m,
,
-1
),
na,
IFNA(
HSTACK(
y,
HSTACK(
v,
VSTACK(
MIN(
b
),
MAX(
b
)
)
)
),
v
),
&
tk,
HSTACK(
IF(
TAKE(
na,
,
1
)=v,
y,
y
),
TAKE(
na,
,
-2
)
),
tk
)
)
)
),
1
)
)
)
)
),
1
)
)Excel solution 11 for Pivot for Time Entries by Date, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(
a;
C2:C26&"."&A2:A26;
b;
B2:B26;
c;
UNICOS(
ORDENAR(
a
)
);
d;
REDUCE(
"";
c;
LAMBDA(
z;
x;
UNIRCADENAS(
"-";
;
z;
x&"."&LET(
f;
FILTRAR(
b;
a=x;
""
);
SI(
{1;
0};
MIN(
f
);
MAX(
f
)
)
)
)
)
);
--DIVIDIRTEXTO(
d;
".";
"-"
)
)
Maybe it's like that
=LET(
a,C2:C26&"."&A2:A26,b,B2:B26,c,UNIQUE(
SORT(
a
)
),d,REDUCE(
"",c,LAMBDA(
z,x,TEXTJOIN(
"-",,z,x&"."&LET(
f,FILTER(
b,a=x,""
),IF(
{1,0},MIN(
f
),MAX(
f
)
)
)
)
)
),--TEXTSPLIT(
d,".","-"
)
)Excel solution 12 for Pivot for Time Entries by Date, proposed by Crispo Mwangi:
=IF(COUNTIFS($A$1:A2,A2,$C$1:C2,C2)=1,
MAXIFS(B:B,A:A,A2,C:C,C2),
MINIFS(B:B,A:A,A2,C:C,C2))Excel solution 13 for Pivot for Time Entries by Date, proposed by Alexandra Popoff:
= LAMBDA(
in_emp,
in_date,
in_time,
LET(
z_arr_p,
SORT(
UNIQUE(
HSTACK(
in_date,
in_emp
)
),
2
),
z_min,
MAP(
INDEX(
z_arr_p,
,
1
),
INDEX(
z_arr_p,
,
2
),
LAMBDA(
z_date,
z_emp,
MINIFS(
in_time,
in_emp,
z_emp,
in_date,
z_date
)
)
),
z_max,
MAP(
INDEX(
z_arr_p,
,
1
),
INDEX(
z_arr_p,
,
2
),
LAMBDA(
z_date,
z_emp,
MAXIFS(
in_time,
in_emp,
z_emp,
in_date,
z_date
)
)
),
z_out,
VSTACK(
HSTACK(
z_arr_p,
z_min,
SEQUENCE(
ROWS(
z_arr_p
),
1,
1,
0
)
),
HSTACK(
z_arr_p,
z_max,
SEQUENCE(
ROWS(
z_arr_p
),
1,
2,
0
)
)
),
DROP(
SORTBY(
z_out,
INDEX(
z_out,
,
1
),
1,
INDEX(
z_out,
,
2
),
1,
INDEX(
z_out,
,
4
),
1
),
,
-1
)
)
)Excel solution 14 for Pivot for Time Entries by Date, proposed by Alexandra Popoff:
=LET(
in_emp;
$A$2:$A$26;
in_date;
$C$2:$C$26;
in_time;
$B$2:$B$26;
z_arr_p;
SORT(
UNIQUE(
HSTACK(
in_date;
in_emp
)
);
2
);
z_min;
MAP(
INDEX(
z_arr_p;
;
1
);
INDEX(
z_arr_p;
;
2
);
LAMBDA(
z_date;
z_emp;
MINIFS(
in_time;
in_emp;
z_emp;
in_date;
z_date
)
)
);
z_max;
MAP(
INDEX(
z_arr_p;
;
1
);
INDEX(
z_arr_p;
;
2
);
LAMBDA(
z_date;
z_emp;
MAXIFS(
in_time;
in_emp;
z_emp;
in_date;
z_date
)
)
);
z_out;
HSTACK(
z_arr_p;
z_min;
z_max
);
z_Out_sort;
SORTBY(
z_out;
INDEX(
z_out;
;
1
);
1;
INDEX(
z_out;
;
2
);
1
);
Fx_UnPivot(
2;
HSTACK(
"Date";
"Emp";
"Min";
"Max"
);
z_Out_sort;
"Min & Max Time";
"A"
)
)Excel solution 15 for Pivot for Time Entries by Date, proposed by Richard Daniels:
=LET(
empl,
A2:A26,
times,
B2:B26,
dates,
C2:C26,
empl_date,
UNIQUE(
HSTACK(
empl,
dates
)
),
max_times,
MAXIFS(
times,
empl,
CHOOSECOLS(
empl_date,
1
),
dates,
CHOOSECOLS(
empl_date,
2
)
),
min_times,
MINIFS(
times,
empl,
CHOOSECOLS(
empl_date,
1
),
dates,
CHOOSECOLS(
empl_date,
2
)
),
SORT(
VSTACK(
HSTACK(
CHOOSECOLS(
empl_date,
2
),
CHOOSECOLS(
empl_date,
1
),
min_times
),
HSTACK(
CHOOSECOLS(
empl_date,
2
),
CHOOSECOLS(
empl_date,
1
),
max_times
)
),
{1,
2,
3},
1
)
)Solving the challenge of Pivot for Time Entries by Date with Python
Python solution 1 for Pivot for Time Entries by Date, proposed by Konrad Gryczan, PhD:
import pandas as pd
xlsx_file = '418 Pivot on Min and Max .xlsx'
input = pd.read_excel(xlsx_file, sheet_name='Sheet1', usecols='A:C', nrows=26)
test = pd.read_excel(xlsx_file, sheet_name='Sheet1', usecols='E:G', nrows=12)
test.rename(columns={'Date.1': 'Date', 'Emp ID.1': 'Emp ID'}, inplace=True)
grouped_data = input.groupby(['Date', 'Emp ID']).agg({'Time': ['min', 'max']})
pivot_data = grouped_data.stack().reset_index().drop(columns=['level_2'])
pivot_data.rename(columns={'Time': 'Min & Max Time'}, inplace=True)
print(test.equals(pivot_data))
Python solution 2 for Pivot for Time Entries by Date, proposed by Cristobal Salcedo Beltran:
code:_________________
from pyspark.sql import SparkSession
from pyspark.sql.functions import min, max, col, struct, explode, array
import pandas as pd
spark = SparkSession.builder.appName("CHALLENGE408").getOrCreate()
file_path = "/lakehouse/default/Files/Challenge/Excel_Challenge_418 - Pivot on Min and Max .xlsx"
df_pandas = pd.read_excel(file_path, usecols=[0, 1, 2])
df_pandas['Time'] = df_pandas['Time'].astype(str)
df_spark = spark.createDataFrame(df_pandas)
grouped_df = df_spark.groupBy("Date","Emp ID")
.agg(min("Time").alias("Min Time"),max("Time").alias("Max Time"))
expanded_df = grouped_df.withColumn("Min Max", explode(array(col("Min Time"), col("Max Time")))).drop("Min Time","Max Time")
sorted_df = expanded_df.orderBy("Date", "Emp ID")
sorted_df.show()
Python solution 3 for Pivot for Time Entries by Date, proposed by Cristobal Salcedo Beltran:
Code:_____________________________________
import pandas as pd
file_path = "/lakehouse/default/Files/Challenge/Excel_Challenge_418 - Pivot on Min and Max .xlsx"
df = pd.read_excel(file_path, usecols=[0, 1, 2])
df['Time'] = pd.to_datetime(df['Time'], format='%H:%M:%S').dt.time
grouped = df.groupby(['Date', 'Emp ID']).agg(Min_Time=('Time', 'min'), Max_Time=('Time', 'max')).reset_index()
min_times = grouped[['Date', 'Emp ID', 'Min_Time']].rename(columns={'Min_Time': 'Time'})
max_times = grouped[['Date', 'Emp ID', 'Max_Time']].rename(columns={'Max_Time': 'Time'})
expanded_df = pd.concat([min_times, max_times]).sort_values(by=['Date', 'Emp ID', 'Time'])
expanded_df.reset_index(drop=True, inplace=True)
print(expanded_df)
Solving the challenge of Pivot for Time Entries by Date with Python in Excel
Python in Excel solution 1 for Pivot for Time Entries by Date, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_418 - Pivot on Min and Max .xlsx'
df1 = pd.read_excel(file_path, usecols='E:G', nrows=12)
df1.rename(columns={'Date.1': 'Date', 'Emp ID.1': 'Emp ID'}, inplace=True) # required results
df2 = pd.read_excel(file_path, usecols='A:C') # df for computation
# Groupby to calculate minimum and maximum time per Emp ID and Date
df2 = df2.groupby(['Date', 'Emp ID'])['Time'].agg(['min', 'max']).reset_index()
df2 = pd.melt(df2, id_vars=['Date', 'Emp ID'], value_vars=['min', 'max'], value_name='Min & Max Time')
df2.sort_values(by=['Date', 'Emp ID', 'variable'],
ascending=[True, True, False], ignore_index=True, inplace=True)
df2 = df2[['Date', 'Emp ID', 'Min & Max Time']]
print(f'nExpected Results:n{df1}nnMy Results:n{df2}')
Python in Excel solution 2 for Pivot for Time Entries by Date, proposed by ferhat CK:
My Phyton Code
alan=xl("A1:C26", headers=True)
a2=alan[['Date','Emp ID', 'Time']]
a=a2.groupby(["Date","Emp ID"]).max()
b=a2.groupby(["Date","Emp ID"]).min()
df=pd.concat([b,a],axis=0)
df.sort_values("Date")
df2 = (df.sort_values('Emp ID', ascending=True).sort_values('Date', ascending=True, kind='stable'))
df2.rename(columns = {'Time':'Min & Max Time'}, inplace = True)
df2['Min & Max Time']
Python in Excel solution 3 for Pivot for Time Entries by Date, proposed by Giorgi Goderdzishvili:
df = xl("A1:C26", headers=True)
mx = df.groupby(["Date","Emp ID"])["Time"].max()
mn = df.groupby(["Date","Emp ID"])["Time"].min()
fn = pd.concat([mx,mn]).to_frame()
fn.sort_values(by=["Date","Emp ID","Time"]).reset_index().rename(columns={"Time":"Min & Max Time"})
Solving the challenge of Pivot for Time Entries by Date with R
R solution 1 for Pivot for Time Entries by Date, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(hms)
input = read_excel("Excel/418 Pivot on Min and Max .xlsx", range = "A1:C26")
test = read_excel("Excel/418 Pivot on Min and Max .xlsx", range = "E1:G13") %>%
mutate(`Min & Max Time` = as_hms(`Min & Max Time`))
result = input %>%
summarise(Min = min(Time), Max = max(Time), .by = c(Date, `Emp ID`)) %>%
pivot_longer(cols = c(Min, Max), names_to = "Type", values_to = "Time") %>%
mutate(`Min & Max Time` = as_hms(Time)) %>%
select(-c(Type, Time)) %>%
arrange(Date, `Emp ID`)
Solving the challenge of Pivot for Time Entries by Date with DAX
DAX solution 1 for Pivot for Time Entries by Date, proposed by Zoran Milokanović:
EVALUATE
SELECTCOLUMNS(
UNION(
SUMMARIZE(
ADDCOLUMNS(Input, "D", DATE(YEAR(Input[Date]), MONTH(Input[Date]), DAY(Input[Date]))),
[D], Input[Emp ID],
"Min & Max Time", MIN(Input[Time]), "Seq", 1
),
SUMMARIZE(
ADDCOLUMNS(Input, "D", DATE(YEAR(Input[Date]), MONTH(Input[Date]), DAY(Input[Date]))),
[D], Input[Emp ID],
"Min & Max Time", MAX(Input[Time]), "Seq", 2
)
),
"Date", [D], [Emp ID], [Min & Max Time], [Seq]
)
ORDER BY
[Date], Input[Emp ID], [Seq]
&&