cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
system243trd Frequent Visitor
Frequent Visitor

Power Query Cross Join

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

 

Product
a

b

 

CountryAreaTownHouse
EnglandTyne and WearSunderlandA1
EnglandTyne and WearSunderlandA1
ScotlandAyrshireAyrW1
ScotlandAyrshireAyrW2

 

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

 

CountryAreaTownHouseProduct
EnglandTyne and WearSunderlandA1a
England

Tyne and Wear

SunderlandA1a
ScotlandAyrshireAyrW1a
ScotlandAyrshireAyrW2a
EnglandTyne and WearSunderlandA1b
EnglandTyne and WearSunderlandA1b
ScotlandAyrshireAyrW1b
ScotlandAyrshireAyrW2b
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Power Query Cross Join

Hi @system243trd

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

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

4 REPLIES 4
Super User
Super User

Re: Power Query Cross Join

Hi @system243trd

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

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

system243trd Frequent Visitor
Frequent Visitor

Re: Power Query Cross Join

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?

Highlighted
Super User
Super User

Re: Power Query Cross Join

add a new column and type 

= Product

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

Mattmcdowell_ Regular Visitor
Regular Visitor

Re: Power Query Cross Join

Look at the steps of my query called table2

 

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