New table from combination of two not related tables

[maclura]

Hi,

I have 2 tables like

Table1 = #table({"column1"},{{"1"},{"2"},{"3"}})

Table2 = #table({"column1"},{{"a"},{"b"}})

I would like to get this result

Table3 = #table({"column1","Column2"},{{"1","a"},{"2","a"},{"3","a"},{"1","b"},{"2","b"},{"3","b"}})

what is the best approach with M?

thank you

m 

[jppv20 · Accepted solution]

Hi @maclura ,

This should work:

Go to PowerQuery, in both tables create a column which equals 1 for every row:

Go to merge queries, select the columns with 1 for every row and choose full outer join.

Expand column 2, this will be the result:

If I answered your question, please mark it as a solution to help other members find it more quickly.

[AlexisOlson · Accepted solution]

Another way to do this is to define a custom column "column2" on Table1 as Table2[column1].

Then click the expand icon in the upper right corner of the above.

[maclura]

Thank you @AlexisOlson , it does work and it's even more elegant and versatile compared to the previous solution. You can choose if to expand the second column in new rows, or replicate the merging of the values of the second table in each row of the first table. That's powerful!

Thank you so much

[maclura]

It does work, thank you @jppv20 

[jppv20 · Accepted solution]

Hi @maclura ,

This should work:

Go to PowerQuery, in both tables create a column which equals 1 for every row:

Go to merge queries, select the columns with 1 for every row and choose full outer join.

Expand column 2, this will be the result:

If I answered your question, please mark it as a solution to help other members find it more quickly.