Merge the 2 tables and pivot them on Item with Total Row and Total Column. Group and Item assignments for Stock are for each element. Hence, Group: A, F and Item: Item2, Item1 and Stock: 370 means A – Item2 – 370 A – Item1 – 370 F – Item2 – 370 F – Item1 – 370
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 213
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Matrix Merge With Totals with Power Query
_x000D_Power Query solution 1 for Matrix Merge With Totals, proposed by Zoran Milokanović:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
M = List.TransformMany(
Table.ToRows(Source("Table1") & Source("Table2")),
each List.TransformMany(Text.Split(_{0}, ", "), (i) => Text.Split(_{1}, ", "), (i, _) => {i, _}),
(i, _) => _ & {i{2}}
),
Z = List.Zip(M),
T = "Total",
H = List.Distinct(Z{1}) & {T},
S = Table.FromRows(
List.Transform(
List.Distinct(Z{0}) & {T},
(r) => {r}
& List.Transform(
H,
(c) =>
List.Sum(
List.Zip(List.Select(M, each (r = T or _{0} = r) and (c = T or _{1} = c))){2}? ?? {}
)
)
),
{"Group"} & H
)
in
SPower Query solution 2 for Matrix Merge With Totals, proposed by Zoran Milokanović:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
T = Source("Table1") & Source("Table2"),
S = each Text.Split(_, ", "),
L = Table.TransformColumns(T, {{"Group", S}, {"Item", S}}),
G = Table.ExpandListColumn(L, "Group"),
I = Table.ExpandListColumn(G, "Item"),
P = Table.Pivot(I, List.Distinct(I[Item]), "Item", "Stock", List.Sum),
C = Table.AddColumn(P, "Total", each List.Sum(List.Skip(Record.ToList(_)))),
F = C
& Table.FromRows(
{{"Total"} & List.Transform(List.Skip(Table.ToColumns(C)), List.Sum)},
Table.ColumnNames(C)
)
in
FPower Query solution 3 for Matrix Merge With Totals, proposed by Kris Jaganah:
let
S = (x) => Excel.CurrentWorkbook(){[Name = x]}[Content],
A = S("Table1") & S("Table2"),
B = List.Accumulate(
{"Item", "Group"},
A,
(y, z) => Table.ExpandListColumn(Table.TransformColumns(y, {z, each Text.Split(_, ", ")}), z)
),
C = Table.Group(B, {"Item", "Group"}, {"Stock", each List.Sum([Stock])}),
D = Table.Group(C, {"Item"}, {"Total", each List.Sum([Stock])}),
E = Table.AddColumn(Table.Pivot(D, D[Item], "Item", "Total"), "Group", each "Total"),
F = Table.Pivot(C, List.Distinct(C[Item]), "Item", "Stock") & E,
G = Table.AddColumn(F, "Total", each List.Sum(List.Skip(Record.ToList(_))))
in
GPower Query solution 4 for Matrix Merge With Totals, proposed by Aditya Kumar Darak 🇮🇳:
let
Tables = Excel.CurrentWorkbook()[Content],
Combine = Table.Combine(Tables),
ToRecords = Table.ToRecords(Combine),
Transform = List.TransformMany(
ToRecords,
each Text.Split([Group], ", "),
(x, y) => {y, Text.Split(x[Item], ", "), x[Stock]}
),
FromRows = Table.FromRows(Transform, Table.ColumnNames(Combine)),
Expand = Table.ExpandListColumn(FromRows, "Item"),
Group = Table.Group(Expand, "Item", {{"Group", each "Total"}, {"Stock", each List.Sum([Stock])}}),
Pivot = Table.Pivot(Expand & Group, List.Distinct(Group[Item]), "Item", "Stock", List.Sum),
Return = Table.AddColumn(Pivot, "Total", each List.Sum(List.Skip(Record.ToList(_))))
in
ReturnPower Query solution 5 for Matrix Merge With Totals, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Tbls = Table.Combine(
List.Transform(
{"Table1", "Table2"},
(x) =>
Table.AddColumn(
Excel.CurrentWorkbook(){[Name = x]}[Content],
"A",
each
let
a = List.Transform({[Group], [Item]}, each Text.Split(_, ", ")),
b = Table.FromRows({a}, {"Group", "B"}),
c = List.Accumulate({"Group", "B"}, b, (s, c) => Table.ExpandListColumn(s, c))
in
c
)
)
)[[A], [Stock]],
Expand = Table.ExpandTableColumn(Tbls, "A", Table.ColumnNames(Tbls[A]{0})),
Pivot = Table.Pivot(Expand, List.Distinct(Expand[B]), "B", "Stock", List.Sum),
TotalCol = Table.AddColumn(Pivot, "Total", each List.Sum(List.Skip(Record.ToList(_)))),
Sol = TotalCol
& Table.FromRows(
{{"Total"} & List.Transform(List.Skip(Table.ToColumns(TotalCol)), List.Sum)},
Table.ColumnNames(TotalCol)
)
in
SolPower Query solution 6 for Matrix Merge With Totals, proposed by Luan Rodrigues:
let
Fonte = Tabela1&Tabela2,
add = Table.TransformColumns(Fonte, {
{"Group", each Text.Split(_,", ")},
{"Item", each Text.Split(_,", ")}
} ),
exp = List.Accumulate({"Group","Item"},add,(s,c)=> Table.ExpandListColumn(s, c)),
grp = Table.Group(exp, {"Group"}, {"tab", each
let
t = [Group],
a = Table.PromoteHeaders(Table.Transpose(Table.Group(_,"Item",{"tab", each List.Sum(_[Stock]) }))),
b = Table.AddColumn(a, "Total", each List.Sum(Record.FieldValues(_)) ) in Table.AddColumn(b, "Group", each t{0}) } )[tab],
cmb = Table.Combine(grp),
srt = Table.SelectColumns(cmb, List.Sort(Table.ColumnNames(cmb))),
res = srt & hashtag#table({"Group"}& List.Skip(Table.ColumnNames(srt),1),{{"Total"} & List.Transform(Table.ToColumns(Table.RemoveColumns(srt,{"Group"})),List.Sum)})
in
res
Power Query solution 7 for Matrix Merge With Totals, proposed by Abdallah Ally:
let
Table1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Table2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Append = Table.Combine(
List.Transform(
{Table1, Table2},
each [
a = Table.TransformColumns(
_,
{{"Group", each Text.Split(_, ", ")}, {"Item", each Text.Split(_, ", ")}}
),
b = Table.ExpandListColumn(a, "Group"),
c = Table.ExpandListColumn(b, "Item")
][c]
)
),
Pivot = Table.Pivot(Append, List.Distinct(Append[Item]), "Item", "Stock", List.Sum),
AddColumn = Table.AddColumn(Pivot, "Total", each List.Sum(List.Skip(Record.ToList(_)))),
LastRecord = Table.FromRows(
{{"Total"} & List.Transform(List.Skip(Table.ToColumns(AddColumn)), List.Sum)},
Table.ColumnNames(AddColumn)
),
Result = Table.Combine({AddColumn, LastRecord})
in
ResultPower Query solution 8 for Matrix Merge With Totals, proposed by Abdallah Ally:
let
Table1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Table2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Transform = Table.TransformColumns(
Table1 & Table2,
{{"Group", each Text.Split(_, ", ")}, {"Item", each Text.Split(_, ", ")}}
),
Expand1 = Table.ExpandListColumn(Transform, "Group"),
Expand2 = Table.ExpandListColumn(Expand1, "Item"),
Pivot = Table.Pivot(Expand2, List.Distinct(Expand2[Item]), "Item", "Stock", List.Sum),
AddColumn = Table.AddColumn(Pivot, "Total", each List.Sum(List.Skip(Record.ToList(_)))),
Values = List.Transform(List.Skip(Table.ToColumns(AddColumn)), List.Sum),
LastRec = Table.FromRows({{"Total"} & Values}, Table.ColumnNames(AddColumn)),
Result = AddColumn & LastRec
in
ResultSolving the challenge of Matrix Merge With Totals with Excel
_x000D_Excel solution 1 for Matrix Merge With Totals, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
REDUCE(
VSTACK(
A3:C8,
A13:C16
),
{1,
2},
LAMBDA(
a,
i,
LET(
B,
LAMBDA(
b,
a,
LET(
n,
ROWS(
a
),
L,
LAMBDA(
j,
IF(
i=j,
TEXTSPLIT(
INDEX(
a,
1,
j
),
,
", "
),
INDEX(
a,
1,
j
)
)
),
IF(
n=1,
CHOOSE(
{1,
2,
3},
L(
1
),
L(
2
),
L(
3
)
),
VSTACK(
b(
b,
TAKE(
a,
n/2
)
),
b(
b,
DROP(
a,
n/2
)
)
)
)
)
),
B(
B,
a
)
)
)
),
PIVOTBY(
TAKE(
z,
,
1
),
INDEX(
z,
,
2
),
DROP(
z,
,
2
),
SUM
)
)Excel solution 2 for Matrix Merge With Totals, proposed by Bo Rydobon 🇹🇭:
=LET(
b,
LAMBDA(
b,
a,
LET(
n,
ROWS(
a
),
IF(
n=1,
TEXTSPLIT(
CONCAT(
TEXTSPLIT(
@a,
", "
)&"-"&TEXTSPLIT(
INDEX(
a,
1,
2
),
,
", "
)&-DROP(
a,
,
2
)&"_"
),
"-",
"_",
1
),
VSTACK(
b(
b,
TAKE(
a,
n/2
)
),
b(
b,
DROP(
a,
n/2
)
)
)
)
)
),
z,
b(
b,
VSTACK(
A3:C8,
A13:C16
)
),
PIVOTBY(
TAKE(
z,
,
1
),
INDEX(
z,
,
2
),
--DROP(
z,
,
2
),
SUM
)
)Excel solution 3 for Matrix Merge With Totals, proposed by محمد حلمي:
=LET(
e,
A1:A16,
b,
B1:B16,
y,
"Total",
r,
LAMBDA(
x,
DROP(
UNIQUE(
TEXTSPLIT(
CONCAT(
x&", "
),
,
", "
)
),
2
)
),
i,
TOROW(
r(
b
)
),
j,
DROP(
r(
e
),
-2
),
s,
MAP(
j&i,
LAMBDA(
a,
SUM(
IFERROR(
FIND(
LEFT(
a
),
e
)^0*
FIND(
RIGHT(
a,
5
),
b
)^0*C1:C16,
)
)
)
),
x,
LAMBDA(
a,
SUM(
a
)
),
VSTACK(
HSTACK(
A2,
i,
y
),
HSTACK(
j,
s,
BYROW(
s,
x
)
),
HSTACK(
y,
BYCOL(
s,
x
),
SUM(
s
)
)
)
)Excel solution 4 for Matrix Merge With Totals, proposed by Julian Poeltl:
=LET(T,
A3:C8,
TG,
TAKE(
T,
,
1
),
TI,
CHOOSECOLS(
T,
2
),
TS,
DROP(
T,
,
2
),
TT,
A13:C16,
TTG,
TAKE(
TT,
,
1
),
TTI,
CHOOSECOLS(
TT,
2
),
TTS,
DROP(
TT,
,
2
),
UL,
LAMBDA(
A,
B,
UNIQUE(
TEXTSPLIT(
TEXTJOIN(
", ",
,
A,
B
),
,
", "
)
)
),
UG,
UL(
TG,
TTG
),
UI,
TOROW(
UL(
TI,
TTI
)
),
M,
IFERROR(MAP(UG&UI,
LAMBDA(A,
SUM(FILTER(VSTACK(
TS,
TTS
),
ISNUMBER((SEARCH(
LEFT(
A
),
VSTACK(
TG,
TTG
)
)*(SEARCH(
RIGHT(
A,
5
),
VSTACK(
TI,
TTI
)
)))))))),
""),
VSTACK(
HSTACK(
"Group",
UI,
"Total"
),
HSTACK(
UG,
M,
BYROW(
M,
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Total",
BYCOL(
M,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
M
)
)
))Excel solution 5 for Matrix Merge With Totals, proposed by Oscar Mendez Roca Farell:
=LET(
F,
LAMBDA(
i,
TEXTSPLIT(
i,
", ",
,
1
)
),
r,
DROP(
REDUCE(
"",
C3:C16,
LAMBDA(
i,
x,
LET(
r,
TAKE(
A3:x,
-1
),
VSTACK(
i,
IF(
{1,
0},
TOCOL(
TOCOL(
& F(
@+r
)
)&F(
INDEX(
r,
1,
2
)
)
),
MAX(
r
)
)
)
)
)
),
1
),
d,
FILTER(
r,
DROP(
r,
,
1
)>0
),
g,
UNIQUE(
F(
A3:A8
)
),
i,
UNIQUE(
F(
CONCAT(
B3:B8&", "
)
),
1
),
w,
WRAPROWS(
BYCOL(
IF(
TAKE(
d,
,
1
)=TOROW(
g&i
),
DROP(
d,
,
1
),
),
LAMBDA(
c,
SUM(
c
)
)
),
COUNTA(
i
)
),
t,
"Total",
VSTACK(
HSTACK(
A2,
i,
t
),
HSTACK(
g,
w,
MMULT(
w,
TOCOL(
1^N(
i
)
)
)
),
HSTACK(
t,
MMULT(
TOROW(
1^N(
g
)
),
w
),
SUM(
w
)
)
)
)Excel solution 6 for Matrix Merge With Totals, proposed by Sunny Baggu:
=LET(
_g,
UNIQUE(
TEXTSPLIT(
ARRAYTOTEXT(
A3:A8
),
,
", "
)
),
_i,
TOROW(
UNIQUE(
TEXTSPLIT(
ARRAYTOTEXT(
B3:B8
),
,
", "
)
)
),
_v,
MAKEARRAY(
ROWS(
_g
),
COLUMNS(
_i
),
LAMBDA(
r,
c,
LET(
_r,
INDEX(
_g,
r
),
_c,
INDEX(
_i,
,
c
),
SUM(
TOCOL(
ISNUMBER(
SEARCH(
_c,
B3:B16
)
) * ISNUMBER(
SEARCH(
_r,
A3:A16
)
) *
C3:C16,
3
)
)
)
)
),
_s1,
BYCOL(
_v,
LAMBDA(
a,
SUM(
a
)
)
),
_s2,
BYROW(
_v,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
VSTACK(
HSTACK(
"Group",
_i
),
HSTACK(
_g,
_v
)
),
VSTACK(
"Total",
_s2
)
),
HSTACK(
"Total",
_s1,
SUM(
_s1
)
)
)
)Excel solution 7 for Matrix Merge With Totals, proposed by LEONARD OCHEA 🇷🇴:
=LET(
V,
VSTACK,
C,
CHOOSECOLS,
D,
TEXTSPLIT,
U,
CONCAT,
m,
TEXTSPLIT(
U(
MAP(
V(
A3:A8,
A13:A16
),
V(
B3:B8,
B13:B16
),
V(
C3:C8,
C13:C16
),
LAMBDA(
a,
b,
c,
U(
D(
a,
", "
)&"*"&D(
b,
,
", "
)&"*"&c&"|"
)
)
)
),
"*",
"|",
1
),
PIVOTBY(
C(
m,
1
),
C(
m,
2
),
--C(
m,
3
),
SUM
)
)Excel solution 8 for Matrix Merge With Totals, proposed by Eddy Wijaya:
=LET(
f,
LAMBDA(
target,
LET(
db,
DROP(
REDUCE(
0,
target,
LAMBDA(
a,
v,
VSTACK(
a,
IFNA(
HSTACK(
IF(
{1,
0},
LET(
alphabet,
TEXTSPLIT(
v,
,
", "
),
item,
DROP(
REDUCE(
0,
OFFSET(
v,
0,
1
),
LAMBDA(
a,
i,
VSTACK(
a,
TEXTSPLIT(
i,
", "
)
)
)
),
1
),
TOCOL(
alphabet&", "&item
)
),
OFFSET(
v,
,
2,
1,
1
)
)
),
OFFSET(
v,
,
2,
1,
1
)
)
)
)
),
1
),
HSTACK(
DROP(
REDUCE(
0,
CHOOSECOLS(
db,
1
),
LAMBDA(
a,
t,
VSTACK(
a,
TEXTSPLIT(
t,
", "
)
)
)
),
1
),
CHOOSECOLS(
db,
-1
)
)
)
),
merge,
VSTACK(
f(
A3:A8
),
f(
A13:A16
)
),
group,
TAKE(
merge,
,
1
),
item,
CHOOSECOLS(
merge,
2
),
gi,
BYROW(
HSTACK(
group,
item
),
LAMBDA(
r,
CONCAT(
r
)
)
),
calc,
HSTACK(
UNIQUE(
gi
),
MAP(
UNIQUE(
gi
),
LAMBDA(
m,
SUM(
FILTER(
CHOOSECOLS(
merge,
-1
),
gi=m
)
)
)
)
),
calcArr,
XLOOKUP(
UNIQUE(
group
)&TOROW(
UNIQUE(
item
)
),
CHOOSECOLS(
calc,
1
),
CHOOSECOLS(
calc,
-1
),
0
),
allCalc,
IFNA(
HSTACK(
VSTACK(
calcArr,
BYCOL(
calcArr,
LAMBDA(
c,
SUM(
c
)
)
)
),
BYROW(
calcArr,
LAMBDA(
r,
SUM(
r
)
)
)
),
SUM(
calcArr
)
),
VSTACK(
HSTACK(
"Group",
TOROW(
UNIQUE(
item
)
),
"Total"
),
HSTACK(
VSTACK(
UNIQUE(
group
),
"Total"
),
allCalc
)
)
)Excel solution 9 for Matrix Merge With Totals, proposed by Aman Mashetty:
= "PQ_Challenge_213.xlsx"
# Load the specific columns and rows into DataFrame `df`
t1 = pd.read_excel(
path,
usecols="A:C",
nrows=6,
skiprows = 1
)
t2 = pd.read_excel(
path,
usecols="A:C",
nrows=6,
skiprows = 11
)
df = pd.concat(
[t1,
t2],
ignore_index=True
)
df['Group'] = df['Group'].str.split(
',
'
)
df['Item'] = df['Item'].str.split(
',
'
)
df = df.explode(
'Group'
).explode(
'Item'
)
df['Group'] = df['Group'].str.strip()
df['Item'] = df['Item'].str.strip()
pivot_table = df.pivot_table(
index='Group',
columns='Item',
values='Stock',
aggfunc='sum',
fill_value=0
)
pivot_table['Total'] = pivot_table.sum(
axis=1
)
total_row = pivot_table.sum(
axis=0
)
total_row.name = 'Total'
pivot_table = pivot_table.append(
total_row
)
pivot_table = pivot_table.reset_index()
cols = list(
pivot_table.columns
)
cols.remove(
'Total'
)
cols.append(
'Total'
)Solving the challenge of Matrix Merge With Totals with Python
_x000D_Python solution 1 for Matrix Merge With Totals, proposed by Konrad Gryczan, PhD:
Similar to Abdallah's
import pandas as pd
import numpy as np
path = "PQ_Challenge_213.xlsx"
T1 = pd.read_excel(path, usecols="A:C", skiprows=1, nrows=6)
T2 = pd.read_excel(path, usecols="A:C", skiprows=11, nrows=6)
test = pd.read_excel(path, usecols="F:K", skiprows=1, nrows=7).fillna(0)
test.columns = test.columns.str.replace(".1", "")
for col in test.columns[1:]:
test[col] = test[col].astype("int64")
T_full = pd.concat([T1, T2], ignore_index=True)
T_full = T_full.assign(Item=T_full.Item.str.split(", ")).explode("Item")
T_full = T_full.assign(Group=T_full.Group.str.split(", ")).explode("Group").reset_index(drop=True)
T_full = T_full.pivot_table(index="Group", columns="Item", values="Stock", aggfunc = "sum", fill_value=0, margins = True, margins_name = "Total").reset_index()
T_full.columns.name = None
print(T_full.equals(test)) # True
Solving the challenge of Matrix Merge With Totals with Python in Excel
_x000D_Python in Excel solution 1 for Matrix Merge With Totals, proposed by Alejandro Campos:
df1, df2 = xl("A2:C8", headers=True), xl("A12:C16", headers=True)
expand_rows = lambda df: pd.DataFrame([
{'Group': g, 'Item': i, 'Stock': row['Stock']}
for _, row in df.iterrows()
for g in row['Group'].split(', ')
for i in row['Item'].split(', ')
])
combined_df = pd.concat([expand_rows(df1), expand_rows(df2)])
pivot_table = pd.pivot_table(
combined_df, values='Stock', index='Group', columns='Item',
aggfunc='sum', fill_value='', margins=True, margins_name='Total'
)[['Item1', 'Item2', 'Item3', 'Item4', 'Total']].reset_index().rename_axis(None, axis=1)
pivot_table
Solving the challenge of Matrix Merge With Totals with R
_x000D_R solution 1 for Matrix Merge With Totals, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(janitor)
path = "Power Query/PQ_Challenge_213.xlsx"
T1 = read_excel(path, range = "A2:C8")
T2 = read_excel(path, range = "A12:C16")
test = read_excel(path, range = "F2:K9")
T_full = bind_rows(T1, T2) %>%
separate_rows(Item, sep = ", ") %>%
separate_rows(Group, sep = ", ") %>%
pivot_wider(names_from = Item, values_from = Stock, values_fn = sum) %>%
adorn_totals(c("row", "col"))
all.equal(test, T_full, check.attributes = FALSE)
#> [1] TRUE