How to join all rows from one table against each row in another table-CodePudding

I am trying to find a way to efficiently join two tables of data together that don't have any common data in excel.

for example:

name
A
B
C

number
1
2
3

I then want the final product to be:

name	number
A	1
A	2
A	3
B	1
B	2
B	3
C	1
C	2
C	3

CodePudding user response：

With Power Query: assuming your first table is Table1 and your second Table2, create the following query:

let
  Source             = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
  #"Changed Type"    = Table.TransformColumnTypes(Source, {{"name", type text}}),
  #"Added Custom"    = Table.AddColumn(#"Changed Type", "Custom", each Table2),
  #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"number"}, {"number"})
in
  #"Expanded Custom"

CodePudding user response：

If you have Excel 365 you can also use this formula:

=LET(
colA,A2:A6,
colB,B2:B5,
cntRows1,COUNTA(colA),
cntRows2,COUNTA(colB),
maxRows,MAX(cntRows1,cntRows2),
MAKEARRAY(cntRows1*cntRows2,2,LAMBDA(r,c,
       CHOOSE(IF(ISODD(c),1,2),
              INDEX(colA,ROUNDUP(r/maxRows,0)),
              INDEX(colB,ROUNDUP(r/maxRows,0))
             ))))

You have to adjust the ranges for colA and colB to your needs.