Generate the result table from problem table. All continents need to be listed for every year. For missing continents, Sales will be 0. Sorting needs to be done on Year followed by Continent.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 187
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Yearly Sales for All Continents with Power Query
Power Query solution 1 for Yearly Sales for All Continents, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
D = each List.Sort(List.Distinct(_(Source))),
T = {"TOTAL", "GRAND TOTAL"},
G = (c, y) =>
List.Sum(
Table.SelectRows(
Source,
(r) => (List.PositionOf(T, c) > - 1 or r[Continent] = c) and (c = T{1} or r[Year] = y)
)[Sales]
),
S = Table.FromRows(
List.TransformMany(
D(each [Year]),
each D(each [Continent]) & {T{0}, null},
(i, _) =>
let
b = Number.From(_ = null)
in
{_, {i, null}{b}, G(_, i) ?? {0, null}{b}}
)
& {
{
T{1},
let
y = Source[Year]
in
Text.From(y{0}) & "-" & Text.From(List.Last(y)),
G(T{1}, T{1})
}
},
Table.ColumnNames(Source)
)
in
SPower Query solution 2 for Yearly Sales for All Continents, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sort = Table.Sort(
Table.Distinct(Table.RemoveColumns(Source, {"Year", "Sales"})),
{"Continent", 0}
),
Year = Table.AddColumn(Sort, "Year", each List.Distinct(Source[Year])),
Expand = Table.ExpandListColumn(Year, "Year"),
Sal = Table.AddColumn(
Expand,
"Sales",
each
let
a = List.Sum(
Table.SelectRows(Source, (x) => x[Year] = [Year] and x[Continent] = [Continent])[Sales]
)
in
if a = null then 0 else a
),
Pivot = Table.Pivot(Sal, List.Distinct(Sal[Continent]), "Continent", "Sales", List.Sum),
Sub = Table.AddColumn(Pivot, "TOTAL", each List.Sum(List.RemoveFirstN(Record.ToList(_), 1))),
Blank = Table.AddColumn(Sub, "to del", each "to del"),
Unpivot = Table.UnpivotOtherColumns(Blank, {"Year"}, "Continent", "Sales"),
GTotal = Table.InsertRows(
Unpivot,
Table.RowCount(Unpivot),
{[Continent = "GRAND TOTAL", Year = "2010-2013", Sales = List.Sum(Source[Sales])]}
),
Formatxtra = Table.TransformRows(GTotal, (_) => if _[Sales] = "to del" then null else _),
Totable = Table.FromList(Formatxtra, Splitter.SplitByNothing()),
Xpand = Table.ExpandRecordColumn(Totable, "Column1", {"Continent", "Year", "Sales"})
in
XpandPower Query solution 3 for Yearly Sales for All Continents, proposed by Rick de Groot:
let
Source = Table1,
Pivot = Table.Pivot(Source, List.Distinct(Source[Year]), "Year", "Sales"),
Repl = Table.ReplaceValue(Pivot, null, 0, Replacer.ReplaceValue, Table.ColumnNames(Pivot)),
Unp = Table.UnpivotOtherColumns(Repl, {"Continent"}, "Year", "Sales"),
Totals = Table.Group(
Unp,
{"Year"},
{{"Sales", each List.Sum([Sales])}, {"Continent", each "Total"}}
),
GrTotal = Table.Group(
Totals,
{},
{
{"Sales", each List.Sum([Sales])},
{"Continent", each "GRAND TOTAL"},
{"Year", each List.Min(_[Year]) & "-" & List.Max(_[Year])}
}
),
Comb = Table.Combine({Unp, Totals}),
Sort = Table.Sort(Comb, {{"Year", 0}, {"Continent", 0}}),
Grp = Table.Group(
Sort,
{"Year"},
{{"Details", each Table.InsertRows(_, 6, {[Continent = null, Year = null, Sales = null]})}}
)[[Details]],
Exp = Table.ExpandTableColumn(Grp, "Details", {"Continent", "Year", "Sales"}) & GrTotal
in
ExpPower Query solution 4 for Yearly Sales for All Continents, proposed by Aditya Kumar Darak 🇮🇳:
letely agree with you. That's one reason why I started loving List.TransformMany function.
I just wanted to show the audience that we can do so much with Power Query UI, that we shouldn't be afraid of M Language.
In real life, I would use more UI approach when there would be humongous data for better performance. But on challenges, I wanted to show my skills 😉.
Power Query solution 5 for Yearly Sales for All Continents, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Year = List.Sort(List.Distinct(Source[Year])),
Continent = List.Sort(List.Distinct(Source[Continent])),
Generate = List.TransformMany(
Year,
(x) => Continent & {"Total", null},
(x, y) =>
if y = null then
{null, null, null}
else if y = "Total" then
{y, null, List.Sum(Table.SelectRows(Source, each [Year] = x)[Sales])}
else
{y, x, Source{[Continent = y, Year = x]}?[Sales]? ?? 0}
)
& {
{
"Grand Total",
Text.From(Year{0}) & "-" & Text.From(List.Last(Year)),
List.Sum(Source[Sales])
}
},
Return = Table.FromRows(Generate, Table.ColumnNames(Source))
in
ReturnPower Query solution 6 for Yearly Sales for All Continents, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Cont = List.Sort(List.Distinct(Source[Continent])),
Lista = Table.ExpandListColumn(
Table.Group(
Source,
"Year",
{
"A",
each
let
a = _,
b = List.Difference(Cont, [Continent]),
c = List.Transform(b, each {_, a[Year]{0}, 0}),
d = List.Repeat({null}, Table.ColumnCount(a)),
e = List.Sort(Table.ToRows(a) & c, each List.PositionOf(Cont, _{0})),
f = {"TOTAL", a[Year]{0}, List.Sum(List.Transform(e, each List.Last(_)))},
g = e & {f} & {d}
in
g
}
)[[A]],
"A"
)[A],
Sol = Table.FromRows(
Lista
& {
{
"GRAND TOTAL",
Text.Combine(List.Transform({Source[Year]{0}, List.Last(Source[Year])}, Text.From), "-"),
List.Sum(Source[Sales])
}
},
Table.ColumnNames(Source)
)
in
SolPower Query solution 7 for Yearly Sales for All Continents, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Tb2 = Table.FromRows(
List.Transform(List.Distinct(Source[Continent]), each {_, 0}),
{"Continent", "Sales"}
),
Group = Table.Combine(
Table.Group(
Source,
{"Year"},
{
{
"A",
(x) =>
let
a = Table.Sort(
Table.Distinct(Table.RemoveColumns(x, "Year") & Tb2, "Continent"),
"Continent"
),
b = Table.AddColumn(a, "Year", each x[Year]{0}),
c = Table.FromColumns(
{{"TOTAL", null}, {b[Year]{0}, null}, {List.Sum(b[Sales]), null}},
Table.ColumnNames(Source)
),
d = Table.ReorderColumns(b & c, Table.ColumnNames(Source))
in
d
}
}
)[A]
),
Sol = Group
& Table.FromColumns(
{
{"GRAND TOTAL"},
let
a = List.RemoveNulls(List.Distinct(Group[Year])),
b = Text.From(a{0}) & "-" & Text.From(List.Last(a))
in
{b},
{List.Sum(Source[Sales])}
},
Table.ColumnNames(Source)
)
in
SolPower Query solution 8 for Yearly Sales for All Continents, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
L = List.Transform,
D = List.Distinct,
K = List.Skip,
C = Table.RowCount,
N = Table.ColumnNames,
TR = Table.FromRows,
TC = Table.ToColumns,
R = C(S),
a = Table.TransformColumnTypes(S, {"Year", type text}),
b = Table.CombineColumns(a, {"Continent", "Year"}, Combiner.CombineTextByDelimiter(" "), "M"),
c = Table.FromColumns(
{List.TransformMany(D(S[Continent]), (x) => L(D(S[Year]), Text.From), (x, y) => x & " " & y)}
),
d = Table.ReplaceErrorValues(
Table.AddColumn(c, "L", each b[Sales]{List.PositionOf(b[M], [Column1])}),
{"L", 0}
),
e = Table.Sort(
Table.SplitColumn(d, "Column1", Splitter.SplitTextByDelimiter(" "), {"C", "Y"}),
{{"Y", 0}, {"C", 0}}
),
f = Table.Group(
e,
{"Y"},
{"G", each _ & TR({{"TOTAL"} & D([Y]) & L(K(TC(_), 2), List.Sum)}, N(e))}
)[G],
g = Table.Combine(L(f, each Table.InsertRows(_, C(_), {[C = null, Y = null, L = null]}))),
h = g
& TR(
{
{"GRAND TOTAL"}
& {Text.From(S[Year]{0}) & "-" & Text.From(S[Year]{R - 1})}
& L(K(TC(g), 2), each List.Sum(List.RemoveNulls(_)) / 2)
},
N(g)
),
Sol = Table.RenameColumns(h, List.Zip({N(h), N(S)}))
in
SolPower Query solution 9 for Yearly Sales for All Continents, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name="tData187"]}[Content],
CN = Table.ColumnNames(Source),
CList = List.Sort(List.Distinct(Source[Continent])),
YList = List.Sort(List.Distinct(Source[Year])),
YRange = Text.From(List.First(YList)) & "-" & Text.From(List.Last(YList)),
TotalSales = List.Sum(Source[Sales]),
Group = Table.Group(Source, "Year", {"G", (t)=>let
_Y = t[Year]{0},
_MissingCRows = List.Transform(List.Difference(CList, t[Continent]), each {_, _Y, 0}),
_AddMissingC = Table.FromRows( Table.ToRows(t) & _MissingCRows, CN),
_Sort = Table.Sort(_AddMissingC, "Continent"),
_Total = hashtag#table(CN, { {"TOTAL", _Y, List.Sum(t[Sales])}, {null,null,null}})
in _Sort & _Total }),
Add_GT = Table.Combine(Group[G]) & hashtag#table(CN, {{"GRAND TOTAL", YRange, TotalSales}})
in
Add_GT
Power Query solution 10 for Yearly Sales for All Continents, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name="T"]}[Content],
A = Table.FromColumns({List.Distinct(List.Sort(S[Continent]))},{"Continent"}),
B = Table.AddColumn(A, "Year", each List.Distinct(List.Sort(S[Year]))),
C = Table.ExpandListColumn(B, "Year"),
D = Table.NestedJoin(C,{"Year","Continent"},S,{"Year","Continent"},"T"),
E = Table.ExpandTableColumn(D, "T", {"Sales"}, {"Sales"}),
F = Table.Sort(E,{{"Year", Order.Ascending}, {"Continent", Order.Ascending}}),
G = Table.Group(F, {"Year"}, {{"Sales", each List.Sum([Sales]), type nullable number}}),
H = Table.AddColumn(G, "Continent", each "Total"),
I = Table.AddColumn(H, "Index", each 100),
K = Table.Group(F, {"Year"}, {{"C", each _, type table [Continent=text, Year=number, Sales=nullable number]}}),
L = Table.AddColumn(K, "X", each Table.AddIndexColumn(Table.Sort([C],{"Continent",Order.Ascending}),"Index",1,1)),
M = Table.SelectColumns(L,{"X"}),
N = Table.ExpandTableColumn(M, "X", {"Continent", "Year", "Sales", "Index"}, {"Continent", "Year", "Sales", "Index"}),
O = Table.Combine({N,I}),
P = Table.Sort(O,{{"Year", Order.Ascending}, {"Index", Order.Ascending}}),
Q = Table.Group(P, {"Year"}, {{"L", each _&#table({},{{}})}}),
Power Query solution 11 for Yearly Sales for All Continents, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
Part2:
R = Table.SelectColumns(Q,{"L"}),
T = Table.ExpandTableColumn(R, "L", {"Continent", "Year", "Sales", "Index"}, {"Continent", "Year", "Sales", "Index"}),
U = Table.RemoveColumns(T,{"Index"}),
V = Table.Group(U, {}, {{"Sales", each List.Sum([Sales])/2, type nullable number}}),
W = Table.AddColumn(V, "Continent", each "Grand Total"),
X = Table.AddColumn(W, "Year", each Text.From(List.Min(U[Year]))&"-"&Text.From(List.Max(U[Year]))),
Fin = Table.Combine({U,X})
in
Fin
Power Query solution 12 for Yearly Sales for All Continents, proposed by Yaroslav Drohomyretskyi:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
FinalTable = Table.RenameColumns(Table.SelectColumns(Table.ExpandTableColumn(Table.AddColumn(Table.Group(Table.ReplaceValue(Table.ExpandTableColumn(Table.NestedJoin(Table.ExpandTableColumn(Table.AddColumn(Table.Sort(Table.Distinct(Table.SelectColumns(Source, {"Year"})), {{"Year", Order.Ascending}}), "Custom", each Table.Distinct(Table.SelectColumns(Source, {"Continent"}))), "Custom", {"Continent"}), {"Continent", "Year"}, Source, {"Continent", "Year"}, "Expanded Custom", JoinKind.LeftOuter), "Expanded Custom", {"Sales"}), null, 0, Replacer.ReplaceValue, {"Sales"}), {"Year"}, {{"Data", each _}}), "Custom", each
let
Data = Table.Sort([Data], {{"Continent", Order.Ascending}}),
TotalSales = Table.Group(Data, {"Year"}, {{"Sales", each List.Sum([Sales]), type number}, {"Continent", each "TOTAL"}})
in
Data & TotalSales & hashtag#table({},{{}})
), "Custom", {"Continent", "Sales", "Year"},{"Continent", "Sales", "Y1"}) & hashtag#table(
{"Continent", "Year1", "Sales"},
{{"GRAND TOTAL", Number.ToText(List.Min(Source[Year])) & "-" & Number.ToText(List.Max(Source[Year])), List.Sum(Source[Sales])}}
), {"Continent", "Y1", "Sales"}),{{"Y1", "Year"}})
in
FinalTable
Power Query solution 13 for Yearly Sales for All Continents, proposed by Alejandra Horvath CPA, CGA:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = Table.Pivot(Table.TransformColumnTypes(Source, {{"Continent", type text}}, "en-CA"), List.Distinct(Table.TransformColumnTypes(Source, {{"Continent", type text}}, "en-CA")[Continent]), "Continent", "Sales", List.Sum),
b = Table.AddColumn(a, "Total", each List.Sum({[Europe], [Asia], [NA], [SA], [Australia]}), type number),
c = Table.ReplaceValue(b,null,0,Replacer.ReplaceValue,Table.ColumnNames(b)),
d = Table.UnpivotOtherColumns(c, {"Year"}, "Continent", "Sales"),
e = Table.Group(d, {"Year"}, {{"A", each (Table.InsertRows (Table.Sort(_,{{"Continent", 0}}), Table.RowCount(_), {""} ))}}),
f = Table.ExpandTableColumn(e, "A", Table.ColumnNames(e[A]{0}?), {"Year ", "Continent", "Sales"}) [[Continent], [#"Year "], [Sales]],
g = Table.ReplaceErrorValues(f, {{"Continent", null}, {"Year ", null}, {"Sales", null}}),
h = g & hashtag#table({"Year ", "Continent", "Sales"}, {{"2010-2013", "GRAND TOTAL", List.Sum(b[Total])}})
in
h
Solving the challenge of Yearly Sales for All Continents with Excel
Excel solution 1 for Yearly Sales for All Continents, proposed by Bo Rydobon 🇹🇭:
=LET(
c,
A2:A12,
y,
B2:B12,
s,
C2:C12,
u,
VSTACK(
SORT(
UNIQUE(
c
)
),
"*"
),
VSTACK(
REDUCE(
A1:C1,
UNIQUE(
y
),
LAMBDA(
a,
v,
VSTACK(
a,
EXPAND(
SUBSTITUTE(
IFNA(
HSTACK(
u,
v,
SUMIFS(
s,
c,
u,
y,
v
)
),
v
),
"*",
"TOTAL"
),
ROWS(
u
)+1,
,
""
)
)
)
)&,
HSTACK(
"GRAND TOTAL",
@+y&-MAX(
y
),
SUM(
s
)
)
)
)Excel solution 2 for Yearly Sales for All Continents, proposed by محمد حلمي:
🇵🇪 Ned Navarrete C.
SUM(C) Instead of SUM(INDEX(d,,3))/2)
////
SEQUENCE(ROWS(UNIQUE(a))+1)^0
Instead of
ROW(1:6)^0 to make it dynamic
Excel solution 3 for Yearly Sales for All Continents, proposed by محمد حلمي:
=LET(
a,
A2:A12,
b,
B2:B12,
C,
C2:C12,
W,
"TOTAL",
k,
TOCOL(
TOROW(
VSTACK(
SORT(
UNIQUE(
a
)
),
W,
""
)
)&UNIQUE(
b
)
),
x,
TEXTSPLIT(
k,
2
),
v,
RIGHT(
k,
4
),
VSTACK(
HSTACK(
x,
IF(
x="",
"",
v
),
IF(
x="",
"",
SUMIFS(
C,
b,
v,
a,
IF(
x=W,
"*",
x
)
)
)
),
HSTACK(
"GRAND "&W,
@+b&-MAX(
b
),
SUM(
C
)
)
)
)Excel solution 4 for Yearly Sales for All Continents, proposed by 🇰🇷 Taeyong Shin:
=LET(
c,
A2:A12,
y,
B2:B12,
s,
C2:C12,
h,
A1:C1,
u,
UNIQUE(
c
),
uy,
UNIQUE(
y
),
r,
REDUCE(
h,
uy,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
g,
GROUPBY(
HSTACK(
u,
v+N(
u
)
),
u&v,
LAMBDA(
x,
SUM(
XLOOKUP(
x,
c&y,
s,
0
)
)
)
),
IF(
g="",
v,
g
)
),
T(
N(
+h
)
)
)
)
),
VSTACK(
r,
HSTACK(
"GRAND TOTAL",
@uy&"-"&MAX(
uy
),
SUM(
TAKE(
r,
,
-1
)
)/2
)
)
)Excel solution 5 for Yearly Sales for All Continents, proposed by Julian Poeltl:
=LET(
T,
A2:C12,
C,
TAKE(
T,
,
1
),
Y,
CHOOSECOLS(
T,
2
),
S,
TAKE(
T,
,
-1
),
YU,
UNIQUE(
Y
),
CU,
SORT(
UNIQUE(
C
)
),
A,
WRAPROWS(
TEXTSPLIT(
CONCAT(
MAP(
YU,
LAMBDA(
A,
TEXTJOIN(
",",
FALSE,
EXPAND(
VSTACK(
HSTACK(
CU,
SEQUENCE(
COUNTA(
CU
),
,
A,
0
),
XLOOKUP(
CU&A,
C&Y,
S,
0
)
),
HSTACK(
"TOTAL",
A,
SUM(
FILTER(
S,
Y=A
)
)
)
),
COUNTA(
CU
)+2,
,
""
),
""
)
)
)
),
","
),
3
),
R,
VSTACK(
DROP(
A,
-1
),
HSTACK(
"GRAND TOTAL",
MIN(
Y
)&"-"&MAX(
Y
),
SUM(
S
)
)
),
IFERROR(
R*1,
R
)
)Excel solution 6 for Yearly Sales for All Continents, proposed by Oscar Mendez Roca Farell:
=LET(
c,
A2:A12,
y,
B2:B12,
s,
C2:C12,
u,
SORT(
UNIQUE(
c
)
),
t,
"TOTAL",
VSTACK(
REDUCE(
A1:C1,
UNIQUE(
y
),
LAMBDA(
i,
x,
LET(
p,
XLOOKUP(
u&x,
c&y,
s,
0
),
VSTACK(
i,
EXPAND(
VSTACK(
IFNA(
HSTACK(
u,
x,
p
),
x
),
HSTACK(
t,
x,
SUM(
p
)
)
),
ROWS(
p
)+2,
,
""
)
)
)
)
),
HSTACK(
"GRAND "&t,
@y&"-"&MAX(
y
),
SUM(
s
)
)
)
)Excel solution 7 for Yearly Sales for All Continents, proposed by Sunny Baggu:
=LET(
_c,
SORT(
UNIQUE(
A2:A12
)
),
_y,
SORT(
UNIQUE(
B2:B12
)
),
_v,
REDUCE(
A1:C1,
SORT(
UNIQUE(
B2:B12
)
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_a,
IFNA(
HSTACK(
_c,
y
),
y
),
_b,
XLOOKUP(
BYROW(
_a,
LAMBDA(
a,
CONCAT(
a
)
)
),
A2:A12 & B2:B12,
C2:C12,
0
),
VSTACK(
HSTACK(
_a,
_b
),
HSTACK(
"TOTAL",
y,
SUM(
_b
)
),
EXPAND(
"",
1,
3,
""
)
)
)
)
)
),
_f,
HSTACK(
"GRAND TOTAL",
TEXTJOIN(
"-",
,
TAKE(
_y,
{1,
-1}
)
),
SUM(
FILTER(
TAKE(
_v,
,
-1
),
TAKE(
_v,
,
1
) = "TOTAL"
)
)
),
VSTACK(
_v,
_f
)
)Excel solution 8 for Yearly Sales for All Continents, proposed by LEONARD OCHEA 🇷🇴:
=LET(
a,
A2:A12,
b,
B2:B12,
c,
C2:C12,
D,
DROP,
E,
TAKE,
V,
VSTACK,
H,
HSTACK,
S,
SUM,
p,
PIVOTBY(
a,
b,
c,
S,
,
,
,
0
),
u,
D(
E(
p,
1
),
,
1
),
x,
D(
p,
1,
1
),
l,
D(
E(
p,
,
1
),
1
),
V(
REDUCE(
A1:C1,
u,
LAMBDA(
i,
j,
V(
i,
EXPAND(
H(
l,
IF(
l>0,
j
),
XLOOKUP(
j,
u,
x
)
),
ROWS(
l
)+1,
,
""
)
)
)
),
H(
"GRAND TOTAL",
MIN(
b
)&"-"&MAX(
b
),
S(
c
)
)
)
)Excel solution 9 for Yearly Sales for All Continents, proposed by 🇵🇪 Ned Navarrete C.:
=LET(
a,
A2:A12,
b,
B2:B12,
c,
C2:C12,
k,
SORT(
UNIQUE(
a
)
),
y,
UNIQUE(
b
),
VSTACK(
REDUCE(
E1:G1,
y,
LAMBDA(
z,
v,
LET(
s,
XLOOKUP(
k&v,
a&b,
c,
0
),
w,
--REPT(
v,
SEQUENCE(
ROWS(
k
)+1
)^0
),
VSTACK(
z,
IFNA(
HSTACK(
k,
w,
s
),
HSTACK(
"TOTAL",
"",
SUM(
s
)
)
),
{"",
"",
""}
)
)
)
),
HSTACK(
"GRAND TOTAL",
MIN(
y
)&"-"&MAX(
y
),
SUM(
c
)
)
)
)Excel solution 10 for Yearly Sales for All Continents, proposed by Md. Zohurul Islam:
=LET(
u,
B2:B12,
v,
A2:A12,
w,
C2:C12,
unq,
UNIQUE(
u
),
s,
SORT(
UNIQUE(
v
)
),
p,
IFNA(
REDUCE(
A1:C1,
unq,
LAMBDA(
x,
y,
LET(
a,
FILTER(
HSTACK(
v,
w
),
u=y
),
b,
XLOOKUP(
s,
DROP(
a,
,
-1
),
DROP(
a,
,
1
),
0
),
c,
VSTACK(
IFNA(
HSTACK(
s,
y,
b
),
y
),
HSTACK(
"TOTAL",
y,
SUM(
b
)
),
""
),
d,
VSTACK(
x,
c
),
d
)
)
),
""
),
q,
HSTACK(
"GRAND TOTAL",
MIN(
unq
)&"-"&MAX(
unq
)
),
r,
SUM(
DROP(
FILTER(
TAKE(
p,
,
-1
),
TAKE(
p,
,
1
)<>"TOTAL"
),
1
)
),
z,
VSTACK(
p,
HSTACK(
q,
r
)
),
z
)Excel solution 11 for Yearly Sales for All Continents, proposed by Hamidi Hamid:
LET(
x,
SORT(
HSTACK(
SORT(
TOCOL(
IFNA(
UNIQUE(
A2:A12
),
SEQUENCE(
,
COUNTA(
UNIQUE(
B2:B12
)
)
)
)
)
),
TOCOL(
IFNA(
HSTACK(
TRANSPOSE(
UNIQUE(
B2:B12
)
)
),
VSTACK(
UNIQUE(
A2:A12
)
& )
)
)
),
2
),
HSTACK(
x,
SUMIFS(
C2:C12,
A2:A12,
TAKE(
x,
,
1
),
B2:B12,
TAKE(
x,
,
-1
)
)
)
)
Show translationExcel solution 12 for Yearly Sales for All Continents, proposed by Asheesh Pahwa:
=LET(
c,
A2:A12,
y,
B2:B12,
s,
C2:C12,
uc,
SORT(
UNIQUE(
c
)
),
uy,
UNIQUE(
y
),
cy,
TOCOL(
uc&"-"&TOROW(
uy
),
,
1
),
ts,
TEXTSPLIT(
cy,
"-"
),
ta,
--TEXTAFTER(
cy,
"-"
),
x,
XLOOKUP(
ts&ta,
c&y,
s,
0
),
h,
HSTACK(
ts,
ta,
x
),
d,
DROP(
DROP(
IFNA(
REDUCE(
"",
uy,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
f,
FILTER(
h,
ta=v
),
v,
VSTACK(
f,
HSTACK(
"TOTAL",
v,
SUM(
TAKE(
f,
,
-1
)
)
),
""
),
v
)
)
)
),
""
),
1
),
-1
),
tt,
FILTER(
d,
TAKE(
d,
,
1
)="TOTAL"
),
IFNA(
VSTACK(
d,
"",
HSTACK(
"GRAND TOTAL",
"2010 2013",
SUM(
TAKE(
tt,
,
-1
)
)
)
),
""
)
)Excel solution 13 for Yearly Sales for All Continents, proposed by Burhan Cesur:
=LET(
a,
A2:A12,
b,
B2:B12,
c,
C2:C12,
ac,
A2:C12,
l,
SORTBY(
ac,
a,
1,
b,
1
),
u,
VSTACK(
UNIQUE(
INDEX(
l,
,
1
)
),
"TOTAL"
),
y,
UNIQUE(
INDEX(
l,
,
2
)
),
n,
REDUCE(
E1:G1,
y,
LAMBDA(
s,
v,
VSTACK(
s,
HSTACK(
IF(
{1 },
u,
v
),
IF(
u="TOTAL",
SUMIF(
b,
v,
c
),
SUMIFS(
c,
a,
u,
b,
v
)
)
),
IF(
v>=MAX(
y
),
VSTACK(
"",
HSTACK(
"GRAND TOTAL",
MIN(
y
)&"-"&MAX(
y
),
SUM(
c
)
)
),
""
)
)
)
),
IFERROR(
n,
""
)
)Solving the challenge of Yearly Sales for All Continents with Python in Excel
Python in Excel solution 1 for Yearly Sales for All Continents, proposed by Alejandro Campos:
df = xl("A1:C12", headers=True)
df1 = pd.DataFrame(sorted(df['Continent'].unique()), columns=['Continent'])
years = sorted(df['Year'].unique())
dfs = [pd.concat([
pd.merge(df1, df[df['Year'] == year], how='left')
.fillna({'Year': year, 'Sales': 0}).astype({'Year': int, 'Sales': int})
.astype(str)
._append({'Continent': 'TOTAL', 'Year': str(year), 'Sales':
str(df[df['Year'] == year]['Sales'].sum())}, ignore_index=True)
._append({'Continent': '', 'Year': '', 'Sales': ''}, ignore_index=True)
]) for year in years]
df = pd.concat(dfs, ignore_index=True)
df.loc[len(df)] = ['GRAND TOTAL', f"-{years[-1]}",
str(df['Sales'].apply(pd.to_numeric, errors='coerce').sum())]
df
Python in Excel solution 2 for Yearly Sales for All Continents, proposed by Abdallah Ally:
import pandas as pd
file_path = 'PQ_Challenge_187.xlsx'
df = pd.read_excel(file_path, usecols='A:C', nrows=11, keep_default_na=False)
# Perform data wrangling
df1 = pd.DataFrame(sorted(df['Continent'].unique()), columns=['Continent'])
years = sorted(df['Year'].unique())
dfs = []
for year in years:
dfn = pd.merge(df1, df[df['Year'] == year], how='left')
dfn['Year'] = dfn['Year'].fillna(year).astype(int)
dfn['Sales'] = dfn['Sales'].fillna(0).astype(int)
dfn = dfn.astype(str)
dfn.loc[len(dfn)] = ['TOTAL', str(year), str(sum(df['Sales'][df['Year'] == year]))]
dfn.loc[len(dfn)] = ['', '', '']
dfs.append(dfn)
overall = ['GRAND TOTAL', str(years[0]) + '-' + str(years[-1]), str(sum(df['Sales']))]
df = pd.concat(dfs, ignore_index=True)
df.loc[len(df)] = overall
df
Solving the challenge of Yearly Sales for All Continents with R
R solution 1 for Yearly Sales for All Continents, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_187.xlsx", range = "A1:C12")
test = read_excel("Power Query/PQ_Challenge_187.xlsx", range = "E1:G30")
all <- expand_grid(Continent = unique(sort(input$Continent)), Year = unique(sort(input$Year)))
result1 <- all %>%
left_join(input, by = c("Continent", "Year")) %>%
mutate(Sales = replace_na(Sales, 0),
Year = as.character(Year))
years <- unique(sort(result1$Year))
empty_row <- tibble(Continent = NA, Year = NA, Sales = NA_real_)
totals <- map_dfr(years, ~ {
yearly_data <- result1 %>%
filter(Year == .x)
total_row <- summarise(yearly_data, Continent = "TOTAL", Year = .x, Sales = sum(Sales))
bind_rows(yearly_data, total_row, empty_row)
})
grand_total <- summarise(result1, Continent = "GRAND TOTAL", Year = "2010-2013", Sales = sum(Sales))
result <- bind_rows(totals, grand_total)
identical(result, test)
# [1] TRUE
&