Given 3 separate lists of text how can we create all combinations? I’ve known Power Query (Get & Transform) can create a cartesian product but it’s only now that it’s part of my active skill set.

(Download:  SOLUTION Excel file,  PRACTICE Excel file)

 

Cartesian Product?

Cartesian product creates all combinations from multiple lists. In this post I’ll use Power Query!

In sheet ‘Inputs’ we have 6 colors, 4 sizes, and 3 animals giving us 72 combinations.

(see BE CAREFUL! section below if you have thousands of combinations).

 

Steps

1 – Load 3 tables into Power Query (from sheet ‘Inputs’).

We need a ‘Dummy’ column with a 1 in each row (I’ll explain later).

 

 

 

 

• Select any cell within a table
• On the ribbon select ‘Data’ and ‘From Table/Range’
• At top left click ‘Close & Load’ drop down arrow, select ‘Close & Load To’
• Select ‘Only Create Connection’ (as seen below)
• Repeat steps to load other tables

 

 

 

 

 

 

 

 

2 – Merge the Queries. Use loaded tables to create all combinations.

The 3 loaded tables as queries.

2 new queries will create all row combinations.

 

 

We’ll start by combining Colors & Sizes. Highlight queries Colors & Sizes.

 

 

 

 

Click drop down arrow for ‘Merge Queries’ and select ‘Merge Queries as New’.

 

 

 

 

 

Details for the 1st merge:

• Select Sizes in 1st drop down list and Colors in the 2nd
• Highlight Dummy fields in both tables
• Select join kind ‘Left Outer’
• Click OK and name it ‘Merge1_ColorsSizes‘

 

 

 

 

 

 

 

Click double arrows (Colors column) to expand to all rows.

Now you see all combinations for Sizes and Colors.

 

 

2nd and final query. Highlight Animals and Merge1_ColorsSizes

 

 

 

 

Click drop down arrow for ‘Merge Queries’ and select ‘Merge Queries as New’.

 

 

 

 

 

 

Details for the 2nd merge:

• Select Animals in 1st drop down list, Merge1_ColorsSizes in the 2nd
• Select Dummy fields in both tables
• Select join kind ‘Left Outer’
• Click OK and name it ‘Merge2_AnimalsMerge1‘

 

 

 

 

 

 

 

 

 

Click double arrows (top right) to expand to all rows.

 

You’ll now see all 72 rows but let’s remove column ‘Dummy’. Right click on column Dummy and remove (we no longer need it).

 

 

Now you should see 3 columns and 72 rows!

 

 

 

 

 

3 – Export Back to a Sheet.

Select query Merge2_AnimalsMerge1.

Top left of screen select drop down arrow on ‘Close & Load’ button, select ‘Close & Load To’

 

Select ‘Table’, ‘Existing worksheet’ and a destination cell.

 

 

 

 

 

 

 

‘Dummy’ Column?

Normally we’d have unique IDs in a table (one side) and then repeating IDs in another table (many side). Example: Product table has a row for each product we sell. Sales table has a row for each sale (products sold many times).

But…here we use the 1s in the Dummy fields so that each single row in the first table matches all the rows in the second table. It’s known as a ‘Many to Many’ relationship. It’s usually bad news but here we intentionally use it to get all combinations between these tables.

 

!BE CAREFUL!

Be careful if your tables have thousands of records. If you have 2 tables and each has 1000 rows that’s 1000000 rows! 

You could potentially freeze and crash Excel if you have too many combinations! I have seen database queries that should have a join but don’t! This creates an unintentional Cartesian product that can take many hours or days to run!

 

Where Did I Learn This?

I learned this technique from Power Query Academy (Ken Puls & Miguel Escobar). Disclaimer: i’m a student and an affiliate.

 

XLarium’s Solution

Here is the Excel file that uses XLarium’s solution (seen below in comments section). His solution doesn’t require the Dummy column with the 1s. Thanks for sharing!

 

Active Skill Set

Until I can quickly apply a concept on a daily basis I don’t consider it to be part of my active skill set. It’s more like an awareness that something is possible. But now I can quickly use Power Query to create all combinations and it’s MUCH EASIER than a formula or vba solution. My Excel file includes a tedious formula solution. See this post for a Pivot Table solution.

Note that in this example we have 3 tables so we need 2 queries. With a limited amount of tables Power Query is the fastest solution. It would be rare to get all combinations from many tables. If that’s necessary I would contact a vba programmer.

 

About Me

My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham Ontario Canada.

Away from data I sometimes visit museums. In May I visited an impressionist exhibit at Toronto’s AGO.

I should visit the ROM’s Rembrant exhibit this summer!

Here you see me with Mondrian Art that I saw in New York last year (only a nerd would create Mondrian art in Excel).