Power Query: Building Bridges

Welcome to our Power Query blog. This week, I look at creating a bridge table.

Today I am going to look at how to construct and use a bridge table. This does not mean I’ve taken up playing cards; this kind of bridge table can also be called an intermediate table, and it allows me to create a ‘many to many’ join.

In Power Query: If You Can’t Tell Them Apart, Join Them, I looked at using different types of joins in order to see what was missing from two similar looking tables.

The previous screen shows the data I used (now in Excel 2016), and the join options available. Whilst I can happily merge these queries in Power Query, once I upload their product to the data model, I encounter problems joining my two queries if they have duplicate entries, as I will show.

In order to look at ‘many to many’ joins in the data model and how to achieve them with Power Query, I am going to create two simple subsets of this data.

I create queries for each of these tables by using the ‘From Table’ option on the ‘Get and Transform’ section of the ‘Data’ tab. I create the queries as ‘Connection Only’ and add them to the Data Model. This can be done at any time by right-clicking on the query and choosing ‘Load to’:

I then merge the queries.

I choose the ‘Full Outer’ join, which should include all rows from both.

My first table rows are shown, and I can expand the final column to see the data from the second table.

I have all 15 rows.

Now I am going to check what happens when I link ‘Table_One’ to ‘Table_Two’ in the Data Model. This will involve a small excursion into Power Pivot! (For more on the interaction between Power Query and Power Pivot, please refer to see Power Query: (Data) Model Building and Power Query: Models Have Relationship Issues Too.)

In the ‘Power Pivot’ tab, I can select to ‘Manage’ the Data Model.

In the Power Pivot window, if I choose the ‘Design’ tab, I can ‘Manage Relationships’, viz.

When I try to link ‘Table_One’ to ‘Table_Two’ I get the message that it is a ‘many to many’ relationship, and therefore is not currently supported. I need to create a bridge table between ‘Table_One’ and ‘Table_Two’ so that I can join them. My bridge table will contain unique entries of all the data in ‘Table_One’ and ‘Table_Two’, so I can build it from my merged table, currently called ‘Merge4’.

I rename my table ‘Bridge_table’. I need to clean it up, as I only need a list of unique full names. I delete all the other columns by selecting the FULL_NAME column and choosing to ‘Remove Other Columns’.

This gives me one column, and right-clicking again, I can select to ‘Remove Duplicates’.

I now have a unique list of names which I can use as a bridge table. Back in the Power Pivot window, I link ‘Table_One’ to the bridge table.

This is fine as it is a ‘many to one’ relationship.

I also create a relationship between the bridge table and ‘Table_Two’. This is a ‘one to many’ relationship.

Looking at the ‘Diagram View’ from the ‘Home’ tab in my PowerPivot window, I can see that the two tables are now connected via the bridge table. Slicing or filtering on this intermediate, bridge table will affect both dependent tables. This is a common technique employed in relational databases and if you have never encountered this before, once you start using this technique you’ll wonder how you ever got by without it.

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