This challenge is contributed by Abdelrahman Omer, MBA, PMP Find the critical path and its duration for this project. Critical path is that path which has the longest duration. Ex. There are 4 paths for this Project :- i.e. Start-Task1-Task3-Task7-End & duration = 19, Start-Task2-Task4-End & duration = 17, Start-Task2-Task5-Task8-End & duration = 23, Start-Task2-Task6-End & duration =22 The longest path is 3rd path which is the Critical Path.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 656
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Project Critical Path with Power Query
Power Query solution 1 for Find Project Critical Path, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rows = Table.ToRows(Source),
Recs = List.Accumulate(
List.RemoveLastN(List.Skip(Rows)),
Record.AddField([], Rows{0}{0}, [Critical Path = Rows{0}{0}, Duration = Rows{0}{2}]),
Fun
),
Fun = (a, v) =>
[
A = Record.Field(a, v{1})[Critical Path]
& "-"
& v{0}
& (if Text.Contains(List.Last(Rows){1}, v{0}) then "-" & List.Last(Rows){0} else ""),
B = Record.Field(a, v{1})[Duration]
+ v{2}
+ (if Text.Contains(List.Last(Rows){1}, v{0}) then List.Last(Rows){2} else 0),
C = Record.AddField(a, v{0}, [Critical Path = A, Duration = B])
][C],
Res = Table.MaxN(Table.FromRecords(Record.ToList(Recs)), "Duration", 1)
in
ResPower Query solution 2 for Find Project Critical Path, proposed by Peter Krkos:
let
RemovedBottomRows = Table.RemoveLastN(Source, 1),
R =
let
a = Table.ToColumns(RemovedBottomRows)
in
Function.Invoke(Record.FromList, {List.Zip(List.Skip(a)), a{0}}),
F = (tsk) =>
[
L = List.Generate(
() => [
a = Record.FieldOrDefault(R, tsk, {null}),
b = Text.Combine({a{0}, tsk}, "-"),
c = a{1}? ?? 0
],
each [a]{0} <> null,
each [
a = Record.FieldOrDefault(R, [a]{0}, {null}),
b = Text.Combine({a{0}, [b]}, "-"),
c = [c] + (a{1}? ?? 0)
],
each {[b], [c]}
),
L2 =
let
l = List.Last(L)
in
{Text.Combine({l{0}, "End"}, "-"), l{1}}
][L2],
End = Table.FromList(
List.Buffer(List.Difference(RemovedBottomRows[Task], RemovedBottomRows[Predecessor])),
Splitter.SplitByNothing()
),
Ad_Gen = Table.AddColumn(End, "Gen", each F([Column1]), type list),
Result = Table.MaxN(
Table.FromRows(Ad_Gen[Gen], type table [Critical Path = text, Duration = Int64.Type]),
{"Duration"},
1
)
in
ResultPower Query solution 3 for Find Project Critical Path, proposed by Peter Krkos:
let
RemovedBottomRows = Table.RemoveLastN(Source, 1),
R =
let
a = Table.ToColumns(RemovedBottomRows)
in
Function.Invoke(Record.FromList, {List.Zip(List.Skip(a)), a{0}}),
F = (tsk, optional lst) =>
[
l = lst ?? {},
a = Record.FieldOrDefault(R, tsk, null),
b = {Text.Combine({tsk, l{0}?}, "-"), a{1} + (l{1}? ?? 0)},
c = if a{0} = null then {Text.Combine({b{0}, "End"}, "-"), b{1}} else @F(a{0}, b)
][c],
End = Table.FromList(
List.Difference(RemovedBottomRows[Task], RemovedBottomRows[Predecessor]),
(x) => {x}
),
Ad_Fun = Table.AddColumn(End, "Fun", each F([Column1]), type list),
Result = Table.MaxN(
Table.FromRows(Ad_Fun[Fun], type table [Critical Path = text, Duration = Int64.Type]),
{"Duration"},
1
)
in
ResultSolving the challenge of Find Project Critical Path with Excel
Excel solution 1 for Find Project Critical Path, proposed by Bo Rydobon 🇹🇭:
=LET(
t,
A3:A12,
b,
LAMBDA(
b,
a,
LET(
n,
ROWS(
a
),
IF(
n=1,
a&IFERROR(
"-"&FILTER(
A3:A12,
1-ISERR(
FIND(
TEXTAFTER(
"-"&a,
"-",
-1
)&",",
B3:B12&","
)
)
),
""
),
VSTACK(
b(
b,
TAKE(
a,
n/2
)
),
b(
b,
DROP(
a,
n/2
)
)
)
)
)
),
p,
REDUCE(
A3,
SEQUENCE(
4
),
LAMBDA(
a,
_,
b(
b,
a
)
)
),
d,
MAP(
p,
LAMBDA(
f,
SUM(
SUMIFS(
C3:C12,
t,
TEXTSPLIT(
f,
"-"
)
)
)
)
),
TAKE(
SORT(
HSTACK(
p,
d
),
2,
-1
),
1
)
)
=LET(
R,
LAMBDA(
R,
t,
IF(
OR(
t=""
),
IF(
{1,
0},
t,
0
),
LET(
v,
R(
R,
IFNA(
LOOKUP(
t,
A4:B11&""
),
""
)
),
HSTACK(
TAKE(
v,
,
1
)&"-"&t,
IFNA(
LOOKUP(
t,
A4:C11
)+DROP(
v,
,
1
),
)
)
)
)
),
TAKE(
SORT(
R(
R,
TEXTSPLIT(
B12,
,
", "
)&"-End"
),
2,
-1
),
1
)
)Excel solution 2 for Find Project Critical Path, proposed by John V.:
=LET(t,A3:A12,z,LAMBDA(z,r,n,LET(d,"-",i,DROP(REDUCE(0,r,LAMBDA(a,v,VSTACK(a,IFERROR(v&d&TOCOL(IF(FIND(TEXTAFTER(d&v,d,n),B3:B12),t),2),v)))),1),IF(AND(RIGHT(i,3)=A12),i,z(z,i,1+n)))),p,z(z,A3,1),TAKE(SORT(HSTACK(p,MAP(p,LAMBDA(x,SUM(SUMIF(t,TEXTSPLIT(x,"-"),C3:C12))))),2,-1),1))Excel solution 3 for Find Project Critical Path, proposed by Kris Jaganah:
=LET(
m,
A4:A11,
p,
REDUCE(
A3,
SEQUENCE(
10
),
LAMBDA(
t,
u,
LET(
a,
TEXTSPLIT(
t,
,
", "
),
b,
TEXTAFTER(
a,
"-",
-1,
,
,
a
),
ARRAYTOTEXT(
MAP(
a,
b,
LAMBDA(
v,
w,
ARRAYTOTEXT(
BYROW(
IFNA(
HSTACK(
v,
FILTER(
m,
B4:B11=w,
""
)
),
v
),
LAMBDA(
x,
TEXTJOIN(
"-",
,
x
)
)
)
)
)
)
)
)
)
),
q,
TEXTSPLIT(
p,
,
", ",
)&"-End",
r,
MAP(
q,
LAMBDA(
x,
SUM(
XLOOKUP(
TEXTSPLIT(
x,
"-"
),
m,
C4:C11,
0
)
)
)
),
FILTER(
HSTACK(
q,
r
),
r=MAX(
r
)
)
)Excel solution 4 for Find Project Critical Path, proposed by Timothée BLIOT:
=LET(A,SEQUENCE(,8),B,REDUCE(0,A,LAMBDA(w,v,LET(C,FILTER(w,LEN(w)=v),VSTACK(w,TOCOL(IF(ISERR(FIND(A,C)),C&A,1/0),3))))),D,BYROW(HSTACK(IFNA(REGEXEXTRACT(B4:B11,"d"),0),TOCOL(A)),LAMBDA(x,CONCAT(x))),E,FILTER(B,MAP(B,LAMBDA(x,PRODUCT(N(ISNUMBER(XMATCH(MID(x,SEQUENCE(LEN(x)-1),2),D))))))),F,MAP(E,LAMBDA(x,SUM(XLOOKUP(MID(x,SEQUENCE(LEN(x)-1),2),D,C4:C11)))),G,FILTER(MID(E,2,8),MAX(F)=F),HSTACK(TEXTJOIN("-",,"Start","Task"&MID(G,SEQUENCE(@LEN(G)),1),"End"),MAX(F)))Excel solution 5 for Find Project Critical Path, proposed by Hussein SATOUR:
=LET(t,A4:A12,path,REDUCE(",Start",SEQUENCE(4),LAMBDA(x,y,VSTACK(x,REDUCE(,x,LAMBDA(z,v,VSTACK(z,LET(a,TEXTAFTER(v,",",-1),v&","&FILTER(t,IFERROR(FIND(a,B4:B12),0)>0))))))))&",End",len,MAP(path,LAMBDA(w,SUM(XLOOKUP(TEXTSPLIT(w,","),t,C4:C12,0)))),FILTER(HSTACK(path,len),len=MAX(len)))Excel solution 6 for Find Project Critical Path, proposed by Oscar Mendez Roca Farell:
=LET(
d,
A3:C12,
F,
TEXTSPLIT,
t,
F(
B12,
,
", "
),
G,
VLOOKUP,
c,
REDUCE(
t&"-"&A12,
DROP(
XMATCH(
t,
t
),
-1
),
LAMBDA(
i,
x,
G(
F(
i,
"-"
),
d,
2,
)&"-"&i
)
),
m,
MAP(
c,
LAMBDA(
a,
SUM(
G(
F(
a,
,
"-",
1
),
d,
3,
)
)
)
),
TAKE(
SORT(
HSTACK(
c,
m
),
2
),
-1
)
)Excel solution 7 for Find Project Critical Path, proposed by Pieter de B.:
=TAKE(
SORT(
REDUCE(
IFNA(
HSTACK(
TEXTSPLIT(
B12,
,
", "
)&"-End",
0
),
0
),
A3:A12,
LAMBDA(
a,
_,
LET(
t,
TAKE(
a,
,
1
),
c,
TEXTBEFORE(
t,
"-",
1
),
IF(
c="Start",
a,
HSTACK(
XLOOKUP(
c,
A3:A12,
B3:B12
)&"-"&t,
TAKE(
a,
,
-1
)+XLOOKUP(
c,
A3:A12,
C3:C12
)
)
)
)
)
),
2
),
-1
)Excel solution 8 for Find Project Critical Path, proposed by JvdV –:
=LET(
s,
A3:A12,
r,
REGEXTEST,
f,
LAMBDA(
x,
y,
IF(
AND(
r(
y,
"End$"
)
),
TAKE(
SORT(
HSTACK(
y,
MAP(
y,
LAMBDA(
q,
SUM(
r(
q,
s&"b"
)*C3:C12
)
)
)
),
2,
-1
),
1
),
x(
x,
REDUCE(
A12,
y,
LAMBDA(
a,
b,
VSTACK(
a,
IFERROR(
b&"-"&FILTER(
s,
r(
B3:B12,
TEXTAFTER(
b,
"-",
-1,
,
,
b
)&"b"
)
),
b
)
)
)
)
)
)
),
f(
f,
A3
)
)Excel solution 9 for Find Project Critical Path, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(
v,
LET(
m,
BYCOL(
TOROW(
A3:A12
),
LAMBDA(
x,
TEXTJOIN(
",",
,
FILTER(
A3:A12,
ISNUMBER(
SEARCH(
x,
B3:B12
)
)
)
)
)
),
LET(
p,
TOCOL(
FILTER(
TOROW(
A3:A12
),
ISNUMBER(
SEARCH(
"End",
m
)
)
)
),
o,
TOROW(
A3:A12
& ),
LET(
c,
MAP(
p,
LAMBDA(
i,
FILTER(
o,
ISNUMBER(
SEARCH(
i,
m
)
)
)
)
),
HSTACK(
p,
c,
MAP(
c,
LAMBDA(
j,
FILTER(
o,
ISNUMBER(
SEARCH(
j,
m
)
)
)
)
),
MAP(
MAP(
c,
LAMBDA(
j,
FILTER(
o,
ISNUMBER(
SEARCH(
j,
m
)
)
)
)
),
LAMBDA(
c,
FILTER(
o,
ISNUMBER(
SEARCH(
c,
m
)
)
)
)
)
)
)
)
),
HSTACK(
TEXTJOIN(
"-",
,
LET(
t,
TOCOL(
LET(
f,
IFERROR(
LET(
g,
BYROW(
v,
LAMBDA(
t,
SUM(
LET(
w,
MAP(
t,
LAMBDA(
q,
XLOOKUP(
q,
A3:A12,
C3:C12
)
)
),
FILTER(
w,
ISNUMBER(
w
)
)
)
)
)
),
XLOOKUP(
MAX(
g
),
g,
v
)
),
""
),
FILTER(
f,
f<>""
)
)
),
MAP(
SEQUENCE(
COUNTA(
t
)
),
LAMBDA(
p,
XLOOKUP(
p,
COUNTA(
t
)+1-SEQUENCE(
COUNTA(
t
)
),
t
)
)
)
)
)&"-End",
MAX(
BYROW(
v,
LAMBDA(
t,
SUM(
LET(
w,
MAP(
t,
LAMBDA(
q,
XLOOKUP(
q,
A3:A12,
C3:C12
)
)
),
FILTER(
w,
ISNUMBER(
w
)
)
)
)
)
)
)
)
)Excel solution 10 for Find Project Critical Path, proposed by Erdit Qendro:
=LET(
a,
$A$3:$A$12,
b,
$B$3:$B$12,
c,
$C$3:$C$12,
end,
XLOOKUP(
"End",
a,
b
),
PriorEndPoints,
TEXTSPLIT(
end,
,
", "
),
lam,
LAMBDA(
lam,
l,
LET(
n,
COLUMNS(
l
),
lval,
INDEX(
l,
n
),
IF(
lval="Start",
l,
lam(
lam,
HSTACK(
l,
FILTER(
b,
a=lval
)
)
)
)
)
),
p,
LAMBDA(
p,
i,
MAP(
PriorEndPoints,
LAMBDA(
m,
LET(
x,
lam(
lam,
m
),
cl,
SEQUENCE(
,
COLUMNS(
x
)
),
CHOOSE(
i,
TEXTJOIN(
"-",
TRUE,
SORTBY(
x,
-cl
),
"End"
),
SUM(
XLOOKUP(
x,
a,
c,
0
)
)
)
)
)
)
),
XLOOKUP(
99999,
p(
p,
2
),
HSTACK(
p(
p,
1
),
p(
p,
2
)
),
,
-1
)
)Excel solution 11 for Find Project Critical Path, proposed by Luis Couto:
=LET(
S,
TAKE,
V,
VLOOKUP,
k,
A3:C12,
f,
LAMBDA(
f,
t,
r,
d,
LET(
e,
V(
t,
k,
2
),
IF(
e="",
HSTACK(
r,
d
),
f(
f,
e,
e&"-"&r,
d+V(
t,
k,
3
)
)
)
)
),
e,
TEXTSPLIT(
B12,
,
", "
),
REDUCE(
{1,
1},
e,
LAMBDA(
a,
i,
LET(
f,
f(
f,
i,
i&"-"&A12,
0
),
IF(
S(
f,
,
-1
)>S(
a,
,
-1
),
f,
a
)
)
)
)
)Solving the challenge of Find Project Critical Path with Python
Python solution 1 for Find Project Critical Path, proposed by Konrad Gryczan, PhD:
import pandas as pd
input = pd.read_excel("656 Critical Path.xlsx", usecols="A:C", skiprows=1, nrows=10).dropna(subset=['Predecessor '])
input = input.assign(Predecessor=input['Predecessor '].str.split(',')).explode('Predecessor')[['Task', 'Duration', 'Predecessor']]
G = nx.from_pandas_edgelist(input, 'Predecessor', 'Task', ['Duration'], create_using=nx.DiGraph())
longest_path = nx.dag_longest_path(G, weight='Duration')
lp = pd.DataFrame(longest_path, columns=['path']).merge(input, left_on='path', right_on='Task', how='left')[['Task', 'Duration']].dropna()
summary = pd.DataFrame({'Task': ['Start-' + '-'.join(lp['Task']) + '-End'], 'Duration': [lp['Duration'].sum().astype("int64")]})
test = pd.read_excel("656 Critical Path.xlsx", usecols="D:E", skiprows=1, nrows=1)
test.columns = summary.columns
print(summary.equals(test)) # True
Python solution 2 for Find Project Critical Path, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_656 - Critical Path.xlsx'
df = pd.read_excel(io=file_path, usecols='A:C', skiprows=1)
# Perform data manipulation
values = []
for item in df.iat[len(df) - 1, 1].split(', '):
value = ['End', item]
duration = 0
while True:
details = df[df['Task'] == item].iloc[0].tolist()
value += [details[1]]
duration += details[2]
item = details[1]
if details[1] == 'Start':
break
values.append(('-'.join(value[::-1]), duration))
values = [v for v in values if v[1] == max(v[1] for v in values)]
df = pd.DataFrame(data=values, columns=['Critical Path', 'Duration'])
df
Solving the challenge of Find Project Critical Path with Python in Excel
Python in Excel solution 1 for Find Project Critical Path, proposed by Alejandro Campos:
data = xl("A2:C12", headers=True)
tasks = {t: {"duration": d, "predecessors": p.split(", ") if p else []} for t, d, p in zip(data["Task"], data["Duration"], data["Predecessor"])}
def find_paths(tasks, cur="Start", path=["Start"], dur=None):
if cur == "End": return [(path, dur)]
return sum((find_paths(tasks, s, path + [s], dur + tasks[s]["duration"]) for s in tasks if cur in tasks[s]["predecessors"]), [])
critical_path = max(find_paths(tasks, dur=tasks["Start"]["duration"]), key=lambda x: x[1])
compressed_critical_path = ['-'.join(critical_path[0]), critical_path[1]]
Python in Excel solution 2 for Find Project Critical Path, proposed by Aditya Kumar Darak 🇮🇳:
from functools import reduce
df = xl("A2:C12", True)
def MyFun(df):
df["Predecessor"] = df["Predecessor"].apply(
lambda x: [] if pd.isnull(x) else x.split(", ")
)
def Get(task):
preds = df.loc[df["Task"] == task, "Predecessor"].iloc[0]
if not preds:
return [[task]]
return reduce(
lambda acc, pred: acc + [path + [task] for path in Get(pred)], preds, []
)
all_paths = Get("End")
dur = [(path, sum(df.loc[df["Task"].isin(path), "Duration"])) for path in all_paths]
crtc, mx = max(dur, key=lambda x: x[1])
return ", ".join(crtc), mx
result = MyFun(df)
result
Solving the challenge of Find Project Critical Path with R
R solution 1 for Find Project Critical Path, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(igraph)
path = "Excel/656 Critical Path.xlsx"
input = read_excel(path, range = "A2:C12")
test = read_excel(path, range = "D2:E3")
r1 = input %>%
na.omit() %>%
separate_rows(Predecessor, sep = ", ") %>%
relocate(Predecessor, .before = Task)
g = graph_from_data_frame(r1, directed = TRUE)
longest_path = get_diameter(g, weights = E(g)$Duration)
lp = tibble(path = V(g)[longest_path]$name) %>%
left_join(r1, by = c("path" = "Task")) %>%
summarise(Duration = sum(Duration, na.rm = TRUE),
`Critical Path` = paste(path, collapse = "-")) %>%
mutate(`Critical Path` = paste0(`Critical Path`, "-End")) %>%
select(2, 1)
all.equal(lp, test, check.attributes = FALSE)
# [1] TRUE
&&