Pivot the Customer’s table Create a Total for each Month Dynamic array function allowed but Extra marks for Legacy solutions or PowerQuery Solution
📌 Challenge Details and Links
Challenge Number: 22
Challenge Difficulty: ⭐⭐⭐
📥Link to the solutions on LinkedIn
Solving the challenge of Pivoting Data with Power Query
Power Query solution 1 for Pivoting Data, proposed by Omid Motamedisedeh:
let
Source = Excel.CurrentWorkbook(){[Name = "tblSales"]}[Content],
Mrg = Table.CombineColumns(
Source,
{"Month", "Customers", "Sales"},
each Table.FromColumns(
{{_{0}}, Text.Split(_{1}, ","), List.Transform(Text.Split(_{2}, ","), Number.From)},
{"a", "b", "c"}
),
"N"
),
TtlRow = Table.TransformColumns(
Mrg,
{
{
"N",
each Table.FillDown(_, {"a", "b"})
& Table.FromRows({{"Total " & _[a]{0}, List.Sum(_[c])}}, {"a", "c"})
}
}
),
Result = Table.ExpandTableColumn(TtlRow, "N", {"a", "b", "c"}, {"Month", "Customer", "Sale"})
in
ResultPower Query solution 2 for Pivoting Data, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "tblSales"]}[Content],
S = Table.FromRows(
List.TransformMany(
Table.ToRows(Source),
each
let
l = each Text.Split(Text.Remove(_, " "), ","),
c = l(_{1}),
s = List.Transform(l(_{2}), Number.From)
in
List.Zip(
{c & List.Repeat({List.Last(c)}, List.Count(s) - List.Count(c)), s & {List.Sum(s)}}
),
(i, _) => {{"", "Total "}{Number.From(_{0} = null)} & i{0}} & _
),
Table.ColumnNames(Source)
)
in
SPower Query solution 3 for Pivoting Data, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "tblSales"]}[Content],
List = Table.AddColumn(
Source,
"L",
each [
M = [Month],
C = Text.Split([Customers], ", "),
S = List.Transform(Text.Split([Sales], ","), (f) => Number.From(f)),
Z = List.Zip({{M}, C, S}),
R = Z & {{"Total " & M, null, List.Sum(S)}}
][R]
),
Table = Table.FromRows(List.Combine(List[L]), Table.ColumnNames(Source)),
Return = Table.FillDown(Table, {"Month"})
in
ReturnPower Query solution 4 for Pivoting Data, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "tblSales"]}[Content],
Sol = Table.Combine(
Table.Group(
Source,
{"Month"},
{
{
"A",
each
let
a = _,
b = Table.AddColumn(
a,
"B",
each Table.FromColumns(
List.Transform({[Customers], [Sales]}, each Text.Split(_, ", ")),
List.Skip(Table.ColumnNames(a))
)
),
c = Table.SelectColumns(b, {"Month", "B"}),
d = Table.ExpandTableColumn(c, "B", Table.ColumnNames(b[B]{0})),
e = d
& Table.FromRows(
{
{"Total " & d[Month]{0}}
& {null}
& {List.Sum(List.Transform(d[Sales], Number.From))}
},
Table.ColumnNames(a)
)
in
e
}
}
)[A]
)
in
SolPower Query solution 5 for Pivoting Data, proposed by Luan Rodrigues:
let
Fonte = tblSales,
tab = Table.AddColumn(Fonte, "tab", each
let
a = Table.FillDown(Table.FromRows(List.Zip(List.Transform(Record.FieldValues(_),each Text.Split(_,","))),Table.ColumnNames(Fonte)),{"Month","Customers"}),
b =
hashtag
#table(Table.ColumnNames(Fonte),{{"Total "&a[Month]{0},null,List.Sum(List.Transform(a[Sales],Number.From))}} )
in a & b
)[tab],
res = Table.Combine(tab)
in
resPower Query solution 6 for Pivoting Data, proposed by Brian Julius:
let/in statement - a topic I posted about a couple of times last week.
In this case, you can just build the monthly tables step-by-step within the record, checking your steps as you go, and then just call the final table when you're done. Easy-peasy. 😁
Here's my
hashtag
#powerquery solution:
let
Source = Excel.CurrentWorkbook(){[Name="tblSales"]}[Content],
BuildTables = Table.SelectColumns( Table.AddColumn(Source, "Build", each [
M = [Month],
C = Text.Split( [Customers], ", "),
S = List.Transform(Text.Split( [Sales], ","), each Number.From(_)),
T1 = Table.FromColumns( {{M}, C, S }),
T2 = Table.FromColumns( {{"Total " & M}, {}, {List.Sum(S) }}),
T = Table.FillDown( T1 & T2, {"Column1"} )
][T]), "Build"),
Head = Table.ColumnNames(Source),
Expand = Table.ExpandTableColumn(BuildTables, "Build", {"Column1", "Column2", "Column3"}, Head)
in
ExpandPower Query solution 7 for Pivoting Data, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.TransformColumns(
S,
{
{"Customers", each Text.Split(_, ", ")},
{"Sales", each List.Select(Text.SplitAny(_, ",, "), each _ <> "")}
}
),
b = Table.AddColumn(a, "M", each Table.FromColumns({[Customers], [Sales]}))[[Month], [M]],
c = Table.TransformColumnTypes(
Table.FillDown(Table.ExpandTableColumn(b, "M", {"Column1", "Column2"}), {"Column1"}),
{"Column2", Int64.Type}
),
d = Table.RenameColumns(c, List.Zip({Table.ColumnNames(c), Table.ColumnNames(S)})),
e = Table.Group(d, {"Month"}, {"G", each _})[[G]],
f = Table.TransformColumns(
e,
{
"G",
each Table.Group(
_,
{},
{
{"Month", each "Total " & [Month]{0}},
{"Customers", each ""},
{"Sales", each List.Sum([Sales])}
}
)
}
),
g = List.Generate(
() => [i = 0],
each [i] < Table.RowCount(f),
each [i = [i] + 1],
each Record.ToTable(e{[i]})[[Value]] & Record.ToTable(f{[i]})[[Value]]
),
Sol = Table.Combine(Table.Combine(g)[Value])
in
SolPower Query solution 8 for Pivoting Data, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "tblSales"]}[Content],
B = Table.AddColumn(
S,
"T",
each Table.FillDown(
Table.FromColumns(
{
Splitter.SplitTextByDelimiter(",")([Customers]),
Splitter.SplitTextByDelimiter(",")([Sales])
},
{"Customer", "Sales"}
),
{"Customer"}
)
),
C = Table.SelectColumns(B, {"Month", "T"}),
D = Table.ExpandTableColumn(C, "T", {"Customer", "Sales"}, {"Customer", "Sales"}),
E = Table.TransformColumnTypes(D, {{"Sales", Int64.Type}}),
F = Table.TransformColumns(E, {{"Customer", Text.Trim, type text}}),
G = Table.AddColumn(
F,
"Index",
each
if [Month] = "Jan" then
1
else if [Month] = "Feb" then
2
else if [Month] = "Mar" then
3
else if [Month] = "Apr" then
4
else
null,
Int64.Type
),
H = Table.Group(G, {"Month"}, {{"Sales", each List.Sum([Sales]), type nullable number}}),
I = Table.AddIndexColumn(H, "Index", 1, 1, Int64.Type),
J = Table.TransformColumns(I, {{"Month", each "Total " & _, type text}}),
K = Table.Combine({G, J}),
L = Table.Sort(K, {{"Index", Order.Ascending}, {"Sales", Order.Ascending}}),
M = Table.SelectColumns(L, {"Month", "Customer", "Sales"})
in
MPower Query solution 9 for Pivoting Data, proposed by Peter Tholstrup:
let
Source = Excel.CurrentWorkbook(){[Name = "tblSales"]}[Content],
transform = List.Transform(
Table.ToRecords(Source),
each [
getList = (lst, func) => List.Transform(Text.Split(lst, ","), func),
customers = getList([Customers], Text.Trim),
sales = getList([Sales], Number.FromText),
pair = List.Zip({customers, sales}),
month = [Month],
sales_per_customer = List.Transform(pair, each [Month = month, Customer = _{0}, Sales = _{1}]),
details = Table.FillDown(Table.FromRecords(sales_per_customer), {"Customer"}),
totals = Table.FromRecords({[Month = "Total " & month, Sales = List.Sum(sales)]}),
result = Table.Combine({details, totals})
][result]
),
result = Table.Combine(transform)
in
resultPower Query solution 10 for Pivoting Data, proposed by Nelson Mwangi:
let
Source = Excel.CurrentWorkbook(){[Name = "tblSales"]}[Content],
SplitCust = Table.AddColumn(Source, "Custom", each Text.Split([Customers], ",")),
SplitSales = Table.AddColumn(SplitCust, "Custom.1", each Text.Split([Sales], ",")),
TableFromCols = Table.AddColumn(
SplitSales,
"Data",
each Table.FromColumns({[Custom], [Custom.1]}, {"Customers", "Sales"})
),
RemoveCols = Table.SelectColumns(TableFromCols, {"Month", "Data"}),
Expand = Table.ExpandTableColumn(RemoveCols, "Data", {"Customers", "Sales"}),
FillD = Table.FillDown(Expand, {"Customers"}),
DataType = Table.TransformColumnTypes(FillD, {{"Sales", type number}}),
Totals = Table.Group(DataType, {"Month"}, {{"Sales", each List.Sum([Sales])}}),
CreateTotalColumn = Table.AddColumn(Totals, "M", each "Total " & Text.From([Month])),
RemoveMonth = Table.SelectColumns(CreateTotalColumn, {"Sales", "M"}),
Rename = Table.RenameColumns(RemoveMonth, {{"M", "Month"}}),
Combine = Table.Combine({DataType, Rename})
in
CombineSolving the challenge of Pivoting Data with Excel
Excel solution 1 for Pivoting Data, proposed by 🇰🇷 Taeyong Shin:
=LET(
d,
B3:D6,
REDUCE(
B2:D2,
TAKE(
d,
,
1),
LAMBDA(
a,
v,
LET(
f,
LAMBDA(
c,
TEXTSPLIT(
INDEX(
v:D6,
1,
c),
,
",")),
s,
--f(
3),
VSTACK(
a,
CHOOSE(
{1,
2,
3},
v,
f(
2),
s),
HSTACK(
"Total"&v,
"",
SUM(
s)))))))Excel solution 2 for Pivoting Data, proposed by Julian Poeltl:
=LET(
T,
B2:D6,
TT,
DROP(
T,
1),
LC,
LAMBDA(
T,
LEN(
T)-LEN(
SUBSTITUTE(
T,
",",
""))),
M,
TAKE(
TT,
,
1),
S,
TAKE(
TT,
,
-1),
C,
CHOOSECOLS(
TT,
2),
CR,
MAP(
C,
S,
LAMBDA(
A,
B,
IF(
ISNUMBER(
SEARCH(
",",
A)),
A,
REPT(
A&",",
LC(
B)+1)))),
CCor,
IF(
RIGHT(
CR,
1)=",",
LEFT(
CR,
LEN(
CR)-1),
CR),
ToC,
LAMBDA(
A,
TEXTSPLIT(
TEXTJOIN(
", ",
,
A),
,
{", ",
","})),
CC,
ToC(
CCor),
SC,
ToC(
S)*1,
AM,
MAP(
M,
S,
LAMBDA(
M,
S,
REPT(
M&",",
LC(
S)+1))),
AMC,
DROP(
TEXTSPLIT(
CONCAT(
AM),
,
","),
-1),
US,
MAP(
M,
LAMBDA(
A,
SUM(
FILTER(
SC,
AMC=A)))),
FM,
TEXT(
SEQUENCE(
4,
,
,
31),
"MMM"),
ST,
VSTACK(
HSTACK(
AMC,
CC,
SC),
IFERROR(
HSTACK(
M&" Total",
"",
US),
"")),
Sort,
XMATCH(
LEFT(
TAKE(
ST,
,
1),
2)&"*",
M,
2),
Sorted,
SORTBY(
ST,
Sort),
VSTACK(
HSTACK(
TAKE(
T,
1,
1),
"Customer",
TAKE(
T,
1,
-1)),
HSTACK(
SCAN(
,
TAKE(
Sorted,
,
1),
LAMBDA(
A,
B,
TEXTAFTER(
B,
" ",
,
,
,
B)&" "&TEXTBEFORE(
B,
" ",
,
,
,
""))),
DROP(
Sorted,
,
1))))Excel solution 3 for Pivoting Data, proposed by Oscar Mendez Roca Farell:
=REDUCE(
B2:D2,
D3:D6,
LAMBDA(
i,
x,
LET(
a,
TAKE(
B3:x,
-1),
F,
LAMBDA(
r,
TEXTSPLIT(
INDEX(
a ,
,
r),
,
","&{"",
" "})),
s,
--F(
3),
VSTACK(
i,
VSTACK(
IFNA(
HSTACK(
F(
1),
F(
2),
s),
a),
HSTACK(
"Total "&F(
1),
"",
SUM(
s)))))))Excel solution 4 for Pivoting Data, proposed by Sunny Baggu:
=REDUCE(
tblSales[
hashtag
#Headers],
SEQUENCE(
ROWS(
tblSales)),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_c,
INDEX(
tblSales,
y,
),
_a,
DROP(
REDUCE(
"",
SEQUENCE(
COLUMNS(
tblSales)),
LAMBDA(
a,
v,
HSTACK(
a,
TEXTSPLIT(
INDEX(
_c,
,
v),
,
{",",
", "},
1)))
),
,
1
),
_b,
HSTACK(
TAKE(
_a,
,
2),
--TAKE(
_a,
,
-1)),
VSTACK(
IFNA(
_b,
INDEX(
_c,
,
1)),
HSTACK(
"Total " & INDEX(
_c,
,
1),
"",
SUM(
TAKE(
_b,
,
-1)))
)
)
)
)
)Excel solution 5 for Pivoting Data, proposed by Abdallah Ally:
=REDUCE(
B2:D2,
B3:B6,
LAMBDA(
x,
y,
LET(
b,
OFFSET(
y,
,
1),
c,
TEXTSPLIT(
b,
,
{", ",
","}),
d,
--TEXTSPLIT(
OFFSET(
y,
,
2),
,
{", ",
","}),
e,
COUNT(
d),
VSTACK(
x,
HSTACK(
EXPAND(
y,
e,
,
y),
IF(
COUNTA(
c)<>e,
EXPAND(
b,
e,
,
b),
c),
d),
HSTACK(
"Total "&y,
"",
SUM(
d))))))Excel solution 6 for Pivoting Data, proposed by Asheesh Pahwa:
=VSTACK(B2:D2,
DROP(REDUCE("",
SEQUENCE(
4),
LAMBDA(x,
y,
VSTACK(x,
LET(I,
INDEX(
B3:D7,
y,
),
f,
IFNA(DROP(REDUCE("",
SEQUENCE (COLUMNS(
I)),
LAMBDA(a,
v,
HSTACK(a,
LET(t,
INDEX(
I,
,
v),
DROP(REDUCE("",
t,
LAMBDA (ac,
it,
VSTACK(
ac,
TEXTSPLIT(
it,
,
{", ",
","})))),
1))))),
,
1),
0),
ff,
IF(
f=0,
INDEX(
f,
1,
),
f),
VSTACK(ff,
HSTACK ("Total "&TAKE(
ff,
1,
1),
"",
SUM(
--TAKE(
ff,
,
-1)))))))),
1))Excel solution 7 for Pivoting Data, proposed by Burhan Cesur:
=REDUCE(
tblSales[
hashtag
#Headers],
SEQUENCE(
ROWS(
tblSales[Sales])),
LAMBDA(
s,
v,
LET(
f,
LAMBDA(
x,
--TRIM(
TEXTSPLIT(
INDEX(
tblSales[Sales],
x,
1),
,
","))),
VSTACK(
s,
HSTACK(
EXPAND(
INDEX(
tblSales[Month],
v,
1),
ROWS(
f(
v)),
,
INDEX(
tblSales[Month],
v,
1)),
LET(
e,
TEXTSPLIT(
INDEX(
tblSales[Customers],
v,
1),
,
","),
r,
ROWS(
f(
v)),
IF(
AND(
ROWS(
e)Solving the challenge of Pivoting Data with R
R solution 1 for Pivoting Data, proposed by Anil Kumar Goyal:
#tidyverse solution
library(readxl)
library(janitor)
library(tidyverse)
df <- read_xlsx("Others/Easy Excel Challenge 28th April.xlsx", range = "B2:D6")
df |>
group_by(Month) |>
group_split(.keep = TRUE) |>
map_dfr(~ .x |>
separate_rows(-Month, convert = TRUE) |>
adorn_totals(name = paste("Total", first(.$Month))))