Populate the From and To Date based on type of leaves for a person. For the purpose of continuity, Sat and Sun will not be considered. If a leave is applied for Sat and Sun, that is not to be considered.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 210
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Leave Group From To with Power Query
Power Query solution 1 for Leave Group From To, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.FromRecords(
Table.Group(
Table.SelectRows(Source, each Date.DayOfWeek([Date], 1) < 5),
{"Name", "Type"},
{
"A",
each [Name = [Name]{0}, From Date = [Date]{0}, To Date = List.Max([Date]), Type = [Type]{0}]
},
0
)[A]
)
in
SPower Query solution 2 for Leave Group From To, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Group(Source, {"Name"}, {"All", each
Table.Group( Table.AddIndexColumn( _ ,"I", 1,1) , {"Type"} ,{"Bis" , each
Table.Group( Table.AddColumn( Table.AddIndexColumn( _ ,"In" , 1,1) ,
"Diff" , each [I]-[In] ) , {"Diff"} ,
{{"To Date", each List.Max([Date])} , {"B", each _ } }) }) }),
Xpan = Table.ExpandTableColumn(Group, "All", {"Bis"}),
Xp1 = Table.ExpandTableColumn(Xpan, "Bis", { "To Date", "B"}),
Xpand = Table.ExpandTableColumn(Xp1, "B", {"Date", "Type"}, {"From Date", "Type"}),
Reorder = Table.ReorderColumns(Xpand,{"Name", "From Date", "To Date", "Type"}),
Unique = Table.Distinct(Reorder, {"Name", "To Date", "Type"}),
Sort = Table.Sort(Unique,{{each List.PositionOf(Unique[Name],[Name]), 0}, {"From Date", 0}}),
FrDate = Table.TransformColumns( Sort , {"From Date" , each Date.From( if Date.DayOfWeek(_) =0 then _ + hashtag#duration(1,0,0,0) else if Date.DayOfWeek(_) =6 then _ + hashtag#duration(2,0,0,0) else _ )}),
ToDate = Table.TransformColumns( FrDate , {"To Date" , each Date.From( if Date.DayOfWeek(_) =0 then _ - hashtag#duration(2,0,0,0) else if Date.DayOfWeek(_) =6 then _ - hashtag#duration(1,0,0,0) else _ )})
in
ToDate
Power Query solution 3 for Leave Group From To, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Group = Table.Group(
Source,
{"Name", "Type"},
{
{"From", each List.Min(List.Skip([Date], (f) => Date.DayOfWeek(f, 1) > 4))},
{"To", each List.Max(List.RemoveLastN([Date], (f) => Date.DayOfWeek(f, 1) > 4))}
},
GroupKind.Local
),
Return = Table.ReorderColumns(Group, {"Name", "From", "To", "Type"})
in
ReturnPower Query solution 4 for Leave Group From To, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Name", "Type"},
{
{
"A",
each
let
a = [Date],
b = Date.From(a{0}),
c = List.Select(a, each Date.DayOfWeek(_) <> 6 and Date.DayOfWeek(_) <> 0),
d = Date.From(List.Last(c)),
e = Table.FromColumns({{b}, {d}}, {"From Date", "To Date"})
in
e
}
},
0
)[[Name], [A], [Type]],
Sol = Table.ExpandTableColumn(Group, "A", Table.ColumnNames(Group[A]{0}))
in
SolPower Query solution 5 for Leave Group From To, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
grp = Table.Group(
Fonte,
{"Name", "Type"},
{
"tab",
each
let
a = Table.SelectRows(_, each Date.DayOfWeek([Date]) <> 0 and Date.DayOfWeek([Date]) <> 6),
fd = List.Min(a[Date]),
td = List.Max(a[Date])
in
Table.FromRows(
{{[Name]{0}} & {fd} & {td} & {[Type]{0}}},
{"Name", "From Date", "To Date", "Type"}
)
},
0
)[tab],
res = Table.Combine(grp)
in
resPower Query solution 6 for Leave Group From To, proposed by Hussein SATOUR:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ToDate = Table.TransformColumnTypes(Source,{{"Date", type date}}),
EliminateWE = Table.SelectRows(ToDate, each (Date.DayOfWeek([Date],1) < 5)),
TheMerge = Table.CombineColumns(Table.TransformColumnTypes(EliminateWE, {{"Date", type text}}, "en-US"),{"Name", "Type", "Date"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
TheWork =Table.SelectRows(Table.FromList(Text.Split(List.Accumulate(TheMerge[Merged], "",(x,y) =>if Text.AfterDelimiter(Text.BeforeDelimiter(x, "|", {0, RelativePosition.FromEnd}), "-", {0, RelativePosition.FromEnd}) = Text.BeforeDelimiter(y, "|", {0, RelativePosition.FromEnd}) then x &";"& Text.AfterDelimiter(y, "|", {0, RelativePosition.FromEnd}) else x&"-"&y), "-")), each ([Column1] <> "")),
SplitResult = Table.SplitColumn(TheWork, "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Name", "Type", "Column1.3"}),
ColFrom = Table.AddColumn(SplitResult, "From", each Text.BeforeDelimiter([Column1.3], ";"), type text),
ColTo = Table.AddColumn(ColFrom, "To", each Text.AfterDelimiter([Column1.3], ";", {0, RelativePosition.FromEnd}), type text),
in RemoveCols = Table.RemoveColumns(ColTo,{"Column1.3"})
Power Query solution 7 for Leave Group From To, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData210"]}[Content],
Filter = Table.SelectRows(Source, each Date.DayOfWeek([Date], Day.Monday) < 5),
Group = Table.Group(
Filter,
{"Name", "Type"},
{{"From Date", each List.Min([Date])}, {"To Date", each List.Max([Date])}},
GroupKind.Local
),
ReorderCols = Group[[Name], [From Date], [To Date], [Type]]
in
ReorderColsPower Query solution 8 for Leave Group From To, proposed by Mihai Radu O:
let
Source = Table.TransformColumnTypes(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{"Date", type date}
),
grup = Table.Group(
Source,
{"Name", "Type"},
{
{
"dt",
each [
a = [Date],
b = List.Select(a, (x) => Date.DayOfWeek(x, Day.Monday) < 5),
c = Table.FromColumns({{List.Min(b)}, {List.Max(b)}}, {"From Date", "To Date"})
][c]
}
},
GroupKind.Local
)[[Name], [dt], [Type]],
a = Table.ExpandTableColumn(grup, "dt", {"From Date", "To Date"})
in
aSolving the challenge of Leave Group From To with Excel
Excel solution 1 for Leave Group From To, proposed by Bo Rydobon 🇹🇭:
=LET(
a,
A2:A17,
b,
B2:B17,
c,
C2:C17,
d,
a&c,
HSTACK(
FILTER(
HSTACK(
a,
WORKDAY(
b-1,
1
)
),
d<>VSTACK(
0,
DROP(
d,
-1
)
)
),
FILTER(
HSTACK(
WORKDAY(
b+1,
-1
),
c
),
d<>VSTACK(
DROP(
d,
1
),
0
)
)
)
)Excel solution 2 for Leave Group From To, proposed by محمد حلمي:
=REDUCE(
E1:H1,
B2:B17,
LAMBDA(
a,
v,
LET(
i,
@+v:A17,
d,
WORKDAY(
v-1,
1
),
e,
OFFSET(
v,
,
1
),
IF(
i&e=OFFSET(
v,
-1,
-1
)&OFFSET(
v,
-1,
1
),
IF(
d>v,
a,
VSTACK(
DROP(
a,
-1
),
HSTACK(
TAKE(
a,
-1,
2
),
d,
e
)
)
),
VSTACK(
a,
HSTACK(
i,
d,
d,
e
)
)
)
)
)
)Excel solution 3 for Leave Group From To, proposed by 🇰🇷 Taeyong Shin:
=LET(
d,
B2:B17,
c,
A2:A17&C2:C17,
DROP(
CHOOSECOLS(
GROUPBY(
HSTACK(
SCAN(
0,
c<>VSTACK(
0,
DROP(
c,
-1
)
),
SUM
),
CHOOSECOLS(
A2:C17,
1,
3
)
),
d,
HSTACK(
MIN,
MAX
),
0,
0,
,
WEEKDAY(
d,
2
)<6
),
2,
4,
5,
3
),
1
)
)Excel solution 4 for Leave Group From To, proposed by Julian Poeltl:
=LET(
T,
A2:C17,
TT,
FILTER(
T,
WEEKDAY(
CHOOSECOLS(
T,
2
),
2
)<6
),
N,
TAKE(
TT,
,
1
),
Dt,
CHOOSECOLS(
TT,
2
),
Y,
TAKE(
TT,
,
-1
),
C,
N&Y,
VSTACK(
HSTACK(
"Name",
"From Date",
"To Date",
"Type"
),
HSTACK(
FILTER(
HSTACK(
N,
Dt
),
C<>VSTACK(
0,
DROP(
C,
-1
)
)
),
FILTER(
HSTACK(
Dt,
Y
),
C<>VSTACK(
DROP(
C,
1
),
0
)
)
)
)
)Excel solution 5 for Leave Group From To, proposed by Oscar Mendez Roca Farell:
=LET(
n,
A2:A17,
t,
C2:C17,
m,
n&t,
F,
LAMBDA(
i,
j,
FILTER(
DROP(
HSTACK(
j,
WORKDAY(
B2:B17-i,
i
),
j
),
,
-i
),
m<>DROP(
VSTACK(
0,
DROP(
m,
-i
),
0
),
-i
)
)
),
HSTACK(
F(
1,
n
),
F(
-1,
t
)
)
)Excel solution 6 for Leave Group From To, proposed by LEONARD OCHEA 🇷🇴:
=LET(
n,
A2:A17,
d,
B2:B17,
t,
C2:C17,
H,
HSTACK,
V,
VSTACK,
E,
DROP,
m,
GROUPBY(
H(
SCAN(
0,
N(
V(
"",
E(
t,
-1
)
)<>t
),
SUM
),
n,
t
),
d,
H(
MIN,
MAX
),
,
0,
,
WEEKDAY(
d,
2
)<6
),
V(
E1:H1,
CHOOSECOLS(
E(
m,
1
),
{2;4;5;3}
)
)
)Excel solution 7 for Leave Group From To, proposed by Md. Zohurul Islam:
=LET(
u,
A2:A17,
v,
B2:B17,
w,
C2:C17,
hdr,
{"Name",
"From Date",
"To Date",
"Type"},
a,
u&w,
b,
VSTACK(
0,
DROP(
a,
-1
)
),
c,
ABS(
a<>b
),
d,
SCAN(
0,
c,
SUM
),
e,
WEEKDAY(
v,
2
)<6,
f,
FILTER(
HSTACK(
d,
u,
w
),
e
),
g,
FILTER(
v,
e
),
h,
GROUPBY(
f,
g,
HSTACK(
MIN,
MAX
),
0,
0
),
I,
CHOOSECOLS(
DROP(
h,
1,
1
),
1,
3,
4,
2
),
j,
VSTACK(
hdr,
I
),
j
)Excel solution 8 for Leave Group From To, proposed by Mihai Radu O:
=LET(n,
A2:A17,
dt,
B2:B17,
t,
C2:C17,
a,
SCAN(0,
t,
LAMBDA(x,
y,
IF((OFFSET(
y,
,
-2
)=OFFSET(
y,
-1,
-2
))*(y = OFFSET(
y,
-1,
)),
x,
x+1)))*(WEEKDAY(
dt,
2
)<6),
DROP(
GROUPBY(
a,
HSTACK(
n,
dt,
dt,
t
),
HSTACK(
SINGLE,
MIN,
MAX,
SINGLE
),
0,
0,
,
a>0
),
1,
1
)
)Solving the challenge of Leave Group From To with Python
Python solution 1 for Leave Group From To, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_210.xlsx"
input = pd.read_excel(path, usecols="A:C", skiprows=0, nrows=17)
test = pd.read_excel(path, usecols="E:H", skiprows=0, nrows=9)
test.c&olumns = test.columns.str.replace(".1", "")
r1 = input[["Name", "Date"]].copy()
r1["Date"] = pd.to_datetime(r1["Date"])
r1 = r1.set_index("Date").resample("D").ffill().reset_index()
r2 = r1.merge(input, on = ["Name", "Date"], how = "left")
r2["Weekday_num"] = r2["Date"].dt.weekday
r2["Type"] = r2["Type"].where(r2["Weekday_num"] != 5, r2["Type"].shift(1))
r2["Type"] = r2["Type"].where(r2["Weekday_num"] != 6, r2["Type"].shift(2))
r2["Group"] = r2["Type"].ne(r2["Type"].shift()).cumsum()
r2 = r2.dropna(subset = ["Type"])
r2 = r2[~r2["Weekday_num"].isin([5, 6])]
r2 = r2.groupby(["Name", "Type", "Group"]).agg({"Date": ["min", "max"]}).reset_index()
r2.columns = r2.columns.droplevel()
r2.columns = ["Name", "Type", "Group", "From Date", "To Date"]
r2 = r2.sort_values(["Name", "To Date"], ascending = [False, True]).reset_index(drop = True)
r2 = r2[["Name", "From Date", "To Date", "Type"]]
print(r2.equals(test)) # True
Solving the challenge of Leave Group From To with R
R solution 1 for Leave Group From To, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_210.xlsx"
input = read_xlsx(path, range = "A1:C17")
test = read_xlsx(path, range = "E1:H10")
r1 = input %>%
select(Name, Date) %>%
group_by(Name) %>%
summarise(Date = list(seq(min(Date), max(Date), by = "day"))) %>%
unnest(Date) %>%
left_join(input, by = c("Name", "Date")) %>%
mutate(wday = wday(Date, week_start = 1),
Type = case_when(
wday == 6 ~ lag(Type, 1),
wday == 7 ~ lag(Type, 2),
TRUE ~ Type
)) %>%
mutate(cons = consecutive_id(Type), .by = "Name") %>%
filter(!is.na(Type),
wday %in% 1:5) %>%
summarise(`From Date` = min(Date),
`To Date` = max(Date),
.by = c(Name, Type, cons)) %>%
select(Name, `From Date`, `To Date`, Type) %>%
arrange(desc(Name))
identical(r1, test)
# [1] TRUE
&&