Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

This post shows how to perform a cross join between tables in Power Query. There are cases where you want to return all possible combinations between two tables. Doing this work manually takes a lot of time, but more importantly, will require time from you again and again.

I will teach you an efficient way to generate unique combinations from multiple columns or lists. You will find two methods to produce all possible item combinations from several columns.

You can find the video tutorial below, or if you would rather read it, then continue below. So, what is a crossjoin?

1. What is a Crossjoin?

A cross join is a join type that returns the Cartesian product of rows from the tables in the join. It’s when you combine all the rows from one table with all the rows from another table. You can think of it like a big multiplication table.

There are a few situations where a crossjoin can be super useful. Some situations where a crossjoin can be useful are:

• You prepare your dataset for certain look-ups for budgeting templates. You need all combinations of products and locations.
• You want to prepare a row-level security list. For admins, you want to repeat their email for each of the Properties they should have access to.
• When you want to create a big data set for testing or training a cross-join easily creates a large quantity of data
• One is when you want to create a big list of all possible combinations of two things.

Enough theory; how can you actually perform a crossjoin in Power Query?

2. Sample Dataset

This example uses the following data:

In order, the table names are CountriesCurrencies, BusinessUnits and Metrics. To start out with, get all data into Power Query by repeating the below process for each table:

• select the table -> go to the data tab -> click From Table / Range
• click close & load dropdown -> select close & load to… -> choose only create connection

Repeat the previous steps for all three tables. You should end up with below three queries.

As all your data is in Power Query, the next step is to find all unique combinations of the columns. In SQL, you would do a so-called ‘CROSS JOIN.

This results in a table containing all records from both tables, whether the join matches or not. So how do you perform a crossjoin?

3. Performing a Crossjoin

There are two easy methods to perform a crossjoin in Power Query. The first method involves helper columns, and the second method pulls the data in straight away.

3.1. Method 1: Using a Helper Column (faster)

The crossjoin we will perform is done using two tables. The first method builds heavily on a column we add that contains a single value. Note that this method is also the fastest method of the 2, but involves a few steps.

Start out by creating a new column in both your tables. This is also the column you will perform a join on later.

Do the following:

• go to the tab add column -> click Custom Column
• name the column Join and fill in = 1 as a custom formula

The value used here is a dummy value; you can use any other value you like. Repeat these steps for the other two queries.

After performing these steps, you end up with a join column at the end of each query. For this example, I first select the Query CountriesCurrencies. To get the data of the other queries, you can perform a merge.

• Go to the tab Home -> select Merge Queries
• Merge the query CountriesCurrencies with BusinessUnits
• Select the column Join as a merge column for both queries
• For the merge, use join kind Full Outer (all rows from both) -> press OK

This will add a new column containing a table object. Perform these steps again, but this time merge the table CountriesCurrencies with Metrics.

The result is two newly added columns containing Table objects. The table objects contain the data we just merged. What’s now left is to reveal this data. For both columns BusinessUnits and Metrics:

• Expand the two arrows in the column header -> deselect column Join
• Deselect the checkbox saying ‘Use original column name as prefix’ -> click OK

The result of these steps is that all the combinations of data have appeared in the query. What’s left is:

• Delete the column Join as we don’t need it anymore.

The end result is a table with all possible combinations of data. Yet there is an easier way to do this: taking less steps.

2.2. Method 2: Formula (easier)

The previous method to produce all possible combinations was my first approach. It’s very effective but does take a few steps to perform and includes unnecessary helper columns. You can achieve the same using a formula. Let’s assume we have all our tables in Power Query, without the Join helper columns. To get all combinations using a formula, do the following:

• go to the tab add column -> click Custom Column
• As a custom column formula, fill in =BusinessUnits -> press OK

This will add the below column:

What this formula does is, it looks for a query called ‘BusinessUnits’ and returns all of its rows in a Table object. In other words, it performs a Full Outer Join with the BusinessUnit query, but without using a helper column.

Perform the previous steps again, but this time fill in Column Name MergeMetrics and Formula =Metrics. For both new columns:

• Expand the two arrows in the column headers of the new columns
• Deselect the checkbox saying ‘Use original column name as prefix’. -> click OK
• Change column types to Text for both newly added columns

And again, you end up with the same table, with 64 rows.

You may feel you can handle creating 64 combinations by hand. Yet, imagine doing something similar when you replace the Business Units, with Brands. You may be working with over 100 brands. This would mean 100 * 4 * 4 = 1600 unique combinations! You better think twice before doing that by hand.

This example created all unique combinations of 4 columns. It included the variables for CountriesCurrencies, Business Units and Metrics. As each table contained 4 variables, it should have 4 * 4 * 4 = 64 unique combinations.

3. Conclusion

As always, it’s good to know different approaches to handling a situation. The first method involved using a helper column and inputting a single value. Therefore, this method is the faster performing one of the two and is preferred.

The second method involves referencing the table name in a custom column. This method is easier but also slower.

If you work with multiple files, you may want to check out this post on how to consolidate those in an easy way. Or perhaps you work with single columns that you’d like to turn into a table.

Lastly, if you know other ways to produce unique combinations in Power Query, I would love to read about them in the comments.

Enjoy Power Query!

Share on:

Power Query

Power Query

Power Query

Power Query

Power Query

Power Query

The Definitive Guide to Power Query M

1. Thanks a lot, very useful! But one question, how can I generate the list of unique combinations? For example, I have two lists of cities which I combine to a single table – but I want to list only each pair of cities once, i.e. if the two cities are London-Paris I am also getting a second row with Paris-London. How can I merge them to the unique combinations?

2. Hi Rick,

What if I have an one-column Table A (okay, a list):

Apples
Oranges
Pears

and a proper Table B (let’s say 3 rows x 3 columns):

1, 4, 7
2, 5, 8
3, 6, 9

How do I create a new table where Table B is repeated for every value in List A – and the values in List A are in the first column?

Example:

Apples, 1, 4, 7
Apples, 2, 5, 8
Apples, 3, 6, 9
Oranges, 1, 4, 7
Oranges, 2, 5, 8
Oranges, 3, 6, 9
Pears, 1, 4, 7
Pears, 2, 5, 8
Pears, 3, 6, 9

Thanks if I can borrow your ingenuity!

• Teemu, that’s similar to the method this blog explained. You can reference the list in a custom column, then expand the values to the rows. Like in this example:

``````let
ListA = { "Apples", "Oranges", "Pears"},
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIBYnOlWJ1oJSMgyxSILcA8YyDLDIgtlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Sorted Rows" = Table.Sort(ExpandToRows,{{"Column4", Order.Ascending}})
in
#"Sorted Rows"``````
3. Ignas,

That’s exactly what method 2 does. It’s in the same article.

4. Hey Rick

Great guide, really liked the simplicity and practicality of the solution. I used the 2nd option.

5. Thanks a lot, you saved me hours of work!

6. This has been so helpful for step 1 of a process that I am completing! I am wondering if you might know more about the last step I need to do. Here is an example of what I did when following your first step.
3 columns, each with the same 7 names. i.e. 1,2,3,4,5,6,7. I got through and had the 343 different combinations, but since the data in my 3 columns is the same, some of them are duplicates. Unfortunately, Excel isn’t recognizing the fact that they are duplicates because the sequencing is different, i.e. 122 and 221.
Do you know if there is an easy way to pick through and removes those “duplicates” rather than doing it manually?

• Hi Erika,

Interesting challenge. What you’re looking for can be reached by first transforming a row into a list. Then sort the list, and lastly remove the duplicates in the sorted list column. You can find an example below (copy this code in the advanced editor in Power Query). Hope that works!

Rick

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmOlWB0IzxgsAuMZovCMwCLIKhE8EyA2hfNMUfSZQuRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
#"Removed Duplicates" = Table.Distinct(#"Added Custom1", {"SortedList"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"RowAsRecord", "SortedList"})
in
#"Removed Columns"``````
7. This is brilliant! Thanks for helping

• Great to hear. Thanks for visiting my website Marilize!

8. Ignas,

That’s exactly what method 2 does. It’s in the same article.

10. Bye, bye VLOOKUP – hello Merge!
Thanks, Rick for this great tutorial on the powerful feature.

11. You can do this as many times as you like. Could you provide an example of what combinations you would like to see?

Rick

12. Hi, How can I do this multiple times? I have multiple combinations to use the query always in pairs. Do I have to make the query for each combination?

13. Thanks Rick! Much appreciated. I’ll be sure to look at it closely and if I can get a full solution from it I’ll let you know!

14. Hi Rick,

This video is great and I had no idea you could use excel in this way!

I have a question that’s related to this topic that I’m hoping you can help me with:

I’m trying to find all unique combinations in a dataset that has 7 columns. Unique combinations can be any two – seven values that never appear in the same row as each other.

1, 2, 3, 4, 5, 6, 7,
2, 3, 4, 5, 6, 7, 8,
9, 1, 2, 3, 4, 5, 6,

(each value is in it’s own cell).

In the above example you can see that numbers 1 and 8 are never in the same row, so this is considered a unique two value combination. Numbers 1, 7 and 9 also never appear in the same row so this is a unique three value combination.

If possible, I’d like a section that contains a drop down list so I can choose some of the values and it will then highlight the values that should be in the remaining options to make them unique. e.g. if I select number 9 as a value then on the next drop down if it highlights number 8 (as that would be unique), or I can choose number 6 (not unique) so then the third option would highlight number 7 (as this would make it unique).