Sometimes, you need to create a multiplication of all sets of all pairs from two different data tables in Power BI. This action is different from Merge (Join) because there is no matching key columns and no way to relate the two tables together. However, you want to create a multiplication as a flatten table. If this process is needed to be done in Power Query, then there is a simple trick to do it, In this article, I’ll explain how you can do it.
What is Cartesian Product
Based on a definition from Mathstopia (and that is where the below picture is also coming from); Cartesian Product is the multiplication of two sets to form the set of all ordered pairs. The first element of the ordered pair belong to the first set and the second pair belongs to the second set. For example;
Example
Let’s say I have a table of products as below;
and a Warehouse table as below;
There is no link between the two tables above. If there was a table that had the information about each product in each warehouse, I could use that to merge the three tables, but that is not the case here.
The expected output is below:
for each warehouse, all the products are listed. considering that the product table has three rows, and the warehouse table also three rows, the final result, has nine rows.
Cartesian Product in Power Query
Here are steps that you can follow to do this process in Power Query Editor.
First, click on Transform Data to get to the Power Query Editor,
I normally prefer to keep the original tables untouched, so I create a reference for the cartesian product query (you can do this in any of the existing queries if you want);
Now in this new query, you can add a column; Custom column.
The column that you add can be named as the other table (the name part is not the important part unless you want to use it as the prefix of the columns after expanding it). the most important part is that the expression should be the name of the other table (exactly the same spelling, Power Query is case-sensitive remember that!)
You cannot drag and drop the other table name’s into the expression, but if you start typing, you will see the intellisense comes to help.
Now you can expand the Product column;
And here is the results:
Don’t Use Cartesian Product Instead of Merge/Join
Remember! There is a big difference between a merge/join or cartesian product. Merge/Join is useful when the two tables have some logical ways to be matched together, using one or more joining fields. Cartesian Product on the other hand side is for tables with no existing logic of connection, it is for scenarios that you want to have a result set of all pairs regardless.
Cartesian Product can result in a huge table if the tables that you are using as the source are big. If table A is 1,000 rows, and table B is also 1,000 rows, the result of the cartesian product will be 1,000,000 rows. So use it carefully, and only if needed.
Download Sample Power BI File
Download the sample Power BI report here:
Video
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.