Power Query: Unique Combination
28 August 2019
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!