fbpx

2 Tricks to Create Unique Combinations From Multiple Columns – Power Query #7

Power Query - Produce Unique Combinations - Featured Image

There are times where you need an efficient way to produce all unique combinations of your data. Recently a customer of mine used a report to track how the sales were doing. Their situation is as follows. The company reports several metrics for their business units. And not only that. They also operate in several countries, each with their local currencies. Their last step is to convert the numbers to the reporting currency for consolidation.

The required numbers were represented in several different pivot tables. Some GETPIVOTDATA formulas did the job of getting the right numbers out of the pivots. The client did not want to use a pivot table to show the numbers. Instead it was desirable to pull down the formulas and use the result of that. To be able to do that, the spreadsheet needed all the unique combinations possible for the data. After all, only that would add up to the right total. And that’s where Power Query came to the rescue. In today’s post you learn 2 tricks to produce all possible item combinations from several columns.

 

Video Tutorial

If you would rather read the tutorial or need more instructions, then continue reading.

Load data to Power Query

This example uses the following data:

Power Query - Produce All Combinations - Data

In order, the tables 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.

Power Query - Produce All Combinations - Queries

As all your data is in Power Query, the next step is to find all combinations of the data. In SQL you would do a so called ‘Full Outer Join’. This results in a table containing all records from both tables, whether the join matches or not. I will show you two ways how to do this. The first method involves helper columns, and the second method pulls the data in straight away.

Produce All Unique Combinations – Using a Helper Column

To start with, in each query you need a column to match on.

Power Query - Produce All Combinations - Helper Join Column

Do the following:

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

Repeat for the other two queries.

Power Query - Produce All Combinations - Join Column Added

You end up with a column behind each query. For this example I start by 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 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 table CountriesCurrencies with Metrics.

The result is two new 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 column BusinessUnits and Metrics:

Power Query - Produce All Combinations - Table Objects

  • 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.

Power Query - Produce All Combinations - Resulting Table Using Helper Columns

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

Produce All Unique Combinations – Using A Formula

Previous method to produce all possible combinations was my first approach. It’s very effective but does take a few steps to perform. Above all, it involves unnecessary helper columns. You can achieve the same using a formula. Let’s assume we have all our tables into 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
  • name the column MergeBusinessUnits
  • As custom column formula fill in =BusinessUnits -> press OK

Power Query - Produce Unique Combinations - Direct Reference to Query

This will add the below column:

Power Query - Produce All Combinations - Added Merge 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 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

Power Query - Produce All Combinations - Resulting Table Using Direct References

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

This example used 4 variables for CountriesCurrencies, Business Units and Metrics. As each table contained 4 variables, it should have 4 * 4 * 4 = 64 unique combinations. You may feel you can handle creating 64 combinations by hand. Yet imagine doing something similar when you replace the Business Units, by 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.

As always, it’s good to know different approaches to handle a situation. 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. Please share this post if you liked it!

Rick de Groot

My name is Rick and my goal is to help you advance with Excel and Power BI. I believe learning is one of the great pleasures in life and I want to share my knowledge with you to help you improve your skills.

5 Responses

  1. 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).

    I’d appreciate any help you can offer.

    Best,

    Dimi

  2. Hi Dimi,

    Thanks for sending the challenge. It’s a fun riddle to solve! I’m sure Power Query can help you all the way.
    I’ve made an example file on how to create the unique ThreeValueCombinations. You can download it from:

    http://excelgorilla.com/uniquevaluecombinations/

    Your request is possible using a custom function in Power Query. However, I’m still learning every day, especially in the area of custom functions. I haven’t been able to produce the full solution. I hope this file at least gets you going!

    Cheers,
    Rick

  3. 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!

Share Your Thoughts!