Sankey diagrams are used to show flow between two or more categories, where the width of each individual element is proportional to the flow rate. These chart types are available in Power BI, but are not natively available in Excel. However, today I want to show you that it is possible to create Sankey diagrams Read More
Imagine a scenario where you have two or more lists in Excel and want to create a single table of all possible combinations. The question might come in a simple form, such as: “How can we get a complete list of all General Ledger and Cost Centre combinations?”. Sounds simple, right? There must be a single function in Excel or Power Query that will generate that… right?
Er, actually… No. We’ll need to get a little more involved. Let’s dig into Power Query to see how we can solve this problem.
In this post, there are two solutions presented. They each teach us different things about Power Query, so it’s worth working through both and understanding the differences.
Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples. This is the best way to learn. You’ll be able to see the solutions in action, plus the file will be useful for future reference. The support files are available for FREE to newsletter subscribers.
Click below to subscribe and gain access to the subscriber area. You will also receive:
- My favorite tips and tricks direct to your inbox
- Exclusive content (which is only available to subscribers)
- FREE tools and downloads
If you’re already a subscriber, click here to log-in to the subscriber downloads area.
The filename for this post is 0049 List of all possible combinations.zip.
Watch the video:
Load the data into Power Query
Both solutions in this post require data to be loaded into Power Query. So let’s start there.
The example file contains 3 tables:
First, we need to load all three tables into Power Query.
- Select a cell with the table
- Click Data > From Table/Range
- The Power Query editor will open with the selected data loaded.
- Next, we need to load the data as a connection only. Click Home > Close & Load > Close and Load To….
- The Import Data dialog box will open. Select the Only Create Connection option, then click OK.
Repeat these steps for each table.
Option #1: Merging tables
Once all 3 tables have been loaded, go back into the Power Query editor by clicking Data > Get Data (dropdown) > Launch Power Query Editor.
The first option uses Power Query’s merge feature. However, before we can use that, there is another step we must undertake to prepare the data.
- Select the first query.
- Click Add Column > Custom Column from the ribbon.
- In the Custom Column dialog box, enter the following information:
New Column Name: Temp
Custom column formula: =1
The Custom Name and Column Formula can be any value you wish. But in this example, I’ve used these values.
Then click OK.
Repeat the steps above for each query. Make sure the Custom Column name and Custom Column formula are identical across all the queries.
Our preparation is complete, so we can move on to merging the queries.
- Click Home > Merge Queries (dropdown) > Merge Queries as New.
- The Merge dialog box will open.
Select two separate lists in each section.
Click the Temp column (or whatever name you chose) in both lists.
Set the Join Kind to be Full Outer (all rows from both)
Then click OK.
Now merge any additional tables into the existing merged table.
Your Power Query window will look similar to this.
To create the list of all possible combinations:
- Click the Expand button in the column header.
From the sub-menu:
Select only the column with the data we wish to retain (i.e., in our example, uncheck the Temp column)
Uncheck the Use original column name as prefix option
- The list of possible combinations now appears in the Power Query window.
If you have more than two queries, go ahead and expand those columns too. If you’re working with the example data, the Preview window now looks like this:
Select the Temp column and press the Delete key as we no longer need this. Finally, rename the columns to be Product, Size and Color.
We now have our list of all possible combinations.
Finally, we just need to load this into Excel, so click File > Close and Load (dropdown) > Close & Load To…
In the Import Data dialog box, select Table and New worksheet, then click OK.
Download the 100 Excel Macros ebook
- Contains 100 Excel VBA macros
- Learn VBA by following along with the example codes
- Apply to your macros, automate Excel, save time.
Download the ebook today!
Click the button below to subscribe, you’ll gain access to the subscriber area in which you can download the ebook.
Option #2: Working with tables
There is an easier way, which will be less obvious unless you’ve got a bit of experience of working with tables and lists in Power Query.
Start with the data already loaded into Power Query.
- From Excel, open a blank query by clicking Data > Get Data > From Other Sources > Blank Query.
- In the Formula Bar type =Product. (If you’ve not got the Formula Bar open, click View > Formula Bar in the Power Query editor).
- Next, click Add Column > Custom Column
- In the Custom Column dialog box, enter the following:
New column name: Size
Custom column Formula: =Size
Then click OK.
- Add any additional queries in the same way.
- Expand the tables in the same method as shown in option 1.
This will again give us a table containing all possible combinations.
Now, we just need to load this into Excel. Click File > Close and Load (dropdown) > Close & Load To…
In the Import Data window, select Table and New worksheet, then click OK.
It was a long time after I started using Power Query that I became aware that tables could be used in this way. So hopefully, you’ve learned something new.
Getting a table of all possible combinations into Excel may not be as simple as we had wanted. But hopefully, this post has shown that we can achieve it with a few steps.
There is just one warning from me. From 3 lists containing 3 items, we created 27 rows. Now imagine working with hundreds or thousands of rows. That could result in a lot of data. Assuming all the lists are unique, then it follows the basic principles of multiplication. 1000 rows combined with another 1,000 rows would give 1,000,000 rows!!!! So this can become overly cumbersome if we have a lot of data.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
Within Excel formulas, double quotes are used to denote the start and end of a text string. Look at the following formula as an example: =”Here is some sample text” The double quotes are at the start and end of the text string. But what if we want the text string to include double quotes, Read More
Pivot Tables are a key tool for many Excel users to analyze data. They are flexible and easy to use. Combine the power of Pivot Tables with the automation of VBA, and we can analyze data even faster. This post includes the essential code to control Pivot Tables with VBA. The filename for this post Read More
The INFO function in Excel returns information about the current operating environment. In total, there are seven pieces of information available: File path of the current folder Number of worksheets in open workbooks Cell reference to the top-left visible cell of the window Operating system version Excel’s calculation mode Excel version Type of operating system Read More
The CHAR function in Excel returns a specific character based on a code number from the operating system’s character set. Each operating system has it’s own character set: Windows has the Windows -1251 character set, which is more commonly known as ANSI Mac uses the Mac OS Roman character set. Excel online has a limited Read More
When using Excel, we often display numbers as decimals. However, that is not necessarily the best way for users to read the information; fractions can be a better option. We could enter fractions as text, but then we can’t use them in calculations. So, in this post, we’ll look at the best way to use Read More
One of the things which continually causes problems for all data professionals is dates. Over time, Microsoft has given us a plethora of tools and Excel functions for handling dates, yet they can still catch us out from time-to-time. Even with Power Query (Excel’s tool for manipulating data), we can run into issues when trying Read More
While it is not often that we have to control pictures or images within Excel, based on the number of Google searches for this topic, it’s clearly something people want to know more about. This post serves as a reference to cover most of the scenarios we’re likely to encounter to copy, insert, move, delete Read More
Automating processes with VBA can involve copying, moving, deleting and managing lots of files. Thankfully, VBA has many built-in functions to undertake these tasks for us. These files do not necessarily need to be Excel workbooks; they can be any file type. The filename for this post is 0041 VBA copy, move, delete and manage Read More