cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Solution Sage
Solution Sage

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!  

View solution in original post

6 REPLIES 6
Solution Sage
Solution Sage

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!  

View solution in original post

Highlighted
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 II
Super User II

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

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

Highlighted
Helper I
Helper I

Re: Power Query Cross Join

Look at the steps of my query called table2

 

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

Highlighted
Helper I
Helper I

Re: Power Query Cross Join

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

Highlighted
Frequent Visitor

Re: Power Query Cross Join

Stachu, 

A simple elegant way to do cross join in M.

Many thanks!

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors