Excel Data Model Pivot Table Refresh Error

A simple data change can cause a strange pivot table refresh error, if you added the data to the Data Model. The long error message starts with, “We couldn’t get data from the Data Model. Here’s the error message we got”. It also mentions “function PCMinorObjectCollection”, which I’d never heard of before. See what causes this problem, and how to fix it.

Strange Error Message

Here’s an edited version of the pivot table refresh error message, with some of the text moved, so you can read it.

And here’s a text version of the message:

We couldn’t get data from the Data Model. Here’s the error message we got:
An unexpected error occurred (file ‘pcminorobjcoll.inl’, line 109, function PCMinorObjectCollection < class PCProperty, class NameHashSupport > ::SetNameAndUpdateCollection’)

What Causes This Error?

I learned about this problem from UniMord, who figured out what caused the error, and how to fix it.

In his Excel workbook, UniMord had done one simple thing that caused the pivot refresh error.

In the pivot table source data, he had changed one of the headings from UPPER case to Proper case.

Reproduce the Problem

To reproduce this pivot table refresh error, you can follow these steps.

Create a simple list on a worksheet, like the one shown below. It has 3 columns – Num, Month, DAY
Format the list as a named Excel table.

Next, create a pivot table from the table
IMPORTANT: Be sure to check the box to Add to Data Model

Add a couple of fields to the pivot table layout. In this example:
- DAY is in the Rows area
- Num is in the Values area, as Sum of Num

Make a Small Change

Next, you’ll make a small change that causes the error when refreshing.

In the named table, change the DAY heading to Day

Next, you’ll refresh the pivot table a couple of times:

Right-click on a pivot table cell, and refresh the pivot table
The first refresh should be OK
Next, refresh the pivot table again
This time you should see the error message that starts, “We couldn’t get data from the Data Model.

Duplicate in PivotTable Field List

That error message doesn’t give any clues as to what is wrong, or what needs to be fixed.

However, while troubleshooting the error, UniMord noticed that the changed field was listed twice in the PivotTable Field List.

the old name (DAY) is still there
the new name is also listed, with a number added (Day 1)

Fix the Problem

There might be other ways to fix this problem, but here’s what I did.

First, I changed the heading back, to get rid of the extra field:

In the source data table, change the heading back to its original case – I changed Day back to DAY
Then, refresh the pivot table
The extra field should disappear from the PivotTable Field List

Change a Heading Case With No Error

Next, I changed the heading case again, but in a 2-step process, to avoid getting an error message.

1) Change the Heading

In the source data table, change the heading case – I changed DAY to Day

2) Change the Data Model

On the Excel Ribbon’s Data tab, click the Manage Data Model command
In the Power Pivot for Excel window, on the Home tab, click the Design View command
In the diagram, right-click on the field name that you changed, and click Rename
Type the field name, the way that you entered it in the source data table – I changed DAY to Day
Close the Power Pivot window

Problem Solved

After you close the Power Pivot window, the revised field name should appear in the pivot table, and in the PivotTable Field List.

To test the fix, and be sure it worked:

Refresh the pivot table
No error message should appear.

More Pivot Table Info

For more pivot table information, you can go to these pages on my Contextures website.

Refresh Pivot Table

Data Source

Pivot Table Errors

________________________________________