Find the total leaves taken by the employees. Total leaves will be counted only for working days i.e. Mon to Fri. Weekends (Sat & Sun) will be excluded. Dates may overlap. So, double counting should not be done. Note – Value for Laura is 11. Updated in Excel but picture can’t be updated.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 234
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Exclude Weekend Leave Days with Power Query
Power Query solution 2 for Exclude Weekend Leave Days, proposed by Zoran Milokanović:
let
Source = Table.ToRows(Excel.CurrentWorkbook(){[Name = "Input"]}[Content]),
S = Table.Sort(
Table.FromList(
Source,
each {
_{0},
List.Count(
List.Distinct(
List.TransformMany(
List.Split(List.Select(_, each _ is datetime), 2),
each List.Select(
List.DateTimes(_{0}, Duration.Days(_{1} - _{0}) + 1, Duration.From(1)),
each Date.DayOfWeek(_, 6) > 1
),
(i, _) => _
)
)
)
},
{"Employee", "Total Leaves"}
),
"Employee"
)
in
SPower Query solution 3 for Exclude Weekend Leave Days, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = Table.AddColumn(A, "Total Leaves", each let
a = List.RemoveNulls( Record.ToList(_)),
b = List.Transform( List.Skip(a), Date.From) ,
c = List.TransformMany (List.Split(b,2), each List.Dates( _{0} , Number.From( _{1} - _{0})+1 ,hashtag#duration(1,0,0,0) ) ,
(x,y)=> y) ,
d = List.Count( List.Distinct( List.Select(c , each Date.DayOfWeek( _ ) < 5 )) ) in d) [[Employee] ,[Total Leaves]],
C = Table.Sort(B,{"Employee"})
in C
Power Query solution 5 for Exclude Weekend Leave Days, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
Unpivot = Table.UnpivotOtherColumns ( Source, { "Employee" }, "H", "D" ),
Split = Table.Split ( Unpivot, 2 ),
Trasnform = List.TransformMany (
Split,
each [
S = [D]{0},
E = [D]{1},
Df = Duration.Days ( E - S ) + 1,
R = List.Dates ( Date.From ( S ), Df, hashtag#duration ( 1, 0, 0, 0 ) )
][R], (x, y) => {Table.FirstValue(x), y }
),
Table = Table.FromRows ( Trasnform, { "Employee", "Dates" } ),
Unique = Table.Distinct ( Table ),
Filter = Table.SelectRows ( Unique, each Date.DayOfWeek ( [Dates], 1 ) < 5 ),
Group = Table.Group ( Filter, "Employee", { "Count", Table.RowCount } ),
Return = Table.Sort ( Group, "Employee" )
in
Return
Power Query solution 6 for Exclude Weekend Leave Days, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
Unpivot = Table.UnpivotOtherColumns ( Source, { "Employee" }, "H", "D" ),
Split = Table.Split ( Unpivot, 2 ),
Trasnform = List.Transform (
Split,
each [
Employee = Table.FirstValue ( _ ),
S = [D]{0},
E = [D]{1},
Df = Duration.Days ( E - S ) + 1,
Dates = List.Dates ( Date.From ( S ), Df, hashtag#duration ( 1, 0, 0, 0 ) )
]
),
Table = Table.FromRecords ( Trasnform, { "Employee", "Dates" } ),
Expand = Table.ExpandListColumn ( Table, "Dates" ),
Unique = Table.Distinct ( Expand ),
Filter = Table.SelectRows ( Unique, each Date.DayOfWeek ( [Dates], 1 ) < 5 ),
Group = Table.Group ( Filter, "Employee", { "Count", Table.RowCount } ),
Return = Table.Sort ( Group, "Employee" )
in
Return
Power Query solution 7 for Exclude Weekend Leave Days, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Sort(
Table.AddColumn(
Source,
"TotalLeaves",
each
let
a = List.Skip(Record.ToList(_)),
b = List.Split(List.RemoveNulls(List.Transform(a, Date.From)), 2),
c = List.Transform(
{0 .. List.Count(b) - 1},
each List.Dates(b{_}{0}, Number.From(b{_}{1} - b{_}{0}) + 1, Duration.From(1))
),
d = List.Union(c),
e = List.Select(d, each Date.DayOfWeek(_, 1) < 5),
f = List.Count(e)
in
f
)[[Employee], [TotalLeaves]],
"Employee"
)
in
SolPower Query solution 8 for Exclude Weekend Leave Days, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"TotalLeaves",
each
let
a = List.Skip(Record.FieldValues(_), 1),
b = List.Split(a, 2),
c = List.Transform(
b,
(x) =>
try
List.Select(
{Number.From(x{0}) .. Number.From(x{1})},
(y) => Date.DayOfWeek(Date.From(y)) <> 0 and Date.DayOfWeek(Date.From(y)) <> 6
)
otherwise
null
),
d = List.Count(List.Union(List.RemoveNulls(c)))
in
d
)[[Employee], [TotalLeaves]]
in
resPower Query solution 9 for Exclude Weekend Leave Days, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(
Source,
"TotalLeaves",
each [
a = List.Transform(
List.Split(List.Skip(List.RemoveNulls(Record.ToList(_))), 2),
each List.DateTimes(_{0}, Duration.Days(_{1} - _{0}) + 1, Duration.From(1))
),
b = List.Select(List.Combine(a), each Date.DayOfWeek(_, Day.Saturday) > 1),
c = List.Count(List.Distinct(b))
][c]
),
Result = Table.Sort(AddCol[[Employee], [TotalLeaves]], "Employee")
in
ResultPower Query solution 10 for Exclude Weekend Leave Days, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Employee"}, "Data", "Date"),
Group = Table.Group(
Unpivot,
"Employee",
{
"TotalLeaves",
each [
a = List.Transform(
List.Split([Date], 2),
each List.DateTimes(_{0}, Duration.Days(_{1} - _{0}) + 1, Duration.From(1))
),
b = List.Select(List.Combine(a), each Date.DayOfWeek(_, Day.Saturday) > 1),
c = List.Count(List.Distinct(b))
][c]
}
),
Result = Table.Sort(Group, "Employee")
in
ResultPower Query solution 11 for Exclude Weekend Leave Days, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name="tData234"]}[Content],
Transform = Table.FromRecords(Table.TransformRows(Source, each let
_Leaves = List.Split(List.Skip(List.RemoveNulls(Record.ToList(_))),2),
_LeaveDays = List.Transform(_Leaves, each let
_StartD = Date.From(_{0}),
_dates = List.Dates(_StartD, Duration.Days(Date.From(_{1}) - _StartD) + 1, hashtag#duration(1,0,0,0))
in List.Select(_dates, each Date.DayOfWeek(_, Day.Monday) < 5) )
in _[[Employee]] & [TotalLeaves=List.Count(List.Distinct(List.Combine(_LeaveDays)))] )
),
Sort = Table.Sort(Transform, "Employee")
in
Sort
Power Query solution 12 for Exclude Weekend Leave Days, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(
S,
"T",
each
let
A = List.Alternate(List.Skip(Record.ToList(_), 1), 1, 1, 1),
B = List.Skip(List.Alternate(List.Skip(Record.ToList(_), 1), 1, 1, 2), 1),
C = Table.SelectRows(Table.FromColumns({A, B}, {"Start", "End"}), each _[Start] <> null)
in
C
),
B = Table.ExpandTableColumn(A, "T", {"Start", "End"}, {"Start", "End"}),
C = Table.AddColumn(
B,
"D",
each {Number.From(Date.From([Start])) .. Number.From(Date.From([End]))}
),
D = Table.ExpandListColumn(C, "D"),
E = Table.Group(D, {"Employee"}, {{"T", each _}}),
F = (T) =>
let
A = Table.TransformColumnTypes(T, {{"D", type date}}),
B = Table.Distinct(A, {"D"}),
C = Table.TransformColumns(B, {{"D", each Date.DayOfWeekName(_), type text}}),
D = Table.SelectRows(C, each ([D] <> "Saturday" and [D] <> "Sunday")),
E = List.NonNullCount(D[D])
in
E,
I = Table.AddColumn(E, "F", each F([T])),
G = Table.Sort(Table.SelectColumns(I, {"Employee", "F"}), {{"Employee", Order.Ascending}})
in
GPower Query solution 13 for Exclude Weekend Leave Days, proposed by Antriksh Sharma:
let
Source = Raw,
Transform = List.Transform(
Table.ToRows(Source),
(x) =>
let
EmployeeName = x{0},
RemoveNulls = List.Skip(List.RemoveNulls(x)),
Split = List.Split(RemoveNulls, 2),
GenerateDates = List.Transform(
Split,
(d) =>
let
StartDate = d{0},
EndDate = d{1},
DateList = List.Dates(
StartDate,
1 + Duration.Days(EndDate - StartDate),
Duration.From(1)
),
RemoveWeekends = List.Select(DateList, each 1 + Date.DayOfWeek(_, Day.Monday) <= 5)
in
RemoveWeekends
),
LeaveCount = List.Count(List.Distinct(List.Combine(GenerateDates))),
ToTable = Table.FromColumns(
{{EmployeeName}, {LeaveCount}},
type table [Employee Name = text, Total Leaves = Int64.Type]
)
in
ToTable
),
TablesCombined = Table.Sort(Table.Combine(Transform), {"Employee Name", Order.Ascending})
in
TablesCombinedPower Query solution 14 for Exclude Weekend Leave Days, proposed by Peter Krkos:
PowerQuery solution:
= Table.AddColumn(Source, "Total Leaves", each
[ a = List.Transform(List.Split(List.RemoveNulls(List.Skip(Record.ToList(_))), 2), (x)=>
b = List.Count(List.Select(List.Distinct(List.Combine(a)), (x)=> Date.DayOfWeek(x, Day.Monday) < 5))
][b], Int64.Type)[[Employee], [Total Leaves]]
Power Query solution 15 for Exclude Weekend Leave Days, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = (a, b) =>
List.Select(
List.Generate(() => [x = a], each [x] <= b, each [x = Date.AddDays([x], 1)], each [x]),
each Date.DayOfWeek(_, Day.Monday) + 1 <= 5
),
C = List.TransformMany(
Table.ToRows(A),
each {List.Transform(List.Split(List.RemoveNulls(List.Skip(_)), 2), each B(_{0}, _{1}))},
(x, y) => {x{0}, List.Count(List.Distinct(List.Combine(y)))}
),
D = Table.FromList(List.Sort(C, {each _{0}}), each ({_{0}, _{1}}), {"Employee", "TotalLeaves"})
in
DPower Query solution 16 for Exclude Weekend Leave Days, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
GetTable = Table.SelectRows(
Table.Combine(
List.Transform(
List.Transform(
List.Split(List.Skip(Table.ToColumns(Source)), 2),
each {Table.Column(Source, Table.ColumnNames(Source){0})} & _
),
each Table.FromColumns(_, {"Employee", "DStart", "DEnd"})
)
),
each ([DStart] <> null)
),
AddDates = Table.AddColumn(
GetTable,
"Lists",
each List.Transform({Number.From([DStart]) .. Number.From([DEnd])}, Date.From)
)[[Employee], [Lists]],
Result = Table.Sort(
Table.Group(
AddDates,
{"Employee"},
{
{
"TotalLeaves",
each List.Count(
List.Select(
List.Transform(
List.Distinct(List.Combine([Lists])),
each Date.DayOfWeek(_, Day.Monday)
),
each _ < 5
)
),
type number
}
}
),
{{"Employee", Order.Ascending}}
)
in
ResultPower Query solution 17 for Exclude Weekend Leave Days, proposed by Francesco Bianchi 🇮🇹:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = (rec as record, str as text) as list =>
let
Lst = List.Transform(
Record.ToList(
Record.SelectFields(
rec,
List.Select(Record.FieldNames(rec), each Text.StartsWith(_, str))
)
),
Number.From
)
in
Lst,
ToRec = List.Zip(
{
Source[Employee],
List.Transform(Table.ToRecords(Source), each Fx(_, "Start")),
List.Transform(Table.ToRecords(Source), each Fx(_, "End"))
}
),
T1 = Table.FromRows(ToRec, {"Employee", "StartDate", "EndDate"}),
T2 = Table.AddColumn(T1, "Tb", each Table.FromColumns({[StartDate], [EndDate]}))[[Employee], [Tb]],
Exp = Table.ExpandTableColumn(T2, "Tb", {"Column1", "Column2"}, {"StartDate", "EndDate"}),
Filter = Table.SelectRows(Exp, each [StartDate] <> null and [StartDate] <> ""),
Count = Table.AddColumn(
Filter,
"List",
each
let
a = List.Transform({[StartDate] .. [EndDate]}, each Date.From(_)),
b = List.Select(a, each Date.DayOfWeek(_) < 5)
in
b,
type number
)[[Employee], [List]],
Group = Table.Group(
Count,
{"Employee"},
{{"Total Leaves", each List.Count(List.Distinct(List.Combine([List]))), type number}}
),
Sort = Table.Sort(Group, {{"Employee", Order.Ascending}})
in
SortPower Query solution 18 for Exclude Weekend Leave Days, proposed by Artur Pilipczuk:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
A = Table.ToColumns( Source),
B = 2,
C = List.Skip( A,1),
D = List.Split (C,B),
E = List.Transform(D, each List.InsertRange(_,0,{A{0}})),
F = Table.Combine(List.Transform(E, each Table.FromColumns( _,List.Transform(List.FirstN(Table.ColumnNames(Source),B+1),each Text.Replace(_,"1","" ) )))),
H = Table.SelectRows(F, each ([StartDate] <> null)),
I = Table.TransformColumnTypes(H,{{"StartDate", type date}, {"EndDate", type date}}),
J = Table.RemoveColumns(Table.ExpandListColumn(Table.AddColumn(I, "Date", each List.Dates([StartDate], Duration.Days([EndDate]-[StartDate])+1,hashtag#duration(1, 0, 0, 0))),"Date"),{"StartDate", "EndDate"}),
M = Table.Distinct(Table.TransformColumnTypes(J,{{"Date", type date}})),
P = Table.AddColumn(M, "WeekDayNo", each Date.DayOfWeek([Date],Day.Monday)),
Q = Table.SelectRows(P, each [WeekDayNo] < 5),
S = Table.Sort(Table.Group(Q, {"Employee"}, {{"TotalLeaves", each Table.RowCount(_), Int64.Type}}),{{"Employee", Order.Ascending}})
in
S
And in more readable form https://gist.github.com/artupi/cab2bad72ea2af3b2b83d777c651fc54.
POWER QUERY CHALLENGE 234
POWER QUERY CHALLENGE 234. GitHub Gist: instantly share code, notes, and snippets.Solving the challenge of Exclude Weekend Leave Days with Excel
Excel solution 1 for Exclude Weekend Leave Days, proposed by Bo Rydobon 🇹🇭:
=SORT(HSTACK(A2:A5,
BYROW(B2:G5,
LAMBDA(r,
LET(F,&
WRAPROWS,
K,
TAKE,
w,
F(
r,
2
),
v,
F(SORT(TOCOL(VSTACK(w,
IFS(MAP(w,
LAMBDA(x,
SUM((K(
w,
,
1
)Excel solution 2 for Exclude Weekend Leave Days, proposed by Bo Rydobon 🇹🇭:
=SORT(
HSTACK(
A2:A5,
BYROW(
B2:G5,
LAMBDA(
r,
LET(
w,
WRAPROWS(
r,
2
),
n,
MMULT(
-w,
{1;-1}
)+1,
c,
SEQUENCE(
,
MAX(
n
)
),
SUM(
N(
WEEKDAY(
UNIQUE(
TOCOL(
TAKE(
w,
,
1
)-1+IFS(
c<=n,
c
),
3
)
),
2
)<6
)
)
)
)
)
)
)Excel solution 3 for Exclude Weekend Leave Days, proposed by 🇰🇷 Taeyong Shin:
=SORT(
HSTACK(
A2:A5,
BYROW(
B2:G5,
LAMBDA(
r,
LET(
w,
WRAPROWS(
TOROW(
r,
1
),
2
),
m,
MMULT(
w,
{-1;1}
)+1,
s,
SEQUENCE(
,
MAX(
m
),
0
),
@FREQUENCY(
WEEKDAY(
UNIQUE(
TOCOL(
IFS(
sExcel solution 4 for Exclude Weekend Leave Days, proposed by 🇰🇷 Taeyong Shin:
=LET(
F,
LAMBDA(
a,
b,
NETWORKDAYS.INTL(
a,
b,
"0000011"
)
),
SORT(
HSTACK(
A2:A5,
BYROW(
B2:G5,
LAMBDA(
r,
LET(
w,
WRAPROWS(
r,
2
),
SUM(
F(
TAKE(
w,
,
1
),
DROP(
w,
,
1
)
)
)-SUM(
BYROW(
WRAPROWS(
TOROW(
r,
1
),
3,
""
),
LAMBDA(
x,
IF(
MEDIAN(
x
)=TAKE(
x,
,
-1
),
F(
MEDIAN(
x
),
MAX(
x
)
)
)
)
)
)
)
)
)
)
)
)Excel solution 5 for Exclude Weekend Leave Days, proposed by Kris Jaganah:
=SORT(HSTACK(A2:A5,
BYROW(B2:G5,
LAMBDA(z,
LET(a,
WRAPROWS(
TOROW(
z,
3
),
2
),
b,
MIN(
a
),
c,
SEQUENCE(
MAX(
a
)-b+1,
,
b
),
COUNT(UNIQUE(REDUCE("",
SEQUENCE(
ROWS(
a
)
),
LAMBDA(v,
w,
VSTACK(v,
FILTER(c,
(c>=INDEX(
a,
w,
1
))*(c<=INDEX(
a,
w,
2
))*(WEEKDAY(
c,
2
)<6))))))))))))Excel solution 6 for Exclude Weekend Leave Days, proposed by Julian Poeltl:
=LET(
T,
B2:G5,
R,
ROWS(
T
),
C,
COLUMNS(
T
),
SORT(
HSTACK(
A2:A5,
MAP(
SEQUENCE(
R
),
LAMBDA(
R,
LET(
R,
UNIQUE(
DROP(
REDUCE(
0,
SEQUENCE(
C/2,
,
,
2
),
LAMBDA(
A,
B,
VSTACK(
A,
SEQUENCE(
INDEX(
T,
R,
B+1
)-INDEX(
T,
R,
B
)+1,
,
INDEX(
T,
R,
B
)
)
)
)
),
1
)
),
ROWS(
FILTER(
R,
WEEKDAY(
R,
2
)<6
)
)
)
)
)
)
)
)Excel solution 7 for Exclude Weekend Leave Days, proposed by Oscar Mendez Roca Farell:
=SORT(
HSTACK(
A2:A5,
BYROW(
B2:G5,
LAMBDA(
a,
SUM(
N(
WEEKDAY(
-UNIQUE(
-TEXTSPLIT(
CONCAT(
BYROW(
WRAPROWS(
TOROW(
a,
1
),
2
),
LAMBDA(
r,
ARRAYTOTEXT(
SEQUENCE(
SUM(
r*{-1,
1}
)+1,
,
@r
)
)
)
)&", "
),
,
", ",
1
)
),
2
)<6
)
)
)
)
)
)Excel solution 8 for Exclude Weekend Leave Days, proposed by LEONARD OCHEA 🇷🇴:
=LET(
e,
A2:A5,
d,
B2:G5,
I,
TAKE,
C,
TOCOL,
U,
LAMBDA(
k,
TEXTJOIN(
"|",
,
k
)
),
f,
WRAPROWS(
C(
d
),
2
),
g,
C(
IF(
SEQUENCE(
,
COLUMNS(
d
)/2
),
e
)
),
w,
MAP(
I(
f,
,
1
),
I(
f,
,
-1
),
LAMBDA(
a,
b,
IFERROR(
U(
WORKDAY(
a-1,
SEQUENCE(
,
NETWORKDAYS(
a,
b
)
)
)
),
""
)
)
),
GROUPBY(
g,
w,
LAMBDA(
x,
COUNTA(
UNIQUE(
TEXTSPLIT(
U(
x
),
"|"
),
1
)
)
),
,
0
)
)Excel solution 9 for Exclude Weekend Leave Days, proposed by Md. Zohurul Islam:
=LET(p,
VSTACK(
A2:A5,
A2:A5,
A2:A5
),
q,
VSTACK(
B2:C5,
D2:E5,
F2:G5
),
r,
HSTACK(
p,
q
),
s,
SORT(
FILTER(
r,
TAKE(
q,
,
1
)>0
),
1,
1
),
nam,
TAKE(
s,
,
1
),
dt,
DROP(
s,
,
1
),
a,
TAKE(
dt,
,
1
),
b,
TAKE(
dt,
,
-1
),
c,
MAP(
a,
b,
LAMBDA(
x,
y,
TEXTJOIN(
"-",
1,
SEQUENCE(
,
y-x+1,
x,
1
)
)
)
),
d,
DROP(REDUCE("",
UNIQUE(
nam
),
LAMBDA(x,
y,
LET(aa,
TEXTJOIN(
"-",
1,
FILTER(
c,
nam=y
)
),
bb,
TEXT(
UNIQUE(
ABS(
TEXTSPLIT(
aa,
"-"
)
),
1
),
"ddd"
),
cc,
COUNTA(FILTER(bb,
(bb<>"Sat")*(bb<>"Sun"))),
dd,
VSTACK(
x,
cc
),
dd))),
1),
e,
HSTACK(
UNIQUE(
nam
),
d
),
hdr,
HSTACK(
"Employees",
"Total Leaves"
),
result,
VSTACK(
hdr,
e
),
result)Excel solution 10 for Exclude Weekend Leave Days, proposed by Asheesh Pahwa:
=LET(emp,
A2:A5,
d,
DROP(REDUCE("",
SEQUENCE(
4
),
LAMBDA(x,
y,
VSTACK(x,
LET(I,
INDEX(
B2:G5,
y,
),
c,
TOROW(
I,
1,
1
),
w,
WRAPROWS(
c,
2
),
s,
SEQUENCE(
ROWS(
TAKE(
w,
,
1
)
)
),
r,
DROP(REDUCE("",
s,
LAMBDA(a,
v,
VSTACK(a,
LET(_i,
INDEX(
w,
v,
),
ed,
@TAKE(
_i,
,
-1
),
sd,
@TAKE(
_i,
,
1
),
sq,
SEQUENCE((ed-sd)+1,
,
sd),
sq)))),
1),
txt,
TEXT(
UNIQUE(
r
),
"ddd"
),
COUNTA(FILTER(txt,
(txt<>"Sat")*(txt<>"Sun"))))))),
1),
HSTACK(
SORT(
emp
),
SORTBY(
d,
emp,
1
)
))Excel solution 11 for Exclude Weekend Leave Days, proposed by Jaroslaw Kujawa:
=SORT(
HSTACK(
A2:A5;
BYROW(
B2:G5;
LAMBDA(
u;
LET(
b;
REDUCE(
"";
u;
LAMBDA(
a;
x;
VSTACK(
a;
IF(
MOD(
COLUMN(
x
);
2
);
SEQUENCE(
1+x-OFFSET(
x;
0;
-1
);
;
OFFSET(
x;
0;
-1
)
);
""
)
)
)
);
c;
GROUPBY(
FILTER(
b;
b<>""
);
FILTER(
b;
b<>""
);
MAX
);
d;
WEEKDAY(
DROP(
c;
-1;
-1
);
2
);
& COUNT(
FILTER(
d;
d<6
)
)
)
)
)
)
)Excel solution 12 for Exclude Weekend Leave Days, proposed by Ankur Sharma:
=SORT(
HSTACK(
A2:A5,
BYROW(
B2:G5,
LAMBDA(
r,
LET(
loop,
WRAPROWS(
r,
2,
""
),
s,
TAKE(
loop,
,
1
),
e,
DROP(
loop,
,
1
),
d,
TEXTJOIN(
", ",
,
MAP(
s,
e,
LAMBDA(
y,
z,
IF(
y = "",
"",
TEXTJOIN(
", ",
,
WORKDAY(
y - 1,
SEQUENCE(
NETWORKDAYS(
y,
z
)
)
)
)
)
)
)
),
COUNT(
UNIQUE(
--TEXTSPLIT(
d,
,
", "
)
)
)
)
)
)
),
1
)Excel solution 13 for Exclude Weekend Leave Days, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=VSTACK(
HSTACK(
"Employee",
"TotalLeave"
),
LET(
c,
SORT(
A2:A5,
,
1
),
HSTACK(
c,
MAP(
c,
LAMBDA(
j,
XLOOKUP(
j,
A2:A5,
BYROW(
B2:G5,
LAMBDA(
i,
LET(
y,
WRAPROWS(
i,
2
),
LET(
x,
SEARCH(
TEXT(
UNIQUE(
VALUE(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
TAKE(
y,
,
1
),
TAKE(
y,
,
-1
),
LAMBDA(
a,
b,
TEXTJOIN(
",",
,
SEQUENCE(
b-a+1,
,
a,
1
)
)
)
)
),
,
","
)
)
),
"ddd"
),
{"Sat",
"Sun"}
),
COUNTA(
x
)/2-SUM(
IFERROR(
x,
0
)
)
)
)
)
)
)
)
)
)
)
)Excel solution 14 for Exclude Weekend Leave Days, proposed by SHIV SHANKAR KUMAR:
=ARRAYFORMULA(COUNTA(UNIQUE(FLATTEN(FILTER(SEQUENCE(
MAX(
C2,
E2,
G2
) - MIN(
B2,
D2,
F2
) + 1,
1,
MIN(
B2,
D2,
F2
),
1
),
ISNUMBER(
MATCH(
WEEKDAY(
SEQUENCE(
MAX(
C2,
E2,
G2
) - MIN(
B2,
D2,
F2
) + 1,
1,
MIN(
B2,
D2,
F2
),
1
)
),
{2,
3,
4,
5,
6},
0
)
) * ((SEQUENCE(
MAX(
C2,
E2,
G2
) - MIN(
B2,
D2,
F2
) + 1,
1,
MIN(
B2,
D2,
F2
),
1
) >= B2) * (SEQUENCE(
MAX(
C2,
E2,
G2
) - MIN(
B2,
D2,
F2
) + 1,
1,
MIN(
B2,
D2,
F2
),
1
) <= C2) + (SEQUENCE(
MAX(
C2,
E2,
G2
) - MIN(
B2,
D2,
F2
) + 1,
1,
MIN(
B2,
D2,
F2
),
1
) >= D2) * (SEQUENCE(
MAX(
C2,
E2,
G2
) - MIN(
B2,
D2,
F2
) + 1,
1,
MIN(
B2,
D2,
F2
),
1
) <= E2) + (SEQUENCE(
MAX(
C2,
E2,
G2
) - MIN(
B2,
D2,
F2
) + 1,
1,
MIN(
B2,
D2,
F2
),
1
) >= F2) * (SEQUENCE(
MAX(
C2,
E2,
G2
) - MIN(
B2,
D2,
F2
) + 1,
1,
MIN(
B2,
D2,
F2
),
1
) <= G2))))))Solving the challenge of Exclude Weekend Leave Days with Python
Python solution 1 for Exclude Weekend Leave Days, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_234.xlsx"
input = pd.read_excel(path, usecols="A:G", nrows=5)
test = pd.read_excel(path, usecols="A:B", skiprows=9, nrows=5)
input_long = input.melt(id_vars=['Employee'], var_name='variable', value_name='value')
input_long[['variable', 'number']] = input_long['variable'].str.extract(r'(D+)(d+)')
input_long = input_long.dropna().pivot(index=['Employee', 'number'], columns='variable', values='value').reset_index()
input_long = input_long.assign(seq=input_long.apply(lambda row: pd.date_range(start=row['StartDate'], end=row['EndDate']), axis=1))
.explode('seq').reset_index(drop=True)
input_long = input_long[input_long['seq'].dt.weekday < 5]
result = input_long.groupby('Employee').agg(TotalLeaves=('seq', 'nunique')).reset_index()
print(result.equals(test)) # True
Python solution 2 for Exclude Weekend Leave Days, proposed by Artur Pilipczuk:
import polars as pl
df=pl.read_excel(r"PQ_Challenge_234.xlsx",sheet_name="Data")
cn=list(df.columns)
cnl=[[x,cn[i*2+1+1]] for i , x in enumerate(cn[1::2])]
dfn=pl.DataFrame()
for c in cnl:
df_=df.select([cn[0]]+c)
df_.columns=("Employee","StartDate", "EndDate")
if len(dfn)==0:
dfn=df_
else:
dfn=dfn.vstack(df_)
df=(dfn
.filter(
~pl.any_horizontal(pl.all().is_null()))
.with_columns(date=pl.date_ranges("StartDate","EndDate","1d"))
.select("Employee", "date")
.explode("date")
.unique(maintain_order=True)
.with_columns(week_day=pl.col("date").dt.weekday())
.filter(pl.col("week_day")<6)
.group_by("Employee").agg(leave_days=pl.len())
.sort("Employee")
)
print(df)
Python solution 3 for Exclude Weekend Leave Days, proposed by Artur Pilipczuk:
import polars as pl
df=pl.read_excel(r"PQ_Challenge_234.xlsx",sheet_name="Data")
df=pl.DataFrame(employees)
cn=list(df.columns)
cnl=list(map(str,list(range(len(cn)))))
cnl[0]=cn[0]
df.columns=cnl
df=(df.unpivot(index="Employee")
.with_columns(
date_type=pl.when(pl.col("variable").cast(pl.Int16).mod(2)==1).then(pl.lit("StartDate")).otherwise(pl.lit("EndDate")),
variable=pl.when(pl.col("variable").cast(pl.Int16).mod(2)==0).then(pl.col("variable").cast(pl.Int16)-1).otherwise(pl.col("variable").cast(pl.Int16))
)
.pivot(on="date_type",index=["Employee","variable"],values="value")
.select("Employee","StartDate","EndDate")
.filter(
~pl.any_horizontal(pl.all().is_null()))
.with_columns(date=pl.date_ranges("StartDate","EndDate","1d"))
.select("Employee", "date")
.explode("date")
.unique(maintain_order=True)
.with_columns(week_day=pl.col("date").dt.weekday())
.filter(pl.col("week_day")<6)
.group_by("Employee").agg(leave_days=pl.len())
.sort("Employee")
)
print(df)
Solving the challenge of Exclude Weekend Leave Days with R
R solution 1 for Exclude Weekend Leave Days, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_234.xlsx"
input = read_excel(path, range = "A1:G5")
test = read_excel(path, range = "A10:B14")
result = input %>%
pivot_longer(-c(1), names_to = c(".value", "number"), names_pattern = "(.*)(\d)") %>%
na.omit() %>%
mutate(seq = map2(StartDate, EndDate, ~seq.Date(from = as.Date(.x), to = as.Date(.y), by = "day"))) %>%
unnest(cols = seq) %>%
mutate(Weekday = wday(seq, week_start = 1)) %>%
filter(Weekday %in% c(1:5)) %>%
summarise(TotalLeaves = n_distinct(seq), .by = Employee) %>%
arrange(Employee)
all.equal(result, test, check.attributes = FALSE)
# [1] TRUE
&