How do I remove duplicate connections in Power Pivot?

Level: Beginners

I was working with a client recently and was asked a question…  “How do I remove duplicate connections in Power Pivot”?  I know this is an issue and I specifically advise people to be careful in my training courses and also in my book.  But of course once you have the problem, then knowledge on how to do it the correct way is really only helpful for “next time”.  Unfortunately the “fix” is quite manual as I don’t believe there is an “easy” way to reconnect a loaded table to a different data source once you have it imported inside of Power Pivot.

This problem can only occur when you bring in data from a database.  There are often multiple tables in your database that you may want to use in your Power Pivot workbook.  If you bring them all in at once then you are unlikely to have a problem, but if you bring them in one at a time and you don’t do it the correct way, then you will leave a messy trail behind you.

Bringing in your first table(s)

As you would know, to bring in a table you navigate to the Power Pivot window, select “from database” (1 below) and then select the correct data source from the list (2 below).

Once you have at least 1 table imported in this way, there will be a new connection to the database.  You can see this connection by clicking on existing connections (1 below) and then find the connection you made in the existing connections window (2 below).

The problem only occurs when you try to bring in an additional table from the same database.

The correct way to bring in additional table(s)

The correct way to bring in additional tables from the same database is to click on the Existing Connections button (1 above), then select the connection (2 above) and finally clicking on the “Open” button (3 above).  When you do this you will see the import wizard box (shown below) and from there you can click “next” and import additional tables.

When you do it this way the additional tables are imported using the same connection you used the first time you imported from a database.

The wrong way to bring in additional tables

If you don’t know any better, there is another way to bring in an additional table, and that is to go through the same process you did the first time (ie select “from database” and then import the new table).  It seems logical, but there is a negative side affect that creates an additional connection to the database each time you do this.  As you can see in the image below, you can end up with many connections to your workbook without even knowing what is happening.

Now this is not a complete disaster as it will continue to work just fine.  But it will be a pain if you move the location of your database – you will need to re-point each of the connections one at a time (by clicking on the edit button above).  Also it is not immediately obvious to work out which tables are connected to each of the connections, so it is hard to fix.  And it is just plain messy and no doubt frustrating to look at once you realise your mistake.

Unfortunately there is no easy fix

And the unfortunate news is there is no easy fix.  The connection is joined at the hip with the table itself, so there is no way to change the connection from a loaded table and have it use a different existing connection.  So the only solution is to delete the tables one by one and and re-import them using the correct method (shown above) using just one connection.

Which connection is for which table?

As I mentioned above, it is not immediately obvious which connection relates to each table.  The way to find out is to try to delete the connection from the existing connections dialogue shown above.  When you do this you will get an error message telling you that you can’t delete the connection because it is associated with an existing table.  Fortunately this error message tells you which tables are involved.

Here is the process to fix the problem

1. First you should look at the tables in your data model and find the table that contains the most Measures.  This is going to be one of your data tables, so let’s assume it is “Sales” in this example.
2. Then open the existing connections dialogue box and try to delete each of the connections 1 at a time (shown below) until you find the one connected to this table (Sales in my example, obviously it will be different in your case).  Make a note of the name of this connection – this will be the main connection you keep.
3. Then you need to set about deleting each of the other connections and reload the tables using the main connection you identified above.  Firstly select one of the connections to be deleted and press the delete button.
4. The dialog box will pop up again and tell you which table it is connected to.
   
5. Close the dialogue and go to the Power Pivot Diagram View window.  Find the table and take note of the relationships that exist.  Also scroll to the bottom of the table (shown as 2 below) and make sure there are no Measures in the table (shown as 3 below).  A measure will have an Fx icon as shown in 4 below.  You can see there are no Measures in my Customers table below, so I can safely delete it.
   
   If there are one or more measures in the table you are about to delete, you will need to copy them and paste them in a safe location (such as Notepad) so you can re-create the Measures(s) later. Just switch to the Data View, find the Measure(s) in the Calculation Area at the base of the table, then select the measure (shown 1 below) and then copy the Measure from the Formula Bar (shown 2 below).
   
6. The next step is to delete the table(s).  Once you have deleted all the tables related to the data source you are working with, the data source will disappear from the Existing Connections list.
7. Then you should re-import the table(s) you deleted using the correct procedure.  Make sure you open the “Main” existing connection that you identified in step 2.
8. After importing the new table, you need to re-create the relationships and then paste any Measures back into table.
9. Repeat the process for each existing connection in your workbook.

A final word of advice

The above remediation procedure will be quite easy for most Lookup tables as they wont have any (or many) Measures stored in them.  So I generally recommend just fixing the lookup tables.  If you have multiple connections to data tables that contain a lot of Measures, you really have 2 choices.

1. If you want to fix the problem, you can use this technique to copy all your Measures first prior to deleting and re-importing the table.
2. If this looks like too much work, I suggest you simply rename each of the connections that you are going to keep so you know which is which.  Call one of them “Main Connection” and then rename the other(s) with some descriptive name so you know which table they are connected to.  To rename the connection, open “Existing Connections”, select the connection you want to rename and click on “Edit”.  From there you can change the name to something more meaningful.