Populate the End dates against all owners in a pivot format as shown. Here End Date = Start Date + Max of Tasks Duration Days Ex. T1 End Date (row 3) = 2024-05-01 + 45 = 2024-06-15 T1, T2, T3 end Date (row 6) = 2024-05-18 + MAX(45, 23, 18) = 2024-07-02
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 209
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Add Task Duration to Start with Power Query
Power Query solution 1 for Add Task Duration to Start, proposed by Zoran Milokanović:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
T = each Text.Split(_, ", "),
D = each Table.SelectRows(Source("Table2"), (r) => List.Contains(_, r[Task])),
P = Table.FromRows(
List.TransformMany(
Table.ToRows(Source("Table1")),
each T(_{1}),
(i, _) => {i{0}} & D({_})[Owner] & {i{2} + Duration.From(List.Max(D(T(i{1}))[Duration Days]))}
),
{"Process", "A", "V"}
),
S = Table.Pivot(P, List.Sort(List.Distinct(P[A])), "A", "V")
in
SPower Query solution 2 for Add Task Duration to Start, proposed by Kris Jaganah:
let
T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Ans = Table.AddColumn(T1, "Wkg", each [
a = Table.SelectRows(T2,(x)=> Text.Contains( [Task] , x[Task])),
End = Date.ToText( Date.From( [Start Date] + hashtag#duration( List.Max( a[Duration Days]),0,0,0) ) , [Format = "YYYY-MM-DD"]) ,
Owner = a[Owner]]),
Keep = Table.SelectColumns(Ans,{"Process", "Wkg"}),
Xpa = Table.ExpandRecordColumn(Keep, "Wkg", {"End", "Owner"}),
Xpan = Table.ExpandListColumn(Xpa, "Owner"),
Pivot = Table.Pivot(Xpan, List.Sort( List.Distinct(Xpan[Owner])), "Owner", "End")
in
Pivot
Power Query solution 3 for Add Task Duration to Start, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
AddT2 = Table.AddColumn(
T1,
"A",
each
let
a = Text.Split([Task], ", "),
b = List.Transform(a, each Table.SelectRows(T2, (x) => _ = x[Task])),
c = Table.Combine(b),
d =
if Table.RowCount(c) = 1 then
Table.RemoveColumns(c, "Task")
else
Table.TransformColumns(
Table.RemoveColumns(c, "Task"),
{"Duration Days", each List.Max(c[Duration Days])}
)
in
d
),
Expand = Table.ExpandTableColumn(AddT2, "A", Table.ColumnNames(AddT2[A]{0})),
EndDate = Table.AddColumn(
Expand,
"ED",
each Date.ToText(Date.AddDays(Date.From([Start Date]), [Duration Days]), "yyyy-MM-dd")
)[[Process], [Owner], [ED]],
Sol = Table.Pivot(EndDate, List.Sort(List.Distinct(EndDate[Owner])), "Owner", "ED")
in
SolPower Query solution 4 for Add Task Duration to Start, proposed by 🇵🇪 Ned Navarrete C.:
let
T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Transform = Table.TransformColumns(
T1,
{{"Task", each Text.Split(_, ", ")}, {"Start Date", each Date.From(_)}}
),
NewDate = Table.AddColumn(
Transform,
"Custom",
each [
a = List.PositionOfAny(T2[Task], [Task], 3),
b = List.Accumulate(a, {}, (s, i) => s & {T2[Duration Days]{i}}),
c = List.Max(b),
d = Date.AddDays([Start Date], c)
][d]
),
Expand = Table.ExpandListColumn(NewDate, "Task"),
Merged = Table.NestedJoin(Expand, {"Task"}, T2, {"Task"}, "X"),
Transform2 = Table.TransformColumns(Merged, {"X", each _{0}[Owner]}),
Removed = Table.RemoveColumns(Transform2, {"Task", "Start Date"}),
Pivoted = Table.Pivot(Removed, List.Sort(List.Distinct(Removed[X])), "X", "Custom")
in
PivotedPower Query solution 5 for Add Task Duration to Start, proposed by Eric Laforce:
let
Sources = Table.SelectRows(Excel.CurrentWorkbook(), each Text.StartsWith([Name], "tData209"))[
Content
],
T1_SplitTask = Table.TransformColumns(Sources{0}, {"Task", each Text.Split(_, ", ")}),
T1_ExpandTask = Table.ExpandListColumn(T1_SplitTask, "Task"),
JoinT2 = Table.Join(T1_ExpandTask, "Task", Sources{1}, "Task"),
Add_End = Table.AddColumn(JoinT2, "End", each Date.AddDays([Start Date], [Duration Days])),
Group = Table.Group(
Add_End[[Process], [Owner], [End]],
{"Process", "Owner"},
{"End", each List.Max([End])}
),
Pivot = Table.Pivot(
Group,
List.Sort(List.Distinct(Group[Owner])),
"Owner",
"End",
each DateTime.ToText(List.Max(_), "yyyy-MM-dd")
)
in
PivotPower Query solution 6 for Add Task Duration to Start, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S2 = Excel.CurrentWorkbook(){[Name = "T_2"]}[Content],
S1 = Excel.CurrentWorkbook(){[Name = "T_1"]}[Content],
A = Table.TransformColumnTypes(S1, {{"Start Date", type date}}),
B = Table.AddColumn(A, "TL", each Text.Split([Task], ", ")),
C = Table.ExpandListColumn(B, "TL"),
D = Table.NestedJoin(C, {"TL"}, S2, {"Task"}, "N"),
E = Table.ExpandTableColumn(D, "N", {"Owner", "Duration Days"}, {"Owner", "Duration Days"}),
F = Table.Group(
E,
{"Process", "Task", "Start Date"},
{
{"Duration Days", each List.Max([Duration Days]), type number},
{
"Tbl",
each _,
type table [
Process = text,
Task = text,
Start Date = nullable date,
TL = text,
Owner = text,
Duration Days = number
]
}
}
),
G = Table.SelectColumns(F, {"Duration Days", "Tbl"}),
H = Table.ExpandTableColumn(
G,
"Tbl",
{"Process", "Start Date", "TL", "Owner"},
{"Process", "Start Date", "TL", "Owner"}
),
I = Table.AddColumn(H, "End Date", each Date.AddDays([Start Date], [Duration Days])),
J = Table.SelectColumns(I, {"Process", "Owner", "End Date"}),
K = Table.Pivot(J, List.Distinct(J[Owner]), "Owner", "End Date")
in
KSolving the challenge of Add Task Duration to Start with Excel
Excel solution 1 for Add Task Duration to Start, proposed by Bo Rydobon 🇹🇭:
=LET(
t,
B3:B10,
v,
A14:C17,
e,
MAP(
t,
LAMBDA(
x,
MAX(
VLOOKUP(
TEXTSPLIT(
x,
", "
),
v,
3,
)
)
)
)+C3:C10,
o,
VLOOKUP(
TEXTSPLIT(
CONCAT(
t&"_"
),
", ",
"_",
1
),
v,
2,
),
L,
LAMBDA(
x,
TOCOL(
IF(
o>0,
x
),
3
)
),
PIVOTBY(
L(
A3:A10
),
L(
o
),
L(
e
),
MAX,
,
0,
,
0
)
)Excel solution 2 for Add Task Duration to Start, proposed by Bo Rydobon 🇹🇭:
=LET(
a,
A3:A10,
t,
B3:B10,
v,
A14:C17,
e,
MAP(
t,
LAMBDA(
x,
MAX(
VLOOKUP(
TEXTSPLIT(
x,
", "
),
v,
3,
)
)
)
)+C3:C10,
u,
UNIQUE(
a
),
HSTACK(
VSTACK(
A2,
u
),
SORT(
VSTACK(
TOROW(
INDEX(
v,
,
2
)
),
XLOOKUP(
"*"&TOROW(
TAKE(
v,
,
1
)
)&u&"*",
SUBSTITUTE(
t,
", ",
a
)&a,
e,
"",
2
)
),
,
,
1
)
)
)Excel solution 3 for Add Task Duration to Start, proposed by محمد حلمي:
=LET(
p,
A3:A10,
c,
C14:C17,
k,
A14:A17,
r,
B14:B17,
X,
LAMBDA(
v,
MAP(
p,
B3:B10,
LAMBDA(
a,
b,
LET(
u,
ISNUMBER(
FIND(
k,
b
)
),
IF(
v,
XLOOKUP(
b,
k,
c,
MAX(
c*u
)
),
CONCAT(
REPT(
a&r,
u
)
)
)
)
)
)
),
w,
TOROW(
SORT(
r
)
),
e,
UNIQUE(
p
),
VSTACK(
HSTACK(
A2,
w
),
HSTACK(
e,
XLOOKUP(
"*"&e&w&"*",
X(
0
),
C3:C10+X(
1
),
"",
2
)
)
)
)Excel solution 4 for Add Task Duration to Start, proposed by 🇰🇷 Taeyong Shin:
=LET(
task,
B3:B10,
f,
LAMBDA(
x,
TEXTSPLIT(
TEXTAFTER(
", " & x,
", ",
{1,
2,
3}
),
", "
)
),
f_2,
LAMBDA(
x,
REGEXREPLACE(
task,
"bw+b",
x
)
),
f_3,
LAMBDA(
n,
VLOOKUP(
f(
task
),
A14:C17,
n,
)
),
PIVOTBY(TOCOL(f(f_2(A3:A10)),
2),
TOCOL(f_3(2),
2),
TOCOL(f(f_2(C3:C10)) + BYROW(IFNA(f_3(3),
0),
MAX),
2),
SUM,
,
0,
,
0)
)Excel solution 5 for Add Task Duration to Start, proposed by Julian Poeltl:
=LET(P,
A3:A10,
T,
B3:B10,
S,
C3:C10,
TT,
A14:A17,
O,
B14:B17,
D,
C14:C17,
R,
S+MAP(
T,
LAMBDA(
A,
MAX(
XLOOKUP(
TEXTSPLIT(
A,
", "
),
TT,
D
)
)
)
),
N,
MAP(
T,
LAMBDA(
A,
CONCAT(
XLOOKUP(
TEXTSPLIT(
A,
", "
),
TT,
O
)
)
)
),
U,
UNIQUE(
P
),
RN,
TOROW(
SORT(
O
)
),
VSTACK(HSTACK(
"Process",
RN
),
HSTACK(U,
IFERROR(MAP(RN&"|"&U,
LAMBDA(A,
FILTER(R,
(ISNUMBER(
SEARCH(
TEXTBEFORE(
A,
"|"
),
N
)
)*(P=TEXTAFTER(
A,
"|"
)))))),
""))))Excel solution 6 for Add Task Duration to Start, proposed by Oscar Mendez Roca Farell:
=LET(
u,
UNIQUE(
A3:A10
),
r,
REDUCE(
"",
A3:A10,
LAMBDA(
i,
x,
LET(
f,
TAKE(
x:C10,
1
),
t,
TEXTSPLIT(
INDEX(
f,
1,
2
),
,
", "
),
d,
MAX(
f
)+MAX(
VLOOKUP(
t,
A14:C17,
3,
)
),
VSTACK(
i,
IFNA(
HSTACK(
REPT(
@+f,
1^N(
t
)
)&t,
d
),
d
)
)
)
)
),
HSTACK(
VSTACK(
A2,
u
),
SORT(
IFNA(
VSTACK(
TOROW(
B14:B17
),
VLOOKUP(
u&TOROW(
A14:A17
),
r,
2,
)
),
""
),
,
,
1
)
)
)Excel solution 7 for Add Task Duration to Start, proposed by Sunny Baggu:
=LET(
_t1,
TEXTSPLIT(
ARRAYTOTEXT(
B3:B10 & ";" & C3:C10
),
";",
{", ",
","}
),
_t2,
TEXTSPLIT(
ARRAYTOTEXT(
B3:B10 & ";" & A3:A10
),
";",
{", ",
","}
),
L,
LAMBDA(
rng,
LET(
_s,
1 - SEQUENCE(
ROWS(
rng
)
),
_a,
SORTBY(
rng,
_s
),
_b,
SCAN(
"",
_a,
LAMBDA(
a,
v,
IFNA(
v,
a
)
)
),
_c,
SORTBY(
_b,
_s
),
_c
)
),
_tbl,
HSTACK(
L(
TAKE(
_t2,
,
-1
)
),
TAKE(
_t2,
,
1
),
--L(
TAKE(
_t1,
,
-1
)
)
),
_m,
MAP(
B3:B10,
LAMBDA(
t,
MAX(
XLOOKUP(
TEXTSPLIT(
& t,
,
", "
),
A14:A17,
C14:C17,
0
)
)
)
),
_up,
UNIQUE(
A3:A10
),
_sp,
TOROW(
SORT(
B14:B17
)
),
_ut,
XLOOKUP(
_sp,
B14:B17,
A14:A17
),
_v1,
XLOOKUP(
_up & _ut,
INDEX(
_tbl,
,
1
) & INDEX(
_tbl,
,
2
),
TAKE(
_tbl,
,
-1
),
""
),
_v2,
IFERROR(
_v1 + XLOOKUP(
_v1 & _up,
C3:C10 & A3:A10,
_m
),
""
),
VSTACK(
HSTACK(
"Process",
_sp
),
HSTACK(
_up,
_v2
)
)
)Excel solution 8 for Add Task Duration to Start, proposed by LEONARD OCHEA 🇷🇴:
=LET(
F,
LAMBDA(
x,
MAP(
B3:B10,
LAMBDA(
c,
REDUCE(
c,
A14:A17,
LAMBDA(
a,
b,
SUBSTITUTE(
a,
b,
OFFSET(
b,
,
x
)
)
)
)
)
)
),
d,
MAP(
F(
2
),
LAMBDA(
x,
MAX(
--TEXTSPLIT(
x,
","
)
)
)
),
m,
DROP(
TEXTSPLIT(
CONCAT(
SUBSTITUTE(
F(
1
)&", ",
", ",
","&A3:A10&","&C3:C10&","&d&"|"
)
),
",",
"|"
),
-1
),
I,
LAMBDA(
x,
INDEX(
m,
,
x
)
),
PIVOTBY(
I(
2
),
I(
1
),
I(
3
)+I(
4
),
SUM,
,
0,
,
0
)
)Excel solution 9 for Add Task Duration to Start, proposed by Asheesh Pahwa:
=LET(
u,
UNIQUE(
A3:A10
),
sr,
SORT(
TOROW(
B14:B17
),
,
,
1
),
cn,
u&sr,
r,
REDUCE(
"",
SEQUENCE(
ROWS(
A3:A10
)
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
t,
TEXTSPLIT(
INDEX(
B3:B10,
y,
),
,
", "
),
c,
TEXTAFTER(
t&"-"&INDEX(
A3:A10,
y,
),
"-"
),
m,
MAX(
XLOOKUP(
t,
A14:A17,
C14:C17
)
),
xl,
XLOOKUP(
t,
A14:A17,
B14:B17
),
I,
INDEX(
C3:C10,
y,
),
s,
m+I,
in,
IFNA(
HSTACK(
c&xl,
s
),
s
),
in
)
)
)
),
VSTACK(
HSTACK(
"Process",
sr
),
HSTACK(
u,
XLOOKUP(
cn,
TAKE(
r,
,
1
),
TAKE(
r,
,
-1
),
""
)
)
)
)Excel solution 10 for Add Task Duration to Start, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=VSTACK(HSTACK(
"Process",
TOROW(
SORT(
B14:B17
)
)
),
HSTACK(UNIQUE(
A3:A10
),
LET(o,
BYCOL(
TOROW(
SORT(
B14:B17
)
),
LAMBDA(
a,
XLOOKUP(
a,
B14:B17,
A14:A17
)
)
),
LET(p,
DROP(
TEXTSPLIT(
TEXTJOIN(
,
,
MAP(
o,
LAMBDA(
x,
TEXTJOIN(
";",
,
FILTER(
B3:B10,
ISNUMBER(
SEARCH(
x,
B3:B10
)
)
)
)&";"
)
)
),
,
";"
),
-1
),
IFERROR(VALUE(DROP(TEXTSPLIT(TEXTJOIN(,
,
MAP(UNIQUE(
A3:A10
),
LAMBDA(y,
TEXTJOIN(",",
FALSE,
IFERROR(MAP(o,
LAMBDA(x,
UNIQUE(FILTER(VALUE(
DROP(
TEXTSPLIT(
TEXTJOIN(
,
,
MAP(
o,
LAMBDA(
x,
TEXTJOIN(
",",
,
FILTER(
C3:C10,
ISNUMBER(
SEARCH(
x,
B3:B10
)
)
)
)&","
)
)
),
,
","
),
-1
)
)+MAP(
p,
LAMBDA(
a,
MAX(
FILTER(
C14:C17,
ISNUMBER(
MAP(
A14:A17,
LAMBDA(
x,
SEARCH(
x,
a
)
)
)
)
)
)
)
),
IFERROR(((SEARCH(
x,
p
))*(SEARCH(
y,
DROP(
TEXTSPLIT(
TEXTJOIN(
,
,
MAP(
o,
LAMBDA(
x,
TEXTJOIN(
",",
,
FILTER(
A3:A10,
ISNUMBER(
SEARCH(
x,
B3:B10
)
)
)
)&","
)
)
),
,
","
),
-1
)
)))>0,
"FALSE"))))),
""))&"/"))),
",",
"/"),
-1)),
"")))))Excel solution 11 for Add Task Duration to Start, proposed by Imam Hambali:
=LET(
tsa,
HSTACK(
SEQUENCE(
ROWS(
A3:A10
)
),
A3:C10
),
xy,
DROP(
REDUCE(
"",
CHOOSECOLS(
tsa,
3
),
LAMBDA(
x,
y,
VSTACK(
x,
MAX(
XLOOKUP(
TRIM(
TEXTSPLIT(
TEXTJOIN(
",",
TRUE,
y
),
","
)
),
A14:A17,
C14:C17
)
)
)
)
),
1
),
s,
SUBSTITUTE(
","&CHOOSECOLS(
tsa,
3
),
",",
";"&CHOOSECOLS(
tsa,
1
)&","&CHOOSECOLS(
tsa,
2
)&","&CHOOSECOLS(
tsa,
4
)&","&xy&","
),
ts,
TRIM(
DROP(
TEXTSPLIT(
TEXTJOIN(
"",
TRUE,
s
),
",",
";"
),
1
)
),
PIVOTBY(
CHOOSECOLS(
ts,
2
),
XLOOKUP(
TAKE(
ts,
,
-1
),
A14:A17,
B14:B17
),
CHOOSECOLS(
ts,
3
)+ CHOOSECOLS(
ts,
4
),
MAX,
0,
0,
,
0
)
)Excel solution 12 for Add Task Duration to Start, proposed by Edwin Tisnado:
=LET(
s,
A3:A10,
r,
A14:A17,
c,
B14:B17,
l,
UNIQUE(
s
),
IFNA(
HSTACK(
VSTACK(
"Process",
l
),
REDUCE(
G1:J1,
l,
LAMBDA(
x,
y,
VSTACK(
x,
TOROW(
VLOOKUP(
y&XLOOKUP(
SORT(
c
),
c,
r
),
TEXTSPLIT(
CONCAT(
MAP(
s,
B3:B10,
C3:C10,
LAMBDA(
x,
y,
z,
LET(
a,
TEXTSPLIT(
y,
", "
),
CONCAT(
x&a&";"&z+MAX(
XLOOKUP(
a,
r,
C14:C17,
,
0
)
)&","
)
)
)
)
),
";",
","
),
2,
0
)+0
)
)
)
)
),
""
)
)Solving the challenge of Add Task Duration to Start with Python
Python solution 1 for Add Task Duration to Start, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_209.xlsx"
input1 = pd.read_excel(path, usecols="A:C", skiprows=1, nrows = 8)
input2 = pd.read_excel(path, usecols="A:C", skiprows=12, nrows = 4)
test = pd.read_excel(path, usecols="F:J", nrows = 4)
test.columns = test.columns.str.replace('.1', '')
input1['process_part'] = input1.groupby('Process').cumcount() + 1
input1['Task'] = input1['Task'].str.split(', ')
input1 = input1.explode('Task')
i1 = input1.merge(input2, on='Task', how='left')
i1['max_dur'] = i1.groupby(['Process', 'process_part'])['Duration Days'].transform('max')
i1['end_date'] = pd.to_datetime(i1['Start Date']) + pd.to_timedelta(i1['max_dur'], unit='D')
i1 = i1.pivot_table(index='Process', columns='Owner', values='end_date', aggfunc='first').reset_index()
i1 = i1[['Process', 'Anne', 'Lisa', 'Nathan', 'Robert']]
i1.columns.name = None
print(i1.equals(test)) # True
Solving the challenge of Add Task Duration to Start with R
R solution 1 for Add Task Duration to Start, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = 'Power Query/PQ_Challenge_209.xlsx'
input1 = read_excel(path, range = "A2:C10")
input2 = read_excel(path, range = "A13:C17")
test = read_excel(path, range = "F1:J5") %>%
mutate(across(-1, as.Date))
i1 = input1 %>%
mutate(process_part = row_number(), .by = Process) %>%
separate_rows(Task, sep = ", ") %>%
left_join(input2, by = c("Task")) %>%
mutate(max_dur = max(`Duration Days`, na.rm = T),
end_date = as.Date(`Start Date`) + max_dur,
.by = c(Process, process_part)) %>%
select(Owner, Process, end_date) %>%
pivot_wider(names_from = Owner, values_from = end_date) %>%
select(Process, Anne, Lisa, Nathan, Robert)
identical(i1, test)
# [1] TRUE
&&