Power Query: Unique Combination

Welcome to our Power Query blog. Today, I look at a way to create a set of data from multiple tables.

I have some simple lists of data for my ever-ready fictional salespeople.

I want to create a table which provides me with all the combinations, ready for the amounts to be filled in. An example row would be ‘Mary Hotel London’.

I start by creating a table of my employees from the ‘From Table’ option in ‘Get & Transform’ in the ‘Data’ tab:

This loads my data into a query

I repeat this process, so that I have three queries, namely ‘Employees’, ‘Expense Types’ and ‘Locations’.

In each case, I have chosen to only create a connection when I saved my queries.

The first method I will use is to merge queries together. If I tried this now, the problem becomes obvious:

I don’t have a column that I can use to connect my tables: I need to create one.

For each of my queries, I am going to create a custom column from the ‘Add Column’ tab

I am going to set all rows in my new column, Link, to have the same value of zero (0). I will add the column to all my queries.

Now when I merge, I have a column I can use to join my data up. If I use a full outer join, I can join each row in my first query to each row in my second query.

I can see that each employee name has been linked to all the expense types. I expand the Expense Type column and rename my query.

I only need the expense type, not the linking column.

Now I can merge this with the locations using a similar method.

This gives me all the combinations I require, and I can delete the Link column.

There is, however, a quicker way.

I have created three new queries from my original data. This time I don’t add the link column. My new queries are ‘Employees_No_Link’, ‘Expense_Types_No_Link’ and ‘Locations_No_Link’. I go into ‘Employees_No_Link’:

I create a custom column again, but this time instead of a fixed value, I use the name of the query ‘Expense_Types_No_Link’.

In a similar way to the merge operation from the original method, I have created a column of tables, which may be expanded.

I repeat this process for the locations.

When I expand Link_Locations, I get my result:

Come back next time for more ways to use Power Query!