Written by Debra Dalgleish from Contextures Blog
A simple data change can cause a strange pivot table refresh error, if you added the data to the Data Model. The problem is easy to fix, once you see what’s causing it, but the error message, shown below, isn’t much help! I’d never heard of function PCMinorObjectCollection, had you?
Strange Error Message
I learned about this problem from UniMord, who explained how to reproduce the error in a few simple steps. Hopefully, you’ll never run into this problem, but thanks to UniMord for sharing his information, in case you need it!
Here’s an unreadable screen shot of the strange error – it’s one of those really wide messages, and included references that I didn’t recognize, such as function PCMinorObjectCollection. What is that?
Here’s an edited version of that message, with some of the text moved, so you can read all of it.
And here’s a text version of the message, in case anyone is searching for help with this error 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 Caused the Error?
Fortunately, UniMord didn’t just send me a problem, and reproduction steps – he had figured out what caused the error, and how to fix it.
UniMord had done one simple thing that caused the problem – he changed one of the source data headings from upper case to proper case. That’s it! Making that one simple change apparently caused one of the Data Model circuits to overheat, or something, and it had a little meltdown.
Or, to misquote HAL, the computer in 2001: A Space Odyssey, “I’m sorry, UniMord. I’m afraid I can’t do that.”
How to Reproduce the Problem
If you’d like to reproduce this pivot table refresh error, with Data Model data, 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.
- Create a pivot table from the table, and check the box to Add to Data Model
- Add a couple of fields to the pivot table layout – I put DAY in the Rows area, and Num 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 case of one of the headings – I changed DAY to Day
- 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 mentions the function, PCMinorObjectCollection
Check the PivotTable Field List
That error message is really confusing, and it doesn’t give you any clues as to what is wrong, or needs to be fixed.
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.
- 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, you’ll change the heading case again, but in a two-step process, to avoid getting an error message.
NOTE: Use this method if you need to make any future “case” changes to the data headings.
- In the source data table, change the heading case – I changed DAY to Day
- 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
If the Excel worksheet is visible in the background, you might see an alert at the top of the sheet:
- DATA MODEL CHANGED Select the workbook to get these changes
- Close the Power Pivot Window, and the revised field name should appear in the pivot table, and in the PivotTable Field List.
- Refresh the pivot table, and 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
Summary Functions
Data Source
Pivot Table Errors
______________________
Excel Pivot Table Refresh Error with Data Model
_______________
Excel Pivot Table Refresh Error with Data Model is a post from Contextures Blog and is not allowed to be copied to other sites