Convert the problem table into result table. Schedule Performance On Time: Actual To Date = Plan To Date Overrun: Actual To Date > Plan To Date Underrun: Actual To Date < Plan To Date Cost Performance: At Cost: Actual Networkdays = Plan Networkdays Overrun: Actual Networkdays > Plan Networkdays Underrun: Actual Networkdays < Plan Networkdays
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 192
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Schedule and Cost Performance with Power Query
Power Query solution 1 for Schedule and Cost Performance, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
N = (f, t) =>
List.Count(
List.Select(
List.DateTimes(f, Duration.Days(t - f) + 1, Duration.From(1)),
each Date.DayOfWeek(_, 1) < 5
)
),
S = Table.ExpandRecordColumn(
Table.Group(
Source,
{"Project", "Phase"},
{
{
"A",
each
let
d = List.RemoveNulls([From Date] & [To Date])
in
[
SP = {"Underrun", "On Time", "Overrun"}{Number.Sign(Number.From(d{3} - d{2})) + 1},
CP = {"Underrun", "At Cost", "Overrun"}{
Number.Sign(N(d{1}, d{3}) - N(d{0}, d{2})) + 1
}
]
}
},
0,
(b, n) => Byte.From(n[Phase] <> null)
),
"A",
{"SP", "CP"},
{"Schedule Performance", "Cost Performance"}
)
in
SPower Query solution 2 for Schedule and Cost Performance, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Duplicated Column" = Table.DuplicateColumn(Source, "Project", "Project1"),
Fill = Table.FillDown(#"Duplicated Column",{"Project1", "Phase"}),
Filter = Table.SelectRows(Fill, each ([Column1] <> null)),
Idx = Table.AddIndexColumn(Filter, "Index", 1 ,1/2),
Col = Table.TransformColumns( Idx ,{"Index",each Number.IntegerDivide(_,1)}),
Perf = Table.AddColumn(Col, "Schedule Performance", each let
a =Number.From( Col[To Date] {List.PositionOf(Col[Index],[Index])}),
b = Number.From([To Date]),
c = if [Column1] = "Actual" then a - b else null,
d = try if c = 0 then "On Time" else if c < 0 then "Overrun" else "Underrun" otherwise null in d),
Cost = Table.AddColumn(Perf, "Cost", each let x = Number.From([To Date]) - Number.From( [From Date]) in if [Column1] ="Plan" then x*-1 else x),
Cost1 = Table.AddColumn(Cost, "Cost Performance", each let
e = List.Sum( Table.SelectRows(Cost,(x)=> x [Project1] = [Project1] and x[Phase] =[Phase])[Cost]) ,
f = if e = 0 then "At Cost" else if e > 0 then "Overrun" else "Underrun" in f),
FillUp = Table.FillUp(Cost1,{"Schedule Performance"}),
Power Query solution 3 for Schedule and Cost Performance, proposed by Kris Jaganah:
Filter1 = Table.SelectRows(FillUp, each ([Column1] = "Plan")),
Keep = Table.SelectColumns(Filter1,{"Project", "Phase", "Schedule Performance", "Cost Performance"})
in
KeepPower Query solution 4 for Schedule and Cost Performance, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
NoNulls = Table.SelectRows(Source, each List.Distinct(Record.ToList(_)) <> {null}),
Sol = Table.Combine(
Table.Group(
NoNulls,
{"Project"},
{
"A",
each
let
a = _,
c = Table.Group(
a,
{"Project", "Phase"},
{
{
"Schedule",
each
let
d = [To Date],
e =
if d{1} = d{0} then
"On Time"
else if d{1} > d{0} then
"Overrun"
else
"Underrun"
in
e
},
{
"Cost Performance",
each
let
f = List.Transform([To Date], each Number.From(Date.From(_))),
g = List.Transform([From Date], each Number.From(Date.From(_))),
h = List.Count(
List.RemoveMatchingItems(
List.Transform({g{1} .. f{1}}, each Date.DayOfWeek(Date.From(_))),
{0, 6}
)
),
i = List.Count(
List.RemoveMatchingItems(
List.Transform({g{0} .. f{0}}, each Date.DayOfWeek(Date.From(_))),
{0, 6}
)
),
j = if h = i then "At Cost" else if h > i then "Overrun" else "Underrun"
in
j
}
},
0,
(x, y) => Number.From(y[Phase] <> null)
)
in
c
},
0,
(s, t) => Number.From(t[Project] <> null)
)[A]
)
in
SolPower Query solution 5 for Schedule and Cost Performance, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
gp = Table.Group(
Fonte,
{"Project", "Phase"},
{
{
"Schedule Performance",
each
let
a = Table.SelectRows(_, each [Column1] <> null),
b = Table.AddColumn(
a,
"Schedule Performance",
each
if a[To Date]{1} = a[To Date]{0} then
"On Time"
else if a[To Date]{1} > a[To Date]{0} then
"Overrun"
else
"Underrun"
)
in
b[Schedule Performance]{0}
},
{
"Cost Performance",
each
let
a = Table.SelectRows(_, each [Column1] <> null),
b = Table.AddColumn(
a,
"Cost Performance",
each
if [Column1] = "Plan" then
List.Count(
List.RemoveItems(
List.Transform(
{Number.From(a[From Date]{0}) .. Number.From(a[To Date]{0})},
each Date.DayOfWeek(Date.From(_))
),
{0, 6}
)
)
else
List.Count(
List.RemoveItems(
List.Transform(
{Number.From(a[From Date]{1}) .. Number.From(a[To Date]{1})},
each Date.DayOfWeek(Date.From(_))
),
{0, 6}
)
)
),
c =
if b[Cost Performance]{1} = b[Cost Performance]{0} then
"At Cost"
else if b[Cost Performance]{1} > b[Cost Performance]{0} then
"Overrun"
else
"Underrun"
in
c
}
},
0,
(a, b) => Number.From(b[Phase] <> null)
)
in
gpPower Query solution 6 for Schedule and Cost Performance, proposed by Eric Laforce:
let
fxCompare = (v1, v2, Status as list) => Status{Number.Sign(Number.From(v1)-Number.From(v2))+1},
Source = Excel.CurrentWorkbook(){[Name="tData192"]}[Content],
FilterNull = Table.SelectRows(Source, each ([Column1] <> null)),
ChangeType = Table.TransformColumnTypes(FilterNull,{{"From Date", type date}, {"To Date", type date}}),
Add_NWD = Table.AddColumn(ChangeType, "Days", each let
_Dates = List.Dates([From Date], Duration.Days([To Date]-[From Date])+1, hashtag#duration(1,0,0,0))
in List.Count(List.Select(_Dates, each Date.DayOfWeek(_, Day.Monday)<5)) ),
FillDown = Table.FillDown(Add_NWD,{"Project", "Phase"}),
Group = Table.Group(FillDown, {"Project", "Phase"}, {"G", each let
iPosActual = List.PositionOf(_[Column1], "Actual"),
_A = _{iPosActual}, _P = _{Number.Mod(1+iPosActual,2)}
in [SP=fxCompare(_A[To Date], _P[To Date], {"Underrun","On Time","Overrun"}),
CP=fxCompare(_A[Days], _P[Days], {"Underrun","At Cost","Overrun"})] }),
Expand = Table.ExpandRecordColumn(Group, "G", {"SP","CP"},{"Schedule Performance", "Cost Performance"})
in
Expand
Power Query solution 7 for Schedule and Cost Performance, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
A = Table.SelectRows(S, each ([From Date] <> null)),
B = Table.FillDown(A,{"Project","Phase"}),
C = Table.SelectRows(B, each ([Column1] = "Actual")),
C1 = Table.AddIndexColumn(C, "Index", 1, 1, Int64.Type),
D = Table.SelectRows(B, each ([Column1] = "Plan")),
D1 = Table.AddIndexColumn(D, "Index", 1, 1, Int64.Type),
E = Table.NestedJoin(D1,{"Project","Phase"},C1,{"Project","Phase"},"Actual"),
F = Table.ExpandTableColumn(E, "Actual", {"Column1", "From Date", "To Date"}, {"Actual.Column1", "Actual.From Date", "Actual.To Date"}),
G = Table.TransformColumnTypes(F,{{"Project", type text}, {"Phase", type text}, {"Column1", type text}, {"From Date", type date}, {"To Date", type date}, {"Index", Int64.Type}, {"Actual.Column1", type text}, {"Actual.From Date", type date}, {"Actual.To Date", type date}}),
H = Table.AddColumn(G, "Schedule Performance", each if [Actual.To Date]=[To Date] then "OnTime" else if [Actual.To Date]>[To Date] then "Overrun" else "Underrun"),
Power Query solution 8 for Schedule and Cost Performance, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
Part2:
K = Table.AddColumn(J, "Cost Performance", each if [NDayAct] = [NDayPlan] then "AtCost" else if [NDayAct] > [NDayPlan] then "Overrun" else "Underrun"),
L = Table.SelectColumns(K,{"Project", "Phase", "Schedule Performance", "Cost Performance"})
in
L
Power Query solution 9 for Schedule and Cost Performance, proposed by Peter Tholstrup:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
remove_empty = Table.SelectRows(Source, each [Column1] <> null),
diff = (l) => Number.From(l{1} - l{0}),
val = (l, a, b, c) => if diff(l) > 0 then a else if diff(l) = 0 then b else c,
networkdays = (from, to) =>
[
dates = {Number.From(from) .. Number.From(to)},
day_of_week = List.Transform(dates, each Date.DayOfWeek(Date.From(_))),
result = List.Count(List.RemoveMatchingItems(day_of_week, {0, 6}))
][result],
sp = each val([To Date], "Overrun", "On Time", "Underrun"),
cp = each [
days = List.Transform(List.Zip({[From Date], [To Date]}), each networkdays(_{0}, _{1})),
result = val(days, "Overrun", "At Cost", "Underrun")
][result],
agg_cols = {{"Schedule Performance", sp}, {"Cost Performance", cp}},
comp = (a, b) => List.NonNullCount({b[Project], b[Phase]}),
group = Table.Group(remove_empty, {"Project", "Phase"}, agg_cols, GroupKind.Local, comp)
in
groupPower Query solution 10 for Schedule and Cost Performance, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
NetworkDays = (startDate, endDate) as number =>
let
DatesList = List.Dates(Date.From(startDate), Duration.Days(Duration.From(Date.From(endDate) - Date.From(startDate))) + 1, hashtag#duration(1, 0, 0, 0)),
WeekDaysList = List.Select(DatesList, each Date.DayOfWeek(_, Day.Saturday) <> 0 and Date.DayOfWeek(_, Day.Sunday) <> 0),
WorkingDaysCount = List.Count(WeekDaysList)
in
WorkingDaysCount,
Result = Table.SelectColumns(Table.Group(
Table.FillDown(Table.DuplicateColumn(Source, "Project", "Project2"), {"Project2", "Phase"}),
{"Project2", "Phase"},
{
{"Schedule Performance", each
if _{1}[To Date] = _{0}[To Date] then "On Time"
else if _{1}[To Date] > _{0}[To Date] then "Overrun"
else "Underrun"
},
{"Cost Performance", each
if NetworkDays(_{1}[From Date], _{1}[To Date]) = NetworkDays(_{0}[From Date], _{0}[To Date]) then "At Cost"
else if NetworkDays(_{1}[From Date], _{1}[To Date]) > NetworkDays(_{0}[From Date], _{0}[To Date]) then "Overrun"
else "Underrun"
},{"Project", each _{0}[Project]}
}
),{"Project", "Phase", "Schedule Performance", "Cost Performance"})
in
Result
Power Query solution 11 for Schedule and Cost Performance, proposed by Luke Jarych:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
RemoveNullRows = Table.SelectRows(Source, each List.NonNullCount(Record.FieldValues(_)) > 0),
FillDown = Table.FillDown(RemoveNullRows, {"Project", "Phase"}),
Grouped = Table.Group(FillDown, {"Project", "Phase"}, {{"Count", each
let t = _,
c1 = Table.AddColumn(t, "NetworkDays", each
let
startDate = _[From Date],
endDate = _[To Date],
DatesList = List.Dates(Date.From(startDate), Duration.Days(Duration.From(Date.From(endDate) - Date.From(startDate))) + 1, hashtag#duration(1, 0, 0, 0)),
WeekDaysList = List.Select(DatesList, each Date.DayOfWeek(_, Day.Saturday) <> 0 and Date.DayOfWeek(_, Day.Sunday) <> 0),
WorkingDaysCount = List.Count(WeekDaysList)
in
WorkingDaysCount),
c2 = Table.AddColumn(c1, "Shedule Performance", each
let a = if t{1}[To Date] = t{0}[To Date] then "On Time"
else if t{1}[To Date] > t{0}[To Date] then "Overrun"
else "Underrun"
in a),
Power Query solution 12 for Schedule and Cost Performance, proposed by Luke Jarych:
let a = if c2{1}[NetworkDays] = c2{0}[NetworkDays] then "At Cost"
else if c2{1}[NetworkDays] > c2{0}[NetworkDays] then "Overrun"
else "Underrun"
in a)
in c3
}}),
Grouped2 = Table.ExpandTableColumn(Table.RemoveColumns(Table.Group(Grouped, {"Project"}, {{"GRP", each Table.AddIndexColumn(_,"Row",1,1)}}), {"Project"}), "GRP", {"Project", "Phase", "Count", "Row"}),
ReplaceValues = Table.ReplaceValue(Grouped2,each [Project],each if [Row] = 1 then [Project] else null,Replacer.ReplaceValue,{"Project"})[[Project], [Phase], [Count]],
Result = Table.Distinct(Table.ExpandTableColumn(ReplaceValues, "Count", {"Shedule Performance", "Cost Performance"}))
in
Result
Power Query solution 13 for Schedule and Cost Performance, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
RemovedBlankRows = Table.SelectRows(
Source,
each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
),
NWD = (fd, td) =>
let
ld = List.Transform({Number.From(fd) .. Number.From(td)}, Date.From),
wd = List.Select(ld, each Date.DayOfWeek(_, Day.Monday) < 5)
in
List.Count(wd),
tip = Table.TransformColumnTypes(
RemovedBlankRows,
{{"From Date", type date}, {"To Date", type date}}
),
group = Table.Group(
tip,
{"Project", "Phase"},
{
{
"all",
each
let
a = Table.AddColumn([[From Date], [To Date]], "NWD", each NWD([From Date], [To Date])),
fct = (l) =>
if l{0} = l{1} then "On Time" else if l{0} > l{1} then "Underrun" else "Overrun",
sp = fct([To Date]),
cp = if fct(a[NWD]) = "On Time" then "At Cost" else fct(a[NWD])
in
Table.FromColumns({{sp}, {cp}}, {"Schedule Performance", "Cost Performance"})
}
},
GroupKind.Local,
(x, y) => Number.From(y[Project] is text) + Number.From(y[Phase] is text)
),
sol = Table.ExpandTableColumn(group, "all", {"Schedule Performance", "Cost Performance"})
in
solPower Query solution 14 for Schedule and Cost Performance, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
RBR = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValu&es(_), {"", null}))),
Grp = Table.Group(RBR, {"Project", "Phase"},{{"R", each [p=_{[Column1 = "Plan"]},a=_{[Column1 = "Actual"]},SP=let x=Duration.Days(a[To Date] - p[To Date]) in if x = 0 then "On Time" else if x > 0 then "Overrun" else "Underrun",CP= let x=List.Count(List.Select(List.Dates(Date.From(a[From Date]), Duration.Days(a[To Date] - a[From Date])+1,hashtag#duration(1,0,0,0)),each Date.DayOfWeek(_)<5)) - List.Count(List.Select(List.Dates(Date.From(p[From Date]), Duration.Days(p[To Date] - p[From Date])+1,hashtag#duration(1,0,0,0)),each Date.DayOfWeek(_)<5)) in if x = 0 then "At Cost" else if x > 0 then "Overrun" else "Underrun"], type record}},GroupKind.Local, (x,y)=> Number.From(y[Phase] <> null)),
Expnd = Table.ExpandRecordColumn(Grp, "R", {"SP", "CP"}, {"Schedule Performance", "Cost Performance"}),
CT = Table.TransformColumnTypes(Expnd,{{"Project", type text}, {"Phase", type text}, {"Schedule Performance", type text}, {"Cost Performance", type text}})
in
CT
Power Query solution 15 for Schedule and Cost Performance, proposed by Arnaud Duvernois:
let
Source = Excel.CurrentWorkbook(){[Name = "Tableau2"]}[Content],
Filter = Table.SelectRows(Source, each ([Colonne1] <> null)),
Transform = Table.TransformColumns(
Filter,
{
"From Date",
each
let
vNumber = Date.DayOfWeek(Date.From(_), 0)
in
if vNumber > 5 then Date.AddDays(_, 7 - vNumber) else _
}
),
Group = Table.Group(
Transform,
{"Project", "Phase"},
{
{
"Schedule Performance",
each
if _[To Date]{0} = _[To Date]{1} then
"On time"
else if _[To Date]{0} > _[To Date]{1} then
"Underrun"
else
"Overrun"
},
{
"Cost Performance",
each
let
vPlan = Number.From(_[To Date]{0}) - Number.From(_[From Date]{0}),
vActual = Number.From(_[To Date]{1}) - Number.From(_[From Date]{1})
in
if vPlan = vActual then "At cost" else if vPlan > vActual then "Underrun" else "Overrun"
}
},
GroupKind.Local,
(s, c) => Number.From(c[Project] <> null or c[Phase] <> null)
)
in
GroupSolving the challenge of Schedule and Cost Performance with Excel
Excel solution 1 for Schedule and Cost Performance, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
A2:E14,
REDUCE(
G1:J1,
FILTER(
SEQUENCE(
ROWS(
z
)
),
INDEX(
z,
,
2
)>0
),
LAMBDA(
a,
n,
LET(
t,
INDEX(
z,
n+{0,
1},
5
),
L,
LAMBDA(
x,
CHOOSE(
2+SIGN(
SUM(
x*{1,
-1}
)
),
"Overrun",
"On Time",
"Underrun"
)
),
VSTACK(
a,
HSTACK(
INDEX(
z,
n,
{1,
2}
)&"",
L(
t
),
SUBSTITUTE(
L(
NETWORKDAYS(
INDEX(
z,
n+{0,
1},
4
),
t
)
),
"On Time",
"At Cost"
)
)
)
)
)
)
)Excel solution 2 for Schedule and Cost Performance, proposed by محمد حلمي:
=LET(
r,
LAMBDA(
u,
LET(
d,
D2:D14,
e,
DROP(
d,
1
),
x,
E2:E14,
v,
DROP(
x,
1
),
a,
IF(
u,
v,
NETWORKDAYS(
+e,
+v
)
),
p,
IF(
u,
x,
NETWORKDAYS(
+d,
+x
)
),
IFS(
a=p,
IF(
u,
"On Time",
"At Cost"
),
a>p,
"Overrun",
1,
"Underrun"
)
)
),
FILTER(
HSTACK(
A2:B14&"",
r(
1
),
r(
0
)
),
B2:B14>0
)
)Excel solution 3 for Schedule and Cost Performance, proposed by 🇰🇷 Taeyong Shin:
=LET(
e,
E2:E14,
f,
LAMBDA(
opt,
LAMBDA(
x,
SWITCH(
SIGN(
SUM(
x * {-1;1}
)
),
1,
"Overrun",
0,
IF(
opt,
"On Time",
"At Cost"
),
"Underrun"
)
)
),
t,
TRANSPOSE(
SCAN(
,
TRANSPOSE(
A2:B14
),
LAMBDA(
a,
v,
IF(
v > 0,
v,
a
)
)
)
),
r,
BYROW(
t,
CONCAT
),
DROP(
GROUPBY(
HSTACK(
XMATCH(
r,
UNIQUE(
r
)
),
t
),
HSTACK(
e,
NETWORKDAYS(
+D2:D14,
+e
)
),
HSTACK(
f(
1
),
f(
0
)
),
,
0,
,
e
),
1,
1
)
)Excel solution 4 for Schedule and Cost Performance, proposed by Julian Poeltl:
=LET(
T,
A2:E14,
F,
FILTER(
T,
CHOOSECOLS(
T,
2
)<>""
),
PP,
TAKE(
F,
,
2
),
X,
XMATCH(
DROP(
PP,
,
1
)&TAKE(
F,
,
-1
),
CHOOSECOLS(
T,
2
)&TAKE(
T,
,
-1
)
),
FA,
INDEX(
T,
X+1,
4
),
TA,
INDEX(
T,
X+1,
5
),
FP,
CHOOSECOLS(
F,
4
),
TP,
TAKE(
F,
,
-1
),
SP,
IFS(
TA=TP,
"On Time",
TA>TP,
"Overrun",
TAAD,
"Underrun"
),
VSTACK(
HSTACK(
"Project",
"Phase",
"Shedule Performance",
"Cost Performance"
),
HSTACK(
IF(
PP<>"",
PP,
""
),
SP,
CP
)
)
) Excel solution 5 for Schedule and Cost Performance, proposed by Oscar Mendez Roca Farell:
=LET(
t,
E2:E14,
f,
D2:D14,
W,
LAMBDA(
i,
CHOOSECOLS(
WRAPROWS(
TOCOL(
IFS(
D2:E14,
HSTACK(
DAY(
t
),
NETWORKDAYS(
+f,
+t
)
)
),
2
),
4
),
i
)
),
s,
SIGN(
HSTACK(
W(
3
)-W(
1
),
W(
4
)-W(
2
)
)
),
p,
IFS(
B2:B14>"",
A2:B14&""
),
VSTACK(
G1:J1,
HSTACK(
FILTER(
p,
1-ISNA(
TAKE(
p,
,
1
)
)
),
IFNA(
IFS(
s=1,
"Over",
s=-1,
"Under"
)&"run",
{"On Time",
"At Cost"}
)
)
)
)Excel solution 6 for Schedule and Cost Performance, proposed by LEONARD OCHEA 🇷🇴:
=LET(F,
TAKE,
G,
TRANSPOSE,
i,
G(
SCAN(
,
G(
UNIQUE(
A2:E14,
,
1
)
),
LAMBDA(
a,
b,
IF(
b=0,
a,
b
)
)
)
),
c,
INDEX(
i,
,
4
),
d,
F(
i,
,
-1
),
e,
GROUPBY(
F(
i,
,
2
),
HSTACK(
d,
NETWORKDAYS(
c,
d
)
),
LAMBDA(
x,
SUM(
x*{-1;1}
)
),
,
0
),
m,
F(
e,
,
-2
),
s,
2+SIGN(
m
),
VSTACK(G1:J1,
HSTACK(F(
e,
,
2
),
CHOOSE(IF({0,
1}*(s=2),
4,
s),
"Underrun",
"On Time",
"Overrun",
"At Cost"))))Excel solution 7 for Schedule and Cost Performance, proposed by Md. Zohurul Islam:
=LET(
u,
SCAN(
,
A2:A14,
LAMBDA(
x,
y,
IF(
y="",
x,
y
)
)
),
v,
SCAN(
,
B2:B14,
LAMBDA(
x,
y,
IF(
y="",
x,
y
)
)
),
w,
C2:C14,
z,
D2:E14,
dt,
FILTER(
HSTACK(
u,
v,
w,
z
),
w<>""
),
hdr,
HSTACK(
A1,
B1,
"Schedule Performance",
"Cost Performance"
),
F,
LAMBDA(
w,
DROP(
REDUCE(
"",
UNIQUE(
TAKE(
w,
,
1
)
),
LAMBDA(
x,
y,
LET(
a,
FILTER(
ABS(
TAKE(
w,
,
-1
)
),
TAKE(
w,
,
1
)=y
),
b,
TAKE(
a,
-1
),
c,
TAKE(
a,
1
),
d,
IF(
b=c,
"On Time",
IF(
b>c,
"Overrun",
"Underrun"
)
),
e,
VSTACK(
x,
HSTACK(
y,
d
)
),
e
)
)
),
1
)
),
FF,
LAMBDA(
w,
DROP(
REDUCE(
"",
UNIQUE(
TAKE(
w,
,
1
)
),
LAMBDA(
x,
y,
LET(
a,
FILTER(
ABS(
TAKE(
w,
,
-2
)
),
TAKE(
w,
,
1
)=y
),
d,
MAP(
TAKE(
a,
,
1
),
TAKE(
a,
,
-1
),
LAMBDA(
x,
y,
NETWORKDAYS(
x,
y
)
)
),
bb,
TAKE(
d,
-1
),
cc,
TAKE(
d,
1
),
e,
IF(
bb=cc,
"At Cost",
IF(
bb>cc,
"Overrun",
"Underrun"
)
),
f,
VSTACK(
x,
e
),
f
)
)
),
1
)
),
zz,
IFNA(
REDUCE(
hdr,
UNIQUE(
TAKE(
dt,
,
1
)
),
LAMBDA(
x,
y,
LET(
ss,
FILTER(
DROP(
dt,
,
1
),
TAKE(
dt,
,
1
)=y
),
tt,
F(
ss
),
uu,
FF(
ss
),
VSTACK(
x,
HSTACK(
y,
tt,
uu
)
)
)
)
),
""
),
zz
)Excel solution 8 for Schedule and Cost Performance, proposed by Hamidi Hamid:
=LET(
w,
HSTACK(
A2:B14,
IF(
C2:C14="Plan",
LET(
q,
MAP(
D2:D14,
D3:D15,
LAMBDA(
a,
b,
IF(
a=b,
0,
NETWORKDAYS.INTL(
a,
b,
1
)
)
)
),
LET(
s,
MAP(
E2:E14,
E3:E15,
LAMBDA(
a,
b,
IF(
a=b,
0,
NETWORKDAYS.INTL(
a,
b,
1
)
)
)
),
HSTACK(
IF(
s>0,
"Overrun",
IF(
s=0,
"On Time",
"Underrun"
)
),
IF(
q-s=0,
"At cost",
IF(
q-s<0,
"Overrun",
"Underrun"
)
)
)
)
),
""
)
),
LET(
t,
SUBSTITUTE(
FILTER(
w,
TAKE(
w,
,
-1
)<>"",
""
),
0,
""
),
t
)
)Solving the challenge of Schedule and Cost Performance with Python
_x000D_Python solution 1 for Schedule and Cost Performance, proposed by Luke Jarych:
import pandas as pd
import xlwings as xw
import numpy as np
wb = xw.Book(r'PQ_Challenge_192-Fulfillfing grouped table.xlsx')
sh = wb.sheets['Solution']
table = sh.tables['Table2']
rng = sh.range(table.range.address)
df = rng.options(pd.DataFrame, header = True, index=False, numbers=int).value
def is_business_day(date):
return bool(len(pd.bdate_range(date, date)))
def assign_shedule(row1, row2):
if row1['To Date'] == row2['To Date']:
return 'On Time'
elif row1['To Date'] < row2['To Date']:
return 'Overrun'
else:
return 'Underrun'
def assing_cost(row1, row2):
toDate1 = pd.to_datetime(row1['To Date']).date()
toDate2 = pd.to_datetime(row2['To Date']).date()
if is_business_day(toDate1):
if is_business_day(toDate2):
return 'At Cost'
return 'Overrun'
else:
return 'Underrun'
Python solution 2 for Schedule and Cost Performance, proposed by Luke Jarych:
df = df.dropna(how='all')
df[['Project', 'Phase']] = df[['Project', 'Phase']].fillna(method='ffill')
print(df)
grouped_df = df.groupby(['Project', 'Phase'], sort=False)
new_df = []
for name, group in grouped_df:
for i in range(len(group) - 1):
row1 = group.iloc[i]
row2 = group.iloc[i + 1]
status_shedule = assign_shedule(row1, row2)
group['Shedule Performance'] = status_shedule
status_cost = assing_cost(row1, row2)
group['Cost Performance'] = status_cost
if i == 1:
group.at[row2.name, 'Project'] = 'NewValue'
group.iat[group.columns.get_loc('Phase')] = None
group = group.drop(columns=['Column1', 'From Date', 'To Date']).drop_duplicates()
new_df.append(group)
df = pd.concat(new_df)
df
mask = df[['Project', 'Phase']].ne(df[['Project', 'Phase']].shift())
mask
df[['Project', 'Phase']] = df[['Project', 'Phase']].where(mask)
Solving the challenge of Schedule and Cost Performance with Python in Excel
_x000D_Python in Excel solution 1 for Schedule and Cost Performance, proposed by Abdallah Ally:
import pandas as pd
def schedule(df):
values = []
for i in df.index:
if pd.notnull(df.iat[i, 1]):
if df.iat[i + 1, 4] == df.iat[i, 4]: values.append('On Time')
elif df.iat[i + 1, 4] > df.iat[i, 4]: values.append('Overrun')
else: values.append('Underrun')
else: values.append('')
return values
def cost(df):
values = []
for i in df.index:
if pd.notnull(df.iat[i, 1]):
actual = len(pd.bdate_range(df.iat[i + 1, 3], df.iat[i + 1, 4]))
plan = len(pd.bdate_range(df.iat[i, 3], df.iat[i, 4]))
if actual == plan: values.append('At Cost')
elif actual > plan: values.append('Overrun')
else: values.append('Underrun')
else: values.append('')
return values
file_path = 'PQ_Challenge_192.xlsx'
df = pd.read_excel(file_path, usecols='A:E')
# Perform data wrangling
df[['Schedule Performance', 'Cost Performance']] = list(zip(schedule(df), cost(df)))
df = df.dropna(subset='Phase', ignore_index=True).iloc[:, [0, 1, 5, 6]].fillna('')
df