Excel Pivot Table Refresh Error with Data Model

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?

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

  • Format the list as a named Excel table.

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

_______________

Excel Pivot Table Refresh Error with Data Model is a post from Contextures Blog and is not allowed to be copied to other sites

Original source: https://contexturesblog.com/archives/2020/03/26/excel-pivot-table-refresh-error-with-data-model/?utm_source=rss&utm_medium=rss&utm_campaign=excel-pivot-table-refresh-error-with-data-model

Leave a Reply

Close Menu