Transpose the problem table into result table. Here A, B means sum of A & B.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 193
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Unstack Groups for Summation with Power Query
Power Query solution 1 for Unstack Groups for Summation, proposed by Zoran Milokanović:
let
Source = List.Skip(Table.ToRows(Excel.CurrentWorkbook(){[Name = "Input"]}[Content])),
S = Table.FromRows(
List.TransformMany(
List.Transform(
Source,
each
let
c = List.Zip(List.FirstN(Source, List.PositionOf(Source, _) + 1))
in
{Text.Combine(c{0}, ", ")} & List.Transform(List.Skip(c), List.Sum)
),
each
let
d = List.Skip(_)
in
{
{_{0}, "Sales"} & List.Alternate(d, 1, 1, 1),
{null, "Bonus"} & List.Alternate(d, 1, 1),
{null, "Total"} & List.Transform(List.Split(d, 2), List.Sum)
},
(i, _) => _
),
{"Persons", "Category", "Q1", "Q2", "Q3", "Q4"}
)
in
SPower Query solution 2 for Unstack Groups for Summation, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Transpose = Table.Transpose(Source),
Fill = Table.FillDown(Transpose,{"Column1"}),
Promote = Table.PromoteHeaders(Fill, [PromoteAllScalars=true]),
Rename = Table.RenameColumns(Promote,{{"Persons", "Category"}}),
Total = Table.AddColumn(Rename, "Custom", each let
a = List.Skip( Record.ToList( _),2),
b= List.Skip( Table.ColumnNames(Rename),2),
c = List.Generate(()=> [X = a{0},Y = 0],
each [Y] < List.Count(a),
each [X =[X] + a{[Y] +1},Y =[Y]+1],
each [X]),
d = List.Transform(c, each Text.From(_)),
e = List.Generate(()=> [V= b{0} , W=0 ],
each [W] < List.Count(b),
each [V =[V] &", "& b{[W] +1},W =[W]+1 ],
each [V]) ,
f = List.Transform( {0.. List.Count(b)-1} , each e{_}&"#"&d{_} ) in f),
Remove = Table.RemoveColumns(Total,{"A", "B", "C", "D"}),
Xpand = Table.ExpandListColumn(Remove, "Custom"),
Split = Table.SplitColumn(Xpand, "Custom", Splitter.SplitTextByDelimiter("#"), {"Per", "2"}),
Type = Table.TransformColumnTypes(Split,{{"2", Int64.Type}}),
Power Query solution 3 for Unstack Groups for Summation, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Trans = Table.Transpose(Source),
FD = Table.FillDown(Trans,{"Column1"}),
Merge = Table.CombineColumns(FD,{"Column1", "Column2"},Combiner.CombineTextByDelimiter("-"),"Merged"),
Trans2 = Table.Transpose(Merge),
PH = Table.PromoteHeaders(Trans2, [PromoteAllScalars=true]),
Unpivot = Table.UnpivotOtherColumns(PH, {"Quarters-Persons"}, "Attribute", "Value"),
SplitCol = Table.SplitColumn(Unpivot, "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"A", "Category"}),
Pivot = Table.Pivot(SplitCol, List.Distinct(SplitCol[A]), "A", "Value"),
RenCol = Table.RenameColumns(Pivot,{{"Quarters-Persons", "Persons"}}),
Group = Table.Combine(Table.Group(RenCol, {"Category"}, {{"A", each
let
a = _,
b = List.Transform({1..Table.RowCount(a)}, each Text.Combine(List.FirstN(a[Persons], _), ", ")),
c = List.Skip(Table.ToColumns(a), 2),
d = List.Count(c{0}),
e = List.Transform({0..List.Count(c)-1}, each List.Transform({1..d}, (x)=> List.Sum(List.FirstN(c{_}, x)))),
f = Table.FromColumns({b}&{a[Category]}&e, Table.ColumnNames(a))
in f}})[A]),
Power Query solution 4 for Unstack Groups for Summation, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
a = Table.Sort(_, {"Category", 1}),
b = Table.ToColumns(a),
c = List.Transform(List.Skip(b,2), each _&{List.Sum(_)}),
d = Table.FromColumns({{a[Persons]{0}}&List.Repeat({null}, Table.RowCount(a))}&{a[Category]&{"Total"}}&c,
Table.ColumnNames(a))
in d}})[A])
in
Sol
Power Query solution 5 for Unstack Groups for Summation, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "Table210"]}[Content],
Categs = List.Distinct(List.Skip(Record.FieldValues(Source{1}))),
NCateg = List.Count(Categs),
Persons = List.Skip(Source[Column1], 2),
SplitByQ = Table.TransformRows(
Table.Skip(Source, 2),
each
let
_VAll = Record.FieldValues(_),
_P = List.First(_VAll)
in
List.Transform(List.Split(List.Skip(_VAll), NCateg), each _ & {List.Sum(_)})
),
Accumulate = List.Accumulate(
List.Skip(SplitByQ),
[r = SplitByQ{0}, p = SplitByQ{0}],
(s, c) =>
let
_New = List.Transform(List.Zip({s[p], c}), each List.Transform(List.Zip(_), List.Sum))
in
[r = List.Transform(List.Zip({s[r], _New}), List.Combine), p = _New]
)[r],
PCol = List.Accumulate(
Persons,
{},
(s, c) =>
let
_New = Text.Combine({List.Last(List.RemoveNulls(s)), c}, ", ")
in
s & {_New} & List.Repeat({null}, NCateg)
),
CCol = List.Repeat(Categs & {"Total"}, List.Count(Persons)),
Result = Table.FromColumns(
{PCol, CCol} & Accumulate,
{"Person", "Category", "Q1", "Q2", "Q3", "Q4"}
)
in
ResultPower Query solution 6 for Unstack Groups for Summation, proposed by Yaroslav Drohomyretskyi:
let
Source=Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Unpivot=Table.UnpivotOtherColumns(Table.RenameColumns(Table.PromoteHeaders(Table.FillDown(Table.Transpose(Source),{"Column1"}),[PromoteAllScalars=true]),{{"Persons","Category"}}),{"Category","Quarters"},"p","Value"),
Totals=Table.UnpivotOtherColumns(Table.AddColumn(Table.Pivot(Unpivot,List.Distinct(Unpivot[Category]),"Category","Value",List.Sum),"Total",each[Sales]+[Bonus]),{"p","Quarters"},"Category","Value"),
BaseTable=Table.ReplaceValue(Table.Pivot(Table.ReplaceValue(Totals,"Bonus","SalesBonus",Replacer.ReplaceText,{"Category"}),List.Distinct(Totals[Quarters]),"Quarters","Value",List.Sum),"SalesBonus","Bonus",Replacer.ReplaceText,{"Category"}),
Index=Table.AddIndexColumn(Table.Group(BaseTable,{"p"},{{"t",each _}}),"Index",1,1,Int64.Type),
Power Query solution 7 for Unstack Groups for Summation, proposed by Yaroslav Drohomyretskyi:
Concat=Table.RemoveColumns(Table.AddColumn(Table.AddColumn(Index,"Persons",each Text.Combine(List.FirstN(Index[p],[Index]),", ")),"x",each Table.Combine(List.FirstN(Index[t],[Index])
)),{"p","t","Index"}),
Group=Table.Group(Table.ExpandTableColumn(Concat,"x",{"Category","Q1","Q2","Q3","Q4"},{"Category","Q1","Q2","Q3","Q4"}),{"Persons","Category"},{{"Q1",each List.Sum([Q1])},{"Q2",each List.Sum([Q2])},{"Q3",each List.Sum([Q3])},{"Q4",each List.Sum([Q4])}}),
Result=Table.RenameColumns(Table.RemoveColumns(Table.ReorderColumns(Table.AddColumn(Group,"c",each if [Category]="Sales" then [Persons] else null),{"c","Persons","Category","Q1","Q2","Q3","Q4"}),{"Persons"}),{{"c","Persons"}})
in
Result
Solving the challenge of Unstack Groups for Summation with Excel
Excel solution 1 for Unstack Groups for Summation, proposed by Bo Rydobon 🇹🇭:
=LET(
Th,
SCAN(
LAMBDA(
""
),
A3:A6,
LAMBDA(
a,
v,
LAMBDA(
LET(
w,
WRAPCOLS(
TAKE(
I6:v,
1,
-8
),
2
),
IFNA(
HSTACK(
TEXTJOIN(
", ",
,
@a(),
v
),
VSTACK(
B2,
C2,
"Total"
),
N(
TAKE(
a(),
,
-4
)
)+VSTACK(
w,
BYCOL(
w,
SUM
)
)
),
""
)
)
)
)
),
REDUCE(
A12:F12,
Th,
LAMBDA(
a,
v,
VSTACK(
a,
v()
)
)
)
)Excel solution 2 for Unstack Groups for Summation, proposed by Bo Rydobon 🇹🇭:
=REDUCE(
A12:F12,
A3:A6,
LAMBDA(
a,
v,
LET(
w,
WRAPCOLS(
TAKE(
v:I6,
1,
-8
),
2
),
VSTACK(
a,
IFNA(
HSTACK(
TEXTJOIN(
", ",
,
SUBSTITUTE(
TAKE(
a,
-3,
1
),
A2,
),
v
),
VSTACK(
B2,
C2,
"Total"
),
N(
TAKE(
a,
-3,
-4
)
)+VSTACK(
w,
BYCOL(
w,
SUM
)
)
),
""
)
)
)
)
)Excel solution 3 for Unstack Groups for Summation, proposed by محمد حلمي:
=REDUCE(
A12:F12,
I3:I6,
LAMBDA(
a,
v,
LET(
i,
WRAPCOLS(
TAKE(
v:B6,
1
),
2
),
x,
@+v:A6,
VSTACK(
a,
IFNA(
HSTACK(
IF(
x="A",
x,
@TAKE(
a,
-3
)&", "&x
),
VSTACK(
B2,
C2,
"Total"
),
N(
TAKE(
a,
-3,
-4
)
)+VSTACK(
i,
MMULT(
{1,
1},
i
)
)
),
""
)
)
)
)
)Excel solution 4 for Unstack Groups for Summation, proposed by 🇰🇷 Taeyong Shin:
=LET(d,
B3:I6,
f,
LAMBDA(
x,
TOCOL(
IF(
d,
x
)
)
),
n,
ROWS(
d
),
rt,
MAKEARRAY(
n,
COLUMNS(
d
),
LAMBDA(
r,
c,
LET(
c,
INDEX(
d,
,
c
),
s,
SEQUENCE(
n
),
INDEX(
PROB(
s,
c/SUM(
c
),
,
s
)*SUM(
c
),
r
)
)
)
),
pv,
DROP(
PIVOTBY(
HSTACK(
f(
SCAN(
,
A3:A6,
LAMBDA(
a,
v,
TEXTJOIN(
",",
,
a,
v
)
)
)
),
f(
B2:I2
)
),
f(
SCAN(
,
B1:I1,
LAMBDA(
a,
v,
IF(
v>0,
v,
a
)
)
)
),
f(
rt
),
SUM,
,
2,
-2,
0
),
-1
),
IF((SEQUENCE(
ROWS(
pv
)
)>2)*(pv=""),
"Total",
pv))Excel solution 5 for Unstack Groups for Summation, proposed by 🇰🇷 Taeyong Shin:
=IFNA(
REDUCE(
HSTACK(
A2,
"Category",
TOROW(
IFS(
B1:I1>0,
B1:I1
),
2
)
),
B3:B6,
LAMBDA(
a,
v,
LET(
m,
WRAPCOLS(
BYCOL(
v:I3,
SUM
),
2
),
VSTACK(
a,
HSTACK(
ARRAYTOTEXT(
TAKE(
v:A3,
,
1
)
),
TOCOL(
HSTACK(
B2:C2,
"Total"
)
),
VSTACK(
m,
BYCOL(
m,
SUM
)
)
)
)
)
)
),
""
)Excel solution 6 for Unstack Groups for Summation, proposed by Julian Poeltl:
=LET(
T,
A1:I6,
TQ,
TAKE(
DROP(
T,
,
1
),
1
),
TT,
DROP(
T,
2,
1
),
C,
LAMBDA(
Ar,
LET(
M,
MAKEARRAY(
2,
4,
LAMBDA(
A,
B,
INDEX(
Ar,
ROUNDUP(
A/2,
& 0
),
B*2-2+A
)
)
),
VSTACK(
M,
DROP(
TAKE(
M,
1
)+TAKE(
M,
2
),
1
)
)
)
),
AA,
C(
TT
),
BB,
C(
DROP(
TT,
1
)
),
CC,
C(
DROP(
TT,
2
)
),
DD,
C(
DROP(
TT,
3
)
),
Cat,
VSTACK(
"Category",
INDEX(
VSTACK(
"Sales",
"Bonus",
"Total"
),
MOD(
SEQUENCE(
12
)-1,
3
)+1
)
),
HSTACK(
VSTACK(
"Persons",
IF(
DROP(
Cat,
1
)="Sales",
INDEX(
SCAN(
,
TAKE(
T,
-4,
1
),
LAMBDA(
A,
B,
A&", "&B
)
),
ROUNDUP(
SEQUENCE(
12
)/3,
0
)
),
""
)
),
Cat,
VSTACK(
FILTER(
TQ,
TQ<>""
),
AA,
AA+BB,
AA+BB+CC,
AA+BB+CC+DD
)
)
)Excel solution 7 for Unstack Groups for Summation, proposed by Oscar Mendez Roca Farell:
=LET(
p,
A3:A6,
e,
UNIQUE(
B2:I2,
1
),
REDUCE(
HSTACK(
A2,
"Category",
TOROW(
B1:I1,
1
)
),
p,
LAMBDA(
k,
z,
LET(
d,
DROP(
REDUCE(
"",
e,
LAMBDA(
j,
y,
VSTACK(
j,
FILTER(
FILTER(
DROP(
REDUCE(
"",
I3:I6,
LAMBDA(
i,
x,
LET(
b,
B3:x,
VSTACK(
i,
MMULT(
SEQUENCE(
,
ROWS(
b
)
)^0,
b
)
)
)
)
),
1
),
p=z
),
B2:I2=y
)
)
)
),
1
),
IFNA(
VSTACK(
k,
HSTACK(
ARRAYTOTEXT(
TAKE(
p,
XMATCH(
z,
p
)
)
),
VSTACK(
TOCOL(
e
),
"Total"
),
VSTACK(
d,
MMULT(
{1,
1},
d
)
)
)
),
""
)
)
)
)
)Excel solution 8 for Unstack Groups for Summation, proposed by Sunny Baggu:
=LET(
_a,
A3:A6,
_f,
SCAN(
"",
B1:I1,
LAMBDA(
a,
v,
IF(
v = "",
a,
v
)
)
),
_q,
TOROW(
B1:I1,
1
),
REDUCE(
HSTACK(
A2,
"Category",
_q
),
SEQUENCE(
ROWS(
_a
)
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_v,
BYCOL(
TAKE(
B3:I6,
XMATCH(
INDEX(
_a,
y,
1
),
_a
)
),
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
XLOOKUP(
_q,
_f,
_v
),
_sb,
XLOOKUP(
_q,
_f,
_v,
,
,
-1
),
_st,
BYCOL(
VSTACK(
_sv,
_sb
),
LAMBDA(
b,
SUM(
b
)
)
),
IFNA(
HSTACK(
ARRAYTOTEXT(
TAKE(
_a,
y,
)
),
HSTACK(
VSTACK(
B2,
C2,
"Total"
),
VSTACK(
_sv,
_sb,
_st
)
)
),
""
)
)
)
)
)
)Excel solution 9 for Unstack Groups for Summation, proposed by LEONARD OCHEA 🇷🇴:
=LET(
h,
B1:I1,
i,
B2:I2,
v,
A3:A6,
d,
B3:I6,
s,
SEQUENCE(
ROWS(
v
)
),
m,
MMULT(
N(
s>=TOROW(
s
)
),
d
),
F,
LAMBDA(
x,
TOCOL(
IF(
m,
x
)
)
),
DROP(
PIVOTBY(
HSTACK(
F(
MAP(
s,
LAMBDA(
a,
ARRAYTOTEXT(
TAKE(
v,
a
)
)
)
)
),
F(
i
)
),
F(
h&HSTACK(
"",
DROP(
h,
,
-1
)
)
),
F(
m
),
SUM,
0,
2,
,
0
),
-1
)
)Excel solution 10 for Unstack Groups for Summation, proposed by Md. Zohurul Islam:
=LET(
a,
B2:I2,
b,
B3:I6,
c,
SCAN(
,
A3:A6,
LAMBDA(
x,
y,
IF(
y<>x,
TEXTJOIN(
", ",
,
x,
y
),
y
)
)
),
F,
LAMBDA(
u,
v,
REDUCE(
u,
v,
LAMBDA(
x,
y,
LET(
a,
TEXTSPLIT(
y,
,
", "
),
b,
SCAN(
0,
a,
SUM
),
IFNA(
HSTACK(
x,
b
),
x
)
)
)
)
),
p,
BYCOL(
FILTER(
b,
a="Sales"
),
ARRAYTOTEXT
),
q,
BYCOL(
FILTER(
b,
a="Bonus"
),
ARRAYTOTEXT
),
r,
HSTACK(
c,
F(
"Sales",
p
)
),
s,
HSTACK(
c,
F(
"Bonus",
q
)
),
w,
SORT(
VSTACK(
r,
s
),
1,
1
),
wa,
DROP(
REDUCE(
"",
UNIQUE(
TAKE(
w,
,
1
)
),
LAMBDA(
x,
y,
LET(
a,
FILTER(
w,
TAKE(
w,
,
1
)=y
),
b,
BYCOL(
DROP(
a,
,
2
),
SUM
),
c,
HSTACK(
"",
"Total",
b
),
d,
VSTACK(
a,
c
),
e,
VSTACK(
x,
d
),
e
)
)
),
1
),
wb,
SCAN(
,
TAKE(
wa,
,
1
),
LAMBDA(
x,
y,
IF(
y=x,
"",
y
)
)
),
wc,
HSTACK(
wb,
DROP(
wa,
,
1
)
),
wd,
VSTACK(
HSTACK(
A2,
"Category",
"Q"&SEQUENCE(
,
4
)
),
wc
),
wd
)Excel solution 11 for Unstack Groups for Summation, proposed by Asheesh Pahwa:
=DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
A3:A6
)
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
I,
TAKE(
L7:S10,
INDEX(
S14:S17,
y
)
),
sq,
SEQUENCE(
,
COLUMNS(
I
)
),
ie,
ISEVEN(
sq
),
f,
FILTER(
I,
NOT(
ie
)
),
fl,
FILTER(
I,
ie
),
vs,
VSTACK(
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
),
BYCOL(
fl,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
HSTACK(
INDEX(
SCAN(
,
A3:A6,
LAMBDA(
x,
y,
x&","&y
)
),
y,
),
VSTACK(
B13:B15
),
VSTACK(
vs,
BYCOL(
vs,
LAMBDA(
x,
SUM(
x
)
)
)
)
),
""
)
)
)
)
),
1
)Excel solution 12 for Unstack Groups for Summation, proposed by Peter Bartholomew:
= LET(
accumulatedColumnsϑ,
BYCOL(
amounts,
LAMBDA(
column,
THUNK(
SCAN(
0,
column,
LAMBDA(
a,
v,
a+v
)
)
)
)
),
salesϑ,
TAKE(
WRAPCOLS(
accumulatedColumnsϑ,
2
),
1
),
bonusesϑ,
TAKE(
WRAPCOLS(
accumulatedColumnsϑ,
2
),
-1
),
quarterϑ,
MAP(
salesϑ,
bonusesϑ,
LAMBDA(
sϑ,
bϑ,
THUNK(
TOCOL(
HSTACK(
sϑ(),
bϑ(),
sϑ()+bϑ()
)
)
)
)
),
DROP(
REDUCE(
"",
quarterϑ,
LAMBDA(
acc,
qϑ,
HSTACK(
acc,
qϑ()
)
)
),
,
1
)
)
It works,
but Microsoft has made life unnecessarily complicated in order to maintain backward compatibility with solutions that I wouldn't touch with a barge-pole.
BTW,
the function THUNK is simply
= LAMBDA(
x,
LAMBDA(
x
)
)Excel solution 13 for Unstack Groups for Summation, proposed by Pieter de Bruijn:
=REDUCE(
HSTACK(
"Persons",
"Category",
"Q"&COLUMN(
A:D
)
),
A3:A6,
LAMBDA(
r,
a,
LET(
c,
A3:A6<=a,
IFNA(
VSTACK(
r,
HSTACK(
ARRAYTOTEXT(
TOCOL(
A3:a,
1
)
),
{"Sales";"Bonus";"Total"},
DROP(
REDUCE(
0,
{2,
4,
6,
8},
LAMBDA(
x,
y,
HSTACK(
x,
VSTACK(
SUM(
INDEX(
A3:I6,
,
y
)*c
),
SUM(
INDEX(
A3:I6,
,
y+1
)*c
),
SUM(
CHOOSECOLS(
A3:I6,
y,
y+1
)*c
)
)
)
)
),
,
1
)
)
),
""
)
)
)
)Solving the challenge of Unstack Groups for Summation with Python in Excel
Python in Excel solution 1 for Unstack Groups for Summation, proposed by Abdallah Ally:
https://github.com/abdallahdataguy/Excel_BI_Challenges/blob/main/Power_Query_Challenge_193.py
Solving the challenge of Unstack Groups for Summation with R
R solution 1 for Unstack Groups for Summation, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(unpivotr)
path = "Power Query/PQ_Challenge_193.xlsx"
input = read_xlsx(path, range = "A1:I6", col_names = FALSE)
test = read_xlsx(path, range = "A12:F24")
result = input %>%
as_cells() %>%
behead("up-left", "Quarter") %>%
behead("up", "Category") %>%
behead("left", "Persons") %>%
select(Persons, Quarter, Category, chr) %>%
pivot_wider(names_from = Category, values_from = chr) %>%
mutate(across(c(Sales, Bonus), as.numeric),
Total = Sales + Bonus) %>%
pivot_longer(cols = Sales:Total, names_to = "Category", values_to = "Value") %>%
pivot_wider(names_from = Quarter, values_from = Value) %>%
mutate(across(c(Q1:Q4), cumsum), .by = Category) %>%
mutate(Persons = accumulate(Persons, ~ paste(.x, .y, sep = ", "))[match(Persons, unique(Persons))], .by = Category) %>%
mutate(Persons = ifelse(Category == "Sales", Persons, NA_character_))
identical(result, test)
#> [1] TRUE
R solution 2 for Unstack Groups for Summation, proposed by Anil Kumar Goyal:
library(openxlsx)
library(tidyverse)
df <-
openxlsx::read.xlsx(
"PQ/PQ_Challenge_193.xlsx",
colNames = FALSE,
cols = 1:9,
rows = 1:6,
fillMergedCells = TRUE
)
df %>%
set_names(df %>%
slice(1:2) %>%
summarise(across(everything(), str_flatten, collapse = "_"))) %>%
slice(-1:-2) %>%
mutate(across(where(is.numeric), ~ accumulate(., `+`))) %>%
mutate(across(1, ~ accumulate(., str_c, sep = ", "))) %>%
pivot_longer(cols = -1, names_sep = "_",
names_to = c(".value", "Category"),
values_transform = as.numeric) %>%
rename(Persons = Quarters_Persons) %>%
group_split(Persons) %>%
map_dfr(~janitor::adorn_totals(., name = "", fill = "Total"))
&