frequent error when refreshing pivot table

Eric Brinsfield 1 Reputation point
2021-05-10T19:42:09.177+00:00

When refreshing a Pivot table that is created from another worksheet, I get: An unexpected error occurred (file'pcminorobjcoll.inl', line 109, function 'PCMinorObjectCollection[class PCProperty.class NameHashSupport]::SetNameAndUpdateCollection') This happens every time. I have recreated the Pivot table in multiple ways and it still happens. I saw a discussion on this, but the issue was locked.

The original pivot table works fine. I save it out and when I return and refresh, I get this error situation.
I am using the latest version of Microsoft 365 Business Standard. I am running Excel locally when it happens.
What's up?

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,648 questions
0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Emily Hua-MSFT 27,536 Reputation points
    2021-05-11T05:27:16.07+00:00

    @Eric Brinsfield

    Have you renamed columns in Data Model or in data source?

    Please refer to "Excel Pivot Table Refresh Error with Data Model".
    (Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.)

    We need to keep field names in Data Model are same as the names in data source.

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. Eric Brinsfield 1 Reputation point
    2021-05-11T12:05:21.42+00:00

    No. This does not match my situation. I also found this answer on the web and it does not fit. I simply create the PIvot Table; save it, and refresh it. I get the error.
    I can create a video for you if that helps.

    Either way, the error message you get is totally unacceptable for professional software. If a user does something wrong, the software should tell them that or prevent it.

  3. Eric Brinsfield 1 Reputation point
    2021-05-24T20:40:28.24+00:00

    This still happens. I have not had time to create all of the scenarios that might identify what causes it. In my case, the Pivot Table is reading from a table that is created with a Power Query, which gets updated regularly. I just need to find the time to create a static version and start seeing when the problem starts.

    This happens frequently enough that it is a real problem. It occurs with multiple Pivot Tables.

    0 comments
  4. Eric Brinsfield 1 Reputation point
    2021-05-24T21:02:35.637+00:00

    I have extracted the table into another spreadsheet.
    Deleted all Power Queries.
    Recreated the Pivot Table
    The problem persists.

    I can send you the spreadsheet. I don't mind you seeing the content, but would rather not post this for the world to see. How can I get it to you.
    Thanks
    Eric

    0 comments
  5. Eric Brinsfield 1 Reputation point
    2021-05-25T17:29:56.467+00:00

    OK. I have it working now.
    My query to create the source table is shown in the attached image.

    If I add the table from this query to the data model, the Pivot Table fails.
    If I do not add the table to the data model, it works fine.

    Seems like a bug to me.
    Thanks
    Eric99517-querycodescreenshot.jpg

    0 comments