Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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!
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.
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.
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
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!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |