Power Pivot Principles: How Did I Bridge the Gaps?

Welcome back to the Power Pivot Principles blog. By popular demand, I will revisit last week’s scenario and show you how to build a so-called “bridging table”.

Last week, I created a bridging table which contains unique values, in order to join two tables (Budget and Transaction) together, that cannot be joined on a one-to-many basis, as both contain duplicates, viz.

Let me explain how to construct such a masterpiece using Power Query. It is important to note here that Power Pivot only supports one-to-many relationships (a one-to-one relationship will only flow in one direction). Both the Budget and Transaction tables contain duplicate values. Therefore, they may not be connected in Power Pivot. Therefore, we need a lookup table, which is the Bridge table in this case. It just means this week’s Power Pivot article is all about power Query. Go figure.

To begin, select the Budget table and click on Data tab -> Get & Transform Data -> From Table / Range:

It should open in the Query Editor, viz.

Then, in the Home tab click on Append Queries -> Append Queries as New:

Select the Transaction table as the second table in the drop-down menu and click OK. This is because we want all the possible unique values that feature in one or both of the tables to be connected.

The new table looks like this:

Right click on the Account_Number column and select ‘Remove Other Columns’ (this is in case additional columns are added later, we only specify the one field we wish to keep):

Right-click on the Account_Number column and click on ‘Remove Duplicates’.

In the Home Tab click on ‘A to Z’ icon in the Sort section:

Click on ‘Close & Load’ in the Home tab to view the ‘Unique’ value table, if required, viz.

This table may now be used as your bridging table. That’s it for this week!


Come back next week to see how we use the disconnected table for performing a sensitivity analysis by staying tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.