Excel Pivot Table Refresh Error with Data Model

Excel Pivot Table Refresh Error with Data Model

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?

powerpivotfieldsdups02

Here’s an edited version of that message, with some of the text moved, so you can read all of it.

powerpivotfieldsdups02b

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

powerpivotfieldproblem01

powerpivotfieldproblem02

  • Create a pivot table from the table, and check the box to Add to Data Model

powerpivotfieldproblem03

  • 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

powerpivotfieldproblem04

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

powerpivotfieldproblem05

  • Right-click on a pivot table cell, and refresh the pivot table
  • The first refresh should be OK

powerpivotfieldproblem06

  • Next, refresh the pivot table again
  • This time you should see the error message that mentions the function, PCMinorObjectCollection

powerpivotfieldproblem07

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)

powerpivotfieldproblem08

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

powerpivotfieldproblem12

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

powerpivotfieldproblem10

  • In the Power Pivot for Excel window, on the Home tab, click the Design View command

powerpivotfieldproblem13

  • In the diagram, right-click on the field name that you changed, and click Rename

powerpivotfieldproblem14

  • Type the field name, the way that you entered it in the source data table – I changed DAY to Day

powerpivotfieldproblem15

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

powerpivotfieldproblem16

  • 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

pivotrefresherrordatamodel01a

pivotrefresherrordatamodel01b

_______________

27 thoughts on “Excel Pivot Table Refresh Error with Data Model”

  1. I have a similar problem but i did not change the header. instead i changed the format of the column (kept it as a Date, but went from one date format to another). As soon as i did that, i get a similar error message. Do you know how to resolve?

    “We couldn’t get the data from ‘Calendar’ in the workbook “filepath to my worbook”

  2. Thank you !!. I have fixed the Pivot Table error by changing the suspected wrong titles of the worksheet table.

    ERROR: “An unexpected error occured (file ‘pcminorobjcoll.inl’, line 115, function ‘PCMinorObjectCollection::SetNameAndUpdateCollection’)”

  3. Nice sharing! But I totally lost track which header/title that I change that caused the error 🙁
    Do you have any idea how to find it?

  4. Autodesk Inventor doesn’t accept case changes. The solution is to change the name (add a character) then change it again. That might be an easier solution in this case too, though I haven’t tried it.

  5. I cannot tell you how much you rock for posting this. My computer auto-capitalized the first letter in one field name and blew up my day. This fixed it instantly. Thanks!

  6. Wow. just wow. Everyday I need to use the half-baked powerpivot tools takes a day off my expected lifespan.

    These tools are so insanely brittle that they shouldn’t be used for anything but adhoc one-off applications. After inheriting a workbook linking 9 tables in a single excel file, I’m at the point of needing to rebuild the damn thing from scratch.

    Nevermind that the documentation for these features is thin if not non-existent.

  7. If anyone got below error, find the solution below,

    We couldn’t get data from the Data Model. Here’s the error message we got
    ‘AttributeRelationship’ with ‘AttributeID’ = ‘Date (Month Index)’

    Click “Power Pivot” menu then click “Manage”.
    then from the table in “Power Pivot for Excel” window, delete last column(s) created by Data Model.
    Close the window and refresh.

  8. Thank you so much for sharing this! I am new to Power Pivot, but am quickly coming to rely on it for a lot of ad hoc work; it’s great to know that there are other explorers who provide a lifeline!

  9. I got this pcminorobjcoll.inl error message in a file but it wasn’t caused by a renamed field.
    I made a copy of the file and deleted all then Power Query queries, then many sheets until I found which one was causing the error. Then I deleted all the rows, so the sheet was blank and I got a different error message which led me to the solution – under Data, Queries & Connections I finally looked at the Connections tab – there was an old connection which couldn’t be updated! I deleted it and the error was gone. Then I fixed this in the main file. I hope this helps others!

    1. This fixed it for me! In the data ribbon, I clicked Queries & Connections. In the Queries & Connections pane, I clicked the Connections header. I refreshed each query one by one until I found the culprit. When I deleted that query, I didn’t get the error message anymore. Thanks Gary!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.