Power Query Cross Join

[Anonymous]

I have the following two tables in Power Query (Product and Region)

Product

a

b

Country	Area	Town	House
England	Tyne and Wear	Sunderland	A1
England	Tyne and Wear	Sunderland	A1
Scotland	Ayrshire	Ayr	W1
Scotland	Ayrshire	Ayr	W2

I want to be able to cross join - show me all product for each area  - can anyone help e.g.

Country	Area	Town	House	Product
England	Tyne and Wear	Sunderland	A1	a
England	Tyne and Wear	Sunderland	A1	a
Scotland	Ayrshire	Ayr	W1	a
Scotland	Ayrshire	Ayr	W2	a
England	Tyne and Wear	Sunderland	A1	b
England	Tyne and Wear	Sunderland	A1	b
Scotland	Ayrshire	Ayr	W1	b
Scotland	Ayrshire	Ayr	W2	b

[Rickmaurinus]

To create a crossjoin you can either use: 

- Add a helper column in the two tables you want to cross join, and input the same value. Then perform a merge on these columns. It creates a crossjoin

- Reference the table name directly in a custom column -> this also creates a crossjoin. More details you can find here; 

Create All Unique Combinations From Multiple Columns (gorilla.bi)

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[Rickmaurinus]

Hi,

There's also other several ways to do this. 

Method 1: The easiest way to do a crossjoin is to put the table you want to crossjoin, in a custom column. Just write

= TableName

You can then expand the column to get your result. The downside is that this method is slow. 

Method 2: The faster method, is to add a join column to both tables. You can add a dummy value in this column. For example the number 1. Then join the tables together based on this join column. Delete the join column after cross joining the table. 

More details on both method you can find on my blog at: Understanding Join Types in Power Query - BI Gorilla

Enjoy!

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[LivioLanzo · Accepted solution]

Hi @Anonymous

you need to add a new column to the Region table, this column would contain the Product table, and then you'd expand this new column

[Anonymous]

I have tried to create a join in Power Query but there is no field to join on.

How can I add a new column with the product table data?

[Stachu]

add a new column and type 

= Product

(where Product is the name of your product table), then expand the columns

[hullterry]

Stachu, 

A simple elegant way to do cross join in M.

Many thanks!

[pcowman1]

Just learned a fabulous new to me thing from this! LOVE IT! 

[Mattmcdowell_]

Look at the steps of my query called table2

https://1drv.ms/u/s!AlzSMdHvJwp1jQjkmsvDaQD21p0V