Updating Pivot Tables with Data Models

This week was one of those weeks were nothing worked. I couldn’t make anything work. Just before leaving for the conference, I was working on a project and I had managed to reach a good stopping point where I figured that when I came back I could get right to it and finish up the project quickly, having figured out how to incorporate data from a new vendor.

This past week I refreshed my memory on what I did before leaving and started to work on finishing up part 2 of the project when I realized that something was missing. There were multiple issues but I want to focus on one of the issues: using data model and updating the data. Before leaving for the conference, I had to totally redo the pivot table because the data model apparently was not working.

Here’s how I understand how the data model works in Excel 2013 without the PowerPivot add-in (the PowerPivot add-in was not available on my machine): to combine two sets of data, you need to initiate the “insert Pivot Table” for each of the data set. At the bottom “Create Pivot Table” box, you will see a line about adding the data set to a Data Model. So for each table of data you add, you will have a tab created for a pivot table. Most likely you won’t be using all of those tabs; most likely it will be just one of the tabs. Once you’ve added all of the data set to the Data Model, you have to link the tables by a common key. This is the process of “creating a relationship”.

Tip 1

Tip 1: If you are going to combine two tables of data, you need to have a key that is common between the two tables. It could be product number, SKU number, project number or in my case, employee number. The primary table should be the one where the key is listed once – it cannot repeat. In my case, I had payroll checks data so it could NOT be the primary table; the other table that I wanted to combine had to be the primary table, once I fixed some things.

Once you’ve linked the tables using a primary key, you are ready to create your pivot table using fields from any of the data sets added to the Data Model. Each table of data set will have a range name such as Range, Range1, Range2, etc., and you pick the field that you want in the pivot table.

Now, where I ran into problems was in updating the Data Model with more data. I had to update one of the data sets with more data because I was missing some data. I had received two files of data and I forgot to incorporate one of the files, so I needed to update the data set with additional rows.

With a normal pivot table, you just append the additional rows of data at the end of the table of data and then you go into the pivot table and change the data source via PivotTable Tools – Analyze – Change Data Source, fix the number of rows in the data source, then right click the pivot table and hit Refresh. Pivot tables with Data Models do not work like that.

Tip 2

Tip 2: To update the data set in the Data Model and in the pivot table, do PivotTable Tools – Analyze – Connection Properties. At the top is the connection name; change the row number. Doing this won’t change the number of rows pulled into the Data Model but it will help you identify whether you have the right range. In the middle, make sure “Refresh this connection on Refresh All” is checked, just to be safe. Now go into the “Definition” tab and change the row in the “Command text”. This command text is actually the command to Excel on how many rows of the data set should be pulled into the Data Model. Once you’ve corrected that, click OK to get out of it. You may or may not have to refresh the pivot table but the additional rows should be pulled into the pivot table.

So, pivot tables with Data Models work very differently. Supposedly there is a PowerPivot add-in which my machine can’t pull in and according to my research, it works differently from what I just described. So you may have to research to figure out how to update your pivot tables, depending on your set up. I think Excel 2016 comes in with PowerPivot. Some Excel 2013 has a PowerPivot add-in. My Excel 2013 does not.

Just be aware, updating the data set in the pivot table won’t just automatically update if you append new data at the end of the table.