Cartesian Product in Power Query: Multiply All Sets of All Pairs in Power BI

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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:

Enter Your Email to download the file (required)

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply