frequent error when refreshing pivot table

Question

frequent error when refreshing pivot table

Eric Brinsfield 6

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?

Mohan Tamang 0 Reputation points

2024-07-10T04:07:21.7633333+00:00

best answer..
Rob Beaumont 0 Reputation points

2025-05-06T15:55:18.6566667+00:00

I also have the same problem. I downloaded this budgeting excel sheet from the microsoft site

https://create.microsoft.com/en-us/template/family-budget-(monthly)-2d4c31d3-eae7-4e19-9115-df89758755cc

I saved the file locally and then opened it up. I saved another version with the same name in a different location. I then changed a few values (not field names) updated ok. So then I changed a few more values and added line items in some tables. It still refreshed the data ok. So I went further and customized the row values further to things I need, saved it . Then got the error. Easy I thought its a field name value issue. Backed out all those but still the same error. So I opened the fresh downloaded version which I hadn't changed anything on, still the same error, Closed down excel, re-opened ...still the same error. AAAAAarrghh.

Deleted original download, back to the Microsoft link, redownloaded it. STILL THE SAME ERROR...

This is crazy, it must be a bug but I can't get round it now.

Anyone from Microsoft care to help? its your template, from your site on a windows 11 PC, Microsoft 365.

Thanks

Rob

3 answers

Your answer

Mohan Tamang 0 Reputation points

2024-07-10T04:07:21.7633333+00:00

best answer..
Rob Beaumont 0 Reputation points

2025-05-06T15:55:18.6566667+00:00

I also have the same problem. I downloaded this budgeting excel sheet from the microsoft site

https://create.microsoft.com/en-us/template/family-budget-(monthly)-2d4c31d3-eae7-4e19-9115-df89758755cc

I saved the file locally and then opened it up. I saved another version with the same name in a different location. I then changed a few values (not field names) updated ok. So then I changed a few more values and added line items in some tables. It still refreshed the data ok. So I went further and customized the row values further to things I need, saved it . Then got the error. Easy I thought its a field name value issue. Backed out all those but still the same error. So I opened the fresh downloaded version which I hadn't changed anything on, still the same error, Closed down excel, re-opened ...still the same error. AAAAAarrghh.

Deleted original download, back to the Microsoft link, redownloaded it. STILL THE SAME ERROR...

This is crazy, it must be a bug but I can't get round it now.

Anyone from Microsoft care to help? its your template, from your site on a windows 11 PC, Microsoft 365.

Thanks

Rob

Answer 1

Emily Hua-MSFT 27,796

@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.

Eric Brinsfield 6 Reputation points

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.
Emily Hua-MSFT 27,796 Reputation points

2021-05-12T08:12:32.22+00:00

@Eric Brinsfield
Thanks for your reply.
Does this issue occure with all Power Pivot Table?
I need you provide some screenshots that show your fields and data source.
If there is any privacy information, please cover it.
Eric Brinsfield 6 Reputation points

2021-05-12T16:45:46.347+00:00

OK. Let me see if I can isolate the situation and I will send you a sample Excel sheet to demonstrate the problem.
This may take a few days.
Emily Hua-MSFT 27,796 Reputation points

2021-05-24T08:26:04.013+00:00

@Eric Brinsfield
I am checking this thread, if there is any update, welcome to post back.
Eric Brinsfield 6 Reputation points

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.
Eric Brinsfield 6 Reputation points

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
Eric Brinsfield 6 Reputation points

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
Eric
Henry Phan 0 Reputation points

2024-04-15T07:37:21.71+00:00

I have same issue. Last weekend my file

My file have lot of pivot, and when i check refreshing one by one -> no error, but when i use refresh all function, error message pop up.

i'm using Office 2021.

Answer 2

Mario González P 0

Kinda late, but this is how I solved a similar problem (I had the same error, refreshing on data model wasn't working either).

I noticed I had a previous pivot table based on a previous version of my query (I had modified/replaced some columns most likely). So I deleted that pivot table, and then it worked! I could update correctly my new pivot table, as simple as that.

Cheers

Answer 3

ARAYA CADIZ Yerko (ENGIE ENERGÍA CHILE) 0

Hello, solved.

This is the URL https://www.pivot-table.com/2020/10/28/excel-data-model-pivot-table-refresh-error/

Share via

frequent error when refreshing pivot table

3 answers

Your answer