Generate the Cartesian product as shown. For Power Query solutions (not for Excel solutions) – Number of entries in columns might change. Hence solution has to be flexible enough to accommodate the changes.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 34
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Cartesian product of values with Power Query
Power Query solution 1 for Generate Cartesian product of values, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Cart = Table.RemoveColumns(List.Accumulate(Table.ColumnNames(Source),hashtag#table({" "},{{0}}),(s,l)=>
Table.ExpandListColumn(Table.AddColumn(s,l,each List.RemoveNulls(Table.Column(Source,l))), l) )," ")
in
Cart
Power Query solution 2 for Generate Cartesian product of values, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Manager = List.Select(Source[Manager], each _ <> null),
Region = List.Select(Source[Region], each _ <> null),
Country = List.Select(Source[Country], each _ <> null),
TablaCombinada = Table.FromColumns({{Country}, {Region}, {Manager}}, Table.ColumnNames(Source)),
FinalSolution = Table.ExpandListColumn(
Table.ExpandListColumn(Table.ExpandListColumn(TablaCombinada, "Country"), "Manager"),
"Region"
)
in
FinalSolutionPower Query solution 3 for Generate Cartesian product of values, proposed by Luan Rodrigues:
let
Fonte = Data,
a = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = List.RemoveNulls(Fonte[Region]),
b = List.RemoveNulls(Fonte[Manager]),
c = List.Count(List.RemoveNulls(Fonte[Manager])),
d = List.Repeat(a, c),
e = List.Repeat(b, c - 1),
f = List.Sort(e)
]
)[[Country], [Personalizar]],
b = Table.ExpandRecordColumn(a, "Personalizar", {"d", "f"}, {"Region", "Manager1"}),
c = Table.ExpandListColumn(b, "Region"),
d = Table.Group(
c,
{"Country"},
{{"Contagem", each Table.AddIndexColumn(_, "Rank", 0, 1), type table}}
)[[Contagem]],
e = Table.ExpandTableColumn(
d,
"Contagem",
Table.ColumnNames(d[Contagem]{0}),
Table.ColumnNames(d[Contagem]{0})
),
Result = Table.AddColumn(e, "Manager", each [Manager1]{[Rank]})[[Country], [Region], [Manager]]
in
ResultPower Query solution 4 for Generate Cartesian product of values, proposed by Brian Julius:
let
Source = CrossjoinRaw,
Cross1 = Table.ExpandListColumn(
Table.AddColumn(Source, "Region1", each CrossjoinRaw[Region]),
"Region1"
),
Cross2 = Table.ExpandListColumn(
Table.AddColumn(Cross1, "Manager1", each CrossjoinRaw[Manager]),
"Manager1"
),
RemoveFilterRename = Table.RenameColumns(
Table.SelectRows(
Table.RemoveColumns(Cross2, {"Region", "Manager"}),
each ([Region1] <> "") and ([Manager1] <> "")
),
{{"Region1", "Region"}, {"Manager1", "Manager"}}
)
in
RemoveFilterRenamePower Query solution 5 for Generate Cartesian product of values, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Country", type text}, {"Region", type text}, {"Manager", type text}}
),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Region] <> null)),
Region = #"Filtered Rows"[Region],
Custom1 = #"Changed Type",
#"Filtered Rows1" = Table.SelectRows(Custom1, each ([Manager] <> null)),
Manager = #"Filtered Rows1"[Manager],
Custom2 = #"Changed Type",
#"Removed Other Columns" = Table.SelectColumns(Custom2, {"Country"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Regions", each Region),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Manager", each Manager),
#"Expanded Regions" = Table.ExpandListColumn(#"Added Custom1", "Regions"),
#"Expanded Manager" = Table.ExpandListColumn(#"Expanded Regions", "Manager")
in
#"Expanded Manager"Power Query solution 6 for Generate Cartesian product of values, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "Cartesian"]}[Content],
Cartesian = Table.AddColumn(
Table.AddColumn(Source, "Regions", each List.RemoveNulls(Source[Region]))[[Country], [Regions]],
"Managers",
each List.RemoveNulls(Source[Manager])
)[[Country], [Regions], [Managers]],
Regions = Table.ExpandListColumn(Cartesian, "Regions"),
Managers = Table.ExpandListColumn(Regions, "Managers")
in
ManagersPower Query solution 7 for Generate Cartesian product of values, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
GetLists = Table.FromRecords(
Table.TransformRows(
Source,
let
reg = List.RemoveNulls(Source[Region]),
mgr = List.RemoveNulls(Source[Manager])
in
each [Country = [Country], Region = reg, Manager = mgr]
)
),
Expand1 = Table.ExpandListColumn(GetLists, "Manager"),
Expand2 = Table.ExpandListColumn(Expand1, "Region")
in
Expand2Power Query solution 8 for Generate Cartesian product of values, proposed by Venkata Rajesh:
let
Source = Data,
Merge = Table.FromColumns(
{{Source[Country]}, {List.RemoveNulls(Source[Region])}, {List.RemoveNulls(Source[Manager])}},
{"Country", "Region", "Manager"}
),
Output = List.Accumulate(
Table.ColumnNames(Source),
Merge,
(state, current) => Table.ExpandListColumn(state, current)
)
in
OutputPower Query solution 9 for Generate Cartesian product of values, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ColList = List.Skip(Table.ColumnNames(Source)),
Start = Table.Distinct(
Table.SelectColumns(Source, List.Difference(Table.ColumnNames(Source), ColList))
),
Result = List.Accumulate(
ColList,
Start,
(S, C) =>
Table.ExpandListColumn(
Table.AddColumn(S, C, each List.RemoveNulls(Table.Column(Source, C))),
C
)
)
in
ResultPower Query solution 10 for Generate Cartesian product of values, proposed by Sandeep Marwal:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Custom",
each [R = Source[Region], M = Source[Manager]]
),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"R", "M"}, {"R", "M"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom", {"Region", "Manager"}),
#"Expanded M" = Table.ExpandListColumn(#"Removed Columns", "M"),
#"Expanded R" = Table.ExpandListColumn(#"Expanded M", "R"),
#"Filtered Rows" = Table.SelectRows(#"Expanded R", each ([M] <> null) and ([R] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows", {{"R", "Region"}, {"M", "Manager"}})
in
#"Renamed Columns"Power Query solution 11 for Generate Cartesian product of values, proposed by Paolo Pozzoli:
let
Origine = Excel.CurrentWorkbook(){[Name = "tbl_Country"]}[Content],
#"Modificato tipo" = Table.TransformColumnTypes(Origine, {{"Country", type text}}),
#"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "Region", each Region),
#"Aggiunta colonna personalizzata1" = Table.AddColumn(
#"Aggiunta colonna personalizzata",
"Manager",
each Manager
),
#"Tabella Region espansa" = Table.ExpandTableColumn(
#"Aggiunta colonna personalizzata1",
"Region",
{"Region"},
{"Region.1"}
),
#"Tabella Manager espansa" = Table.ExpandTableColumn(
#"Tabella Region espansa",
"Manager",
{"Manager"},
{"Manager.1"}
)
in
#"Tabella Manager espansa"Solving the challenge of Generate Cartesian product of values with Excel
Excel solution 1 for Generate Cartesian product of values, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
A2:D9,
m,
MMULT(
SEQUENCE(
,
ROWS(
z
)
)^0,
N(
z>0
)
),
n,
PRODUCT(
m
),
INDEX(
z,
MOD(
SEQUENCE(
n,
,
0
)/n*SCAN(
1,
m,
LAMBDA(
a,
v,
a*v
)
),
m
)+1,
SEQUENCE(
,
COLUMNS(
z
)
)
)
)Excel solution 2 for Generate Cartesian product of values, proposed by محمد حلمي:
=LET(
a,
TOCOL(
TOCOL(
A2:A5&"-"&
TOROW(
B2:B3
)
)&"|"&TOROW(
C2:C4
)
),
SORT(
HSTACK(
TEXTSPLIT(
a,
"-"
),
TEXTSPLIT(
TEXTAFTER(
a,
"-"
),
"|"
),
TEXTAFTER(
a,
"|"
)
),
{1,
3}
)
)Excel solution 3 for Generate Cartesian product of values, proposed by محمد حلمي:
=LET(
a,
TOCOL(
TOCOL(
A2:A5&"-"&TOROW(
B2:B3
)
)&"-"&
TOROW(
C2:C4
)
),
SORT(
HSTACK(
TEXTSPLIT(
a,
"-"
),
TEXTSPLIT(
TEXTAFTER(
a,
"-",
1
),
"-"
),
TEXTAFTER(
a,
"-",
-1
)
),
{1,
3}
)
)Excel solution 4 for Generate Cartesian product of values, proposed by 🇰🇷 Taeyong Shin:
=LET(
Split,
SORT(
TEXTSPLIT(
TEXTJOIN(
";",
,
TOCOL(
B2:B5,
1
) & ", " & TOROW(
C2:C5,
1
)
),
", ",
";"
),
2
),
Func,
LAMBDA(
x,
HSTACK(
EXPAND(
x,
ROWS(
Split
),
,
x
),
Split
)
),
REDUCE(
Func(
A2
),
A3:A5,
LAMBDA(
a,
b,
VSTACK(
a,
Func(
b
)
)
)
)
)Excel solution 5 for Generate Cartesian product of values, proposed by Kris Jaganah:
=LET(
a,
Table1[Country],
b,
Table1[Region],
c,
Table1[Manager],
d,
TRIM(
TRANSPOSE(
TEXTSPLIT(
REPT(
ARRAYTOTEXT(
a
)&", ",
COUNTA(
b
)*COUNTA(
c
)
),
","
)
)
),
e,
SORT(
FILTER(
d,
d<>""
),
1,
1
),
f,
TRANSPOSE(
TRIM(
TEXTSPLIT(
SUBSTITUTE(
REPT(
ARRAYTOTEXT(
b
),
COUNTA(
e
)/COUNTA(
b
)
),
", ,",
","
),
","
)
)
),
g,
FILTER(
f,
f<>""
),
h,
TRANSPOSE(
TRIM(
TEXTSPLIT(
SUBSTITUTE(
REPT(
ARRAYTOTEXT(
c
),
COUNTA(
e
)/COUNTA(
c
)
),
", ,",
","
),
","
)
)
),
i,
FILTER(
h,
h<>""
),
j,
HSTACK(
e,
g,
i
),
k,
SORTBY(
j,
e,
1,
i,
1,
g,
1
),
k
)Excel solution 6 for Generate Cartesian product of values, proposed by Kris Jaganah:
=LET(
a,
A2:A5,
b,
B2:B5,
c,
C2:C5,
d,
TRIM(
TRANSPOSE(
TEXTSPLIT(
REPT(
ARRAYTOTEXT(
a
)&", ",
COUNTA(
b
)*COUNTA(
c
)
),
","
)
)
),
e,
SORT(
FILTER(
d,
d<>""
),
1,
1
),
f,
TRANSPOSE(
TRIM(
TEXTSPLIT(
SUBSTITUTE(
REPT(
ARRAYTOTEXT(
b
),
COUNTA(
e
)/COUNTA(
b
)
),
", ,",
","
),
","
)
)
),
g,
FILTER(
f,
f<>""
),
h,
TRANSPOSE(
TRIM(
TEXTSPLIT(
SUBSTITUTE(
REPT(
ARRAYTOTEXT(
c
),
COUNTA(
e
)/COUNTA(
c
)
),
", ,",
","
),
","
)
)
),
i,
FILTER(
h,
h<>""
),
j,
HSTACK(
e,
g,
i
),
k,
SORTBY(
j,
e,
1,
i,
1,
g,
1
),
k
)Excel solution 7 for Generate Cartesian product of values, proposed by Alejandro Campos:
=L&ET(
ea,
TOCOL(
A2:A5 & "-" & TOROW(
B2:B3
)
),
eb,
TOCOL(
ea & "-" & TOROW(
C2:C4
)
),
cn,
CONCAT(
eb & "_"
),
SORT(
DROP(
TEXTSPLIT(
cn,
"-",
"_"
),
-1
),
{1,
3}
)
)Excel solution 8 for Generate Cartesian product of values, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_d,
A2:C5,
_cnt,
BYCOL(
_d,
LAMBDA(
a,
SUM(
1 - ISBLANK(
a
)
)
)
),
_tr,
PRODUCT(
_cnt
),
_rseq,
SEQUENCE(
_tr,
,
0
),
_cseq,
SEQUENCE(
1,
COLUMNS(
_cnt
)
),
_c1,
SCAN(
1,
_cnt,
LAMBDA(
a,
b,
a * b
)
),
_c2,
_c1 / _cnt,
_rep1,
QUOTIENT(
_rseq,
_c2
),
_rep2,
MOD(
_rep1,
_cnt
) + 1,
_s,
SORT(
_rep2,
_cseq
),
_r,
INDEX(
_d,
_s,
_cseq
),
_r
)Excel solution 9 for Generate Cartesian product of values, proposed by Jardiel Euflázio:
=LET(
a,
A2:A5,
b,
B2:B3,
c,
C2:C4,
CHOOSE(
{1,
2,
3},
SORT(
TEXTSPLIT(
REPT(
TEXTJOIN(
" ",
,
a
)&" ",
ROWS(
b
)*ROWS(
c
)
),
,
" ",
1
)
),
TEXTSPLIT(
REPT(
TEXTJOIN(
" ",
,
b
)&" ",
ROWS(
a
)*ROWS(
c
)
),
,
" ",
1
),
TEXTSPLIT(
REPT(
TEXTJOIN(
" ",
,
c
)&" ",
ROWS(
a
)*ROWS(
b
)
),
,
" ",
1
)
)
)Excel solution 10 for Generate Cartesian product of values, proposed by Murat Hasanoglu:
=INDEX(A$2:A$100;
MOD((ROUNDUP((ROW()-1)/(COUNTA(
A:A
)-1);
0))-1;
(COUNTA(
A:A
)-1))+1)
or
=INDEX($A$2:$C$100;
MOD((ROUNDUP((ROW()-1)/(COUNTA(
A:A
)-1);
0))-1;
(COUNTA(
A:A
)-1))+1;
COLUMNS(
$E$2:E2
))Solving the challenge of Generate Cartesian product of values with SQL
SQL solution 1 for Generate Cartesian product of values, proposed by Zoran Milokanović:
SELECT
DC.COUNTRY
,DR.REGION
,DM.MANAGER
FROM DATA DC
CROSS JOIN DATA DR
CROSS JOIN DATA DM
WHERE
DC.COUNTRY <> ''
AND DR.REGION <> ''
AND DM.MANAGER <> ''
ORDER BY
1, 3, 2
;
&&