Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
system243trd
Helper I
Helper I

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
LivioLanzo
Solution Sage
Solution Sage

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

8 REPLIES 8
Rickmaurinus
Helper V
Helper V

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
Helper V
Helper V

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
Solution Sage
Solution Sage

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!  

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?

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 🙂

Stachu, 

A simple elegant way to do cross join in M.

Many thanks!

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

Look at the steps of my query called table2

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.