How To Generate All Possible Combinations Of Items From Two Lists

2017-12-26

Given two separate lists of items, how can you create a list of all possible combinations from those lists? In this post, I’ll show you how this can be done using power query.

In this example I’ve set up two lists and created tables from them called List1 and List2.

Create a Connection Only Query for the Two Lists

For each table, we’re going to create a connection only query that adds a column with the same constant value in each row. We will then use this column to join our two tables together using a merge query.

Select a cell in your list and then go to the Data tab and select the From Table/Range command in the Get & Transform Data section.

From the query editor go to the Add Column tab and select Custom Column from the menu.

In the Custom Column dialog box add a constant formula. This can be any text or number, just make sure it’s the same in both list queries.

  1. Give your new column a name. I’ve named it Join Column.
  2. Add a constant to the Custom column formula. I’ve chosen 1 for my constant.
  3. Press the OK button.

Go to the Home tab in the query editor and press the lower part of the Close & Load button then select Close & Load To from the menu.

In the Import Data dialog box, select Only Create Connection and then press the OK button.

Repeat these steps for both lists. You should have two connection only queries called List1 and List2.

Join the Two Connection Only Queries with a Merge Query

Now we are ready to join these two lists together using a Merge query.

Go to the Data tab and select Get Data from the Get & Transform Data section. Select Combine Queries from the menu and then select Merge from the submenu.

Now we can set up our merge query to join the two lists.

  1. Select List1 for the first table.
  2. Left click on the Join Column created in connection only queries.
  3. Select List2 for the second table.
  4. Left click on the Join Column in the List2 connection only queries.
  5. Select the Join Kind as Full Outer (all rows from both).
  6. Press the OK button.

We can delete the Join Column now as we don’t really need it anymore and the output will be less cluttered if we remove it. Right click on the column heading and select Remove.

Now we can expand the List2 table in our query.

  1. Left click on the filter icon to the right of the column heading.
  2. Un-check the Join Column as we don’t need this in the query results.
  3. Un-check the Use original column name as prefix box. We don’t need this either.
  4. Press the OK button.

Now Close & Load the query from the Home tab.

Our List of All Possible Combinations

Now we have a table which contains every possible combination of items from List1 and List2.

With small tables like this example, we could have easily created a table manually. For Large lists this query becomes a massive time saver though!

We can also iterate this process if we have more than two tables to find all possible combinations items from 3 or more lists.

A Slightly Quicker Method

Since writing this post, I’ve found a slightly quicker method to do this. In this method, we still need to load both tables into power query, but we will only need to create one custom column and we don’t need to create a merge query.

Create a connection only query that imports the List1 table.

Now, create a query that imports the List2 table and Edit the query. Add a Custom Column to and name it List1. Enter the formula =List1.

Expand out the new List1 column and then Close & Load the query to a table.

The table will have all the combinations of items from both lists and we saved on making a custom column in List1 and avoided using a merge query altogether!

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Advertisement

Related Articles

Comments

10 Comments

  1. Hannah

    Hi,

    Can you use this method for more than 2 lists?

    Thanks,

    Hannah

    • John

      Yes, you can use the same method for as many lists as you want!

  2. turgut

    Hello John ; Nice post! thank you. But I dont have a powerbi commercial license. I only can download a free version of powerBı, can I use this powerquerry method by this

    • John

      Yes, it will work in Power BI desktop power query as well.

  3. ALAN

    So helpful!! Thank you, sir!

    • John

      No problem Alan, glad it helped.

  4. Craig

    Truly great post John, thank you for figuring this out. I had to combine list 1 of all our customers (1,276 rows), with list 2 of all our products (28 rows) to get 35,728 rows of data that could be imported back into our system. Your article saved a ton of time, and the data is 100% accurate. I only wish I would have scrolled all the way down to see your easier method, before going through the steps to do the merge query. But I learned a part of Excel I hadn’t really been exposed to, so I’m still happy. Thanks again!

    • John

      Awesome to hear that you explored something new (definitely keep exploring power query) and solved your problem.

  5. Rahul Vasudevan

    Thanks a lot John. You saved a ton of time for me and i gained knowledge of one more great trick with the use of power query.

    • John

      Glad it helped Rahul!

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest

Share This