Today’s problem is contributed by Ahmad Syawal Ramli Generate the area enclosed in red from problem table.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 604
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Calculate Red Highlighted Area with Power Query
Solution 1 Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Fin",
each DateTime.ToText(Date.StartOfMonth([Finish]), [Format = "MMM-yy"])
),
P = (v, w) =>
[
C = Table.SelectRows(B, each ([Stage] = v)),
D = Table.Sort(C, {{"Finish", 0}}),
E = Table.SelectColumns(D, {"System No", "Fin"}),
F = Table.Combine(
Table.Group(E, {"Fin"}, {"All", each Table.AddIndexColumn(_, "Id", 1)})[All]
),
G = Table.Pivot(
F,
List.Sort(List.Distinct(B[Fin]), {each Date.FromText(_)}),
"Fin",
"System No"
),
H = Table.Sort(G, {"Id", w}),
I = Table.RemoveColumns(H, {"Id"})
][I],
J = P("Construction", 1),
K = P("Pre-Comm", 0),
L = Table.ColumnNames(K),
M = Table.Combine({J, Table.FromRows({L}, L), K}),
N = Table.Skip(Table.DemoteHeaders(M))
in
NSolution 2 Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = (x) => DateTime.ToText(x, [Format = "MMM-yy"]),
C = List.Distinct(List.Transform(List.Sort(A[Finish]), B)),
D = List.Distinct(A[Stage]),
E = List.Zip({Table.Partition(A, "Stage", List.Count(D), each List.PositionOf(D, _)), {1, 0}}),
F = List.Transform(
E,
each [
a = Table.TransformColumns(
Table.Sort(Table.SelectColumns(_{0}, {"System No", "Finish"}), {"Finish", _{1}}),
{"Finish", B}
),
b = Table.Pivot(
a,
C,
"Finish",
"System No",
(x) => List.Sort(x, each List.PositionOf(a[#"System No"], _))
),
c = List.Max(Record.FieldValues(Table.TransformColumns(b, {}, List.Count){0})),
d =
if _{1} = 1 then
Table.TransformColumns(b, {}, each List.Repeat({null}, c - List.Count(_)) & _)
else
b
][d]
),
G = Table.FromColumns(
List.Transform(
List.Zip({Table.ToColumns(Table.Combine(F)), C}),
each List.Combine(List.InsertRange(_{0}, 1, {{_{1}}}))
)
)
in
GSolving the challenge of Calculate Red Highlighted Area with Excel
Solution 1 Bo Rydobon 🇹🇭:
=LET(f,EOMONTH(+E2:E11,-1)+1,d,TOROW(SORT(UNIQUE(f))),
L,LAMBDA(x,DROP(REDUCE(0,d,LAMBDA(a,v,IFNA(HSTACK(a,IFERROR(TAKE(SORT(FILTER(C2:E11,(f=v)*(LEFT(B2:B11)=x)),3),,1),"")),""))),,1)),
c,L("C"),VSTACK(SORTBY(c,-SEQUENCE(ROWS(c))),d,L("P")))Solution 2 John V.:
=LET(f,
E2:E11,
i,
1+f-DAY(
f
),
b,
TOROW(
UNIQUE(
SORT(
i
)
)
),
z,
LAMBDA(s,
o,
DROP(REDUCE(0,
b,
LAMBDA(a,
v,
HSTACK(a,
SORT(REPT(C2:C11,
(CODE(
B2:B11
)=s)*(i=v)),
,
o)))),
,
1)),
c,
VSTACK(
z(
67,
1
),
b,
z(
80,
-1
)
),
FILTER(
c,
BYROW(
c<>"",
OR
)
))Solution 3 Kris Jaganah:
=LET(a,SORT(A2:E11,{2,5},{1,1}),b,EOMONTH(--TAKE(a,,-1),-1)+1,c,INDEX(a,,2),d,SORT(TAKE(a,,1))-XMATCH(c&b,c&b)+1,e,PIVOTBY(HSTACK(c,d),b,TEXT(INDEX(a,,3),"#"),SINGLE,,0,1,0),f,LAMBDA(v,w,SORT(FILTER(e,TAKE(e,,1)=v),2,w)),g,DROP(VSTACK(f(TAKE(c,1),-1),TAKE(e,1),f(TAKE(c,-1),1)),,2),g)Solution 4 Julian Poeltl:
=LET(T,LEFT(B2:B11,1),S,C2:C11,F,E2:E11,M,EOMONTH(--F,-1)+1,U,TOROW(UNIQUE(SORT(M))),Fi,BYCOL(MAP(U,LAMBDA(A,IFERROR(ROWS(FILTER(S,(M=A)*(T="C"))),0))),LAMBDA(A,MAX(1,A))),IFNA(DROP(REDUCE(0,SEQUENCE(COLUMNS(Fi)),LAMBDA(A,B,HSTACK(A,VSTACK(IF(SEQUENCE(@(MAX(Fi)-INDEX(Fi,,B)+1))," "),IFERROR(FILTER(S,(M=INDEX(U,,B))*(T="C")),""),INDEX(U,,B),IFERROR(FILTER(S,(M=INDEX(U,,B))*(T="P")),""))))),1,1),""))Solution 5 Aditya Kumar Darak 🇮🇳:
=LET(
_stage,
B2:B11,
_systemNo,
C2:C11,
_finish,
E2:E11,
_mnth,
TEXT(
_finish,
"mmm-yy"
),
_srt,
SORTBY(
_mnth,
--_mnth
),
_umnth,
TOROW(
UNIQUE(
_srt
)
),
_fun,
LAMBDA(x,
DROP(
REDUCE("",
_umnth,
LAMBDA(a,
b,
HSTACK(a,
FILTER(_systemNo,
(_mnth = b) * (_stage = x),
"")))),
,
1
)
),
_clc1,
_fun(
"Construction"
),
_clc2,
_fun(
"Pre-Comm"
),
_srt2,
SORTBY(
_clc1,
SEQUENCE(
ROWS(
_clc1
)
),
-1
),
_rtrn,
IFNA(
VSTACK(
_srt2,
_umnth,
_clc2
),
""
),
_rtrn
)Solution 6 Timothée BLIOT:
=DROP(REDUCE(0,TOROW(SORT(UNIQUE(DATE(2024,MONTH(E2:E11),1)))),LAMBDA(w,v,LET(F,LAMBDA(n,m,IFERROR(TAKE(SORT(FILTER(HSTACK(C2:C11,E2:E11),(B2:B11=n)*(DATE(2024,MONTH(E2:E11),1)=v)),2,m),,1),"")),B,F(B2,-1),C,F(B7,1),D,IFERROR(SEQUENCE(4)/0,""),IFNA(HSTACK(w,VSTACK(TAKE(D,4-ROWS(B)),B,v,C)),"")))),1,1)Solution 7 Sunny Baggu:
=LET(
_um, TOROW(UNIQUE(MONTH(TOCOL(D2:E11)))),
_s, UNIQUE(B2:B11),
_e1, LAMBDA(s,
IFNA(
DROP(
REDUCE(
"",
_um,
LAMBDA(a, v, HSTACK(a, FILTER(C2:C11, (B2:B11 = s) * (MONTH(E2:E11) = v), "")))
),
,
1
),
""
)
),
LET(
_c1, _e1(TAKE(_s, 1)),
_c2, _e1(TAKE(_s, -1)),
VSTACK(
DROP(
REDUCE(
"",
SEQUENCE(COLUMNS(_c1)),
LAMBDA(a, v, HSTACK(a, SORTBY(INDEX(_c1, , v), SEQUENCE(ROWS(_c1)), -1)))
),
,
1
),
TEXT(DATE(UNIQUE(YEAR(TOCOL(D2:E11))), _um, 1), "mmm-yy"),
_c2
)
)
)Solution 8 LEONARD OCHEA 🇷🇴:
=LET(a,
A2:A11,
b,
B2:B11,
I,
INDEX,
R,
TOROW,
d,
EOMONTH(
+E2:E11,
-1
)+1,
e,
MMULT((a>=R(
a
))*(d=R(
d
))*(b=R(
b
)),
a^0),
p,
PIVOTBY(
HSTACK(
b,
e
),
d,
C2:C11&"",
SINGLE,
,
0,
,
0
),
F,
LAMBDA(
x,
FILTER(
p,
I(
p,
,
1
)=I(
UNIQUE(
b
),
x
)
)
),
DROP(
VSTACK(
SORT(
F(
1
),
2,
-1
),
I(
p,
1,
),
F(
2
)
),
,
2
))Solution 9 Pieter de B.:
=LET(i,
LAMBDA(
i,
INDEX(
SORT(
A2:E11,
5
),
,
i
)
),
u,
UNIQUE(
EOMONTH(
i(
5
),
-1
)+1
),
r,
LAMBDA(v,
DROP(REDUCE(0,
u,
LAMBDA(a,
b,
IFNA(HSTACK(a,
VSTACK(FILTER(i(
3
),
(LEFT(
i(
2
)
)=v)*(EOMONTH(
i(
5
),
-1
)+1=b),
""))),
""))),
,
1)),
LET(
z,
r(
"C"
),
VSTACK(
SORTBY(
z,
-SEQUENCE(
ROWS(
z
)
)
),
TOROW(
TEXT(
u,
"mmm-yy"
)
),
r(
"P"
)
)
))Solution 10 ferhat CK:
=LET(b,
UNIQUE(SORT(--(1&TEXT(
E2:E11,
"mmmm"
)))),
t,
LAMBDA(
i,
LET(
f,
FILTER(
C2:E11,
B2:B11=i
),
IFNA(
DROP(
REDUCE(
0,
b,
LAMBDA(
x,
y,
HSTACK(
x,
IFERROR(
FILTER(
INDEX(
f,
,
1
),
TEXT(
INDEX(
f,
,
3
),
"mm.yy"
)=TEXT(
y,
"mm.yy"
)
),
""
)
)
)
),
,
1
),
""
)
)
),
r,
t(
"Construction"
),
VSTACK(INDEX(
r,
SEQUENCE(
ROWS(
r
),
,
ROWS(
r
),
-1
),
SEQUENCE(
,
COLUMNS(
r
)
)
),
TEXT(TOROW(UNIQUE(SORT(--(1&TEXT(
E2:E11,
"mmmm"
))))),
"mmm.yy"),
t(
"Pre-Comm"
)))Solution 11 Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=HSTACK(VSTACK(" ",VSTACK(TAKE(UNIQUE(B2:B11),1),"Finish Date",TAKE(UNIQUE(B2:B11),-1))," "),LET(n,TEXT(E2:E11,"yy-Mmm"),m,TOROW(UNIQUE(TEXT(SORT(E2:E11,,1),"yy-Mmm"))),VSTACK(DROP(SORT(IFERROR(TRANSPOSE(TEXTSPLIT(TEXTJOIN(" ",FALSE,IFERROR(BYCOL(m,LAMBDA(d,TEXTJOIN(",",,LET(c,LET(z,MAP(n,C2:C11,LAMBDA(x,y,XLOOKUP(d,x,y))),FILTER(z,NOT(ISNA(z)))),IFERROR(FILTER(c,TAKE(UNIQUE(B2:B11),1)=MAP(c,LAMBDA(a,XLOOKUP(a,C2:C11,B2:B11)))),"")))&"/")),"")),",","/",FALSE)),""),,1),,-1),TOROW(UNIQUE(TEXT(SORT(E2:E11,,1),"yy-Mmm"))),DROP(SORT(IFERROR(TRANSPOSE(TEXTSPLIT(TEXTJOIN(" ",FALSE,IFERROR(BYCOL(m,LAMBDA(d,TEXTJOIN(",",,LET(c,LET(z,MAP(n,C2:C11,LAMBDA(x,y,XLOOKUP(d,x,y))),FILTER(z,NOT(ISNA(z)))),IFERROR(FILTER(c,TAKE(UNIQUE(B2:B11),-1)=MAP(c,LAMBDA(a,XLOOKUP(a,C2:C11,B2:B11)))),"")))&"/")),"")),",","/",FALSE)),""),,1),,-1))))Solution 12 Burhan Cesur:
=LET(m,
UNIQUE(
TEXT(
SORT(
DATE(
YEAR(
TOCOL(
D2:E11
)
),
MONTH(
TOCOL(
D2:E11
)
),
1
)
),
"mmm.yy"
)
),
w,
LAMBDA(x,
z,
IFERROR(CHOOSECOLS(SORT(FILTER(HSTACK(
C2:C11,
E2:E11
),
(B2:B11=z)*(MONTH(
E2:E11
)=MONTH(
x
))),
2,
-1),
1),
"")),
d,
LAMBDA(
x,
z,
MAX(
MAP(
m,
LAMBDA(
x,
COUNTA(
w(
x,
z
)
)
)
)
)
),
DROP(
IFNA(
REDUCE(
"",
m,
LAMBDA(
s,
c,
HSTACK(
s,
VSTACK(
IF(
COUNTA(
w(
c,
B2
)
)<d( c,="" b2="" ),="" sort(="" expand(="" ""&w(="" d(="" m,="" ,="" ""="" )="" w(="" b7="" 1="" ))<="" code=""></d(>