Power Pivot Principles: Bridging the Gaps

Welcome back to the Power Pivot Principles blog. This week, we’ll talk about linking the Budget and Transaction tables in the data set we have been working on.

Before I begin, I would like to show you a Diagram view of how the data tables that we have been working on in these past weeks look, viz.

To achieve my objective, I can simply drag the ‘Account_Number’ column between both tables as this is a common field in both tables (it is not important that the names are the same). However, on doing this, I get the following message:

Oops! It turns out we have no unique fields in either of the ‘Account_Number’ columns of the two tables and we have tried to create a ‘many to many’ relationship. The reason why this error message occurred is because Power Pivot only allows us to create a ‘one to many’ relationship (yes, I know this is possible in Power BI, but that’s another story for another day!).

In order to create a relationship between the two tables, I will have to construct a new table with unique values of all the account numbers. I will call this new table ‘Bridge’.

Once I have my new Bridge table, I can easily connect the Budget and Transaction table and my diagram view now looks like, viz.

There we go! Our two tables with non-unique fields are linked and the goal for this week is achieved.

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.