Nov 18 2020 05:17 AM
Nov 18 2020 05:17 AM
I am getting the following error in Excel.
"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').
I have found various articles that have suggested going through and checking the source data headings. I did this and nothing seems to be out of the ordinary. https://contexturesblog.com/archives/2020/03/26/excel-pivot-table-refresh-error-with-data-model/
Is there anyone that can help me? The document is fairly large and used frequently by staff. I'd like to avoid recreating it, or tearing it apart. If possible.
Nov 20 2020 02:20 AM
You shall rename same field which you renamed in source table.
Let take your example. In source table we have column DAY and we added this field to PivotTable. More exactly data are added to data model.
In source table we rename DAY to Day and refresh data. In data model it will be added one more column Day 1 since data model is case insensitive. If you check PivotTable you see that it keeps connection to DAY, not Day 1.
In Power Pivot let rename DAY to Day and keep Day 1 as it is
Now refresh Error disappeared. Field Day 1 as well.
Oct 18 2023 10:53 AM
I'm testing my new laptop with Microsoft 365. My excel models were built in Excel 2016 environment, and when I try to refresh the pivot tables, I get the error message below:
"Error during rename of script measure 'Sum of Current Rate2' to new name 'Sum of Current Rate 2" already exists in the script measure collection."
The pivot tables were created from a data range within the workbook, and there are two columns with the same name "Final Rate" and "Current Rate", in Excel 2016, the column title in data model for the second Final rate column is automatically updated to Final Rate2 and Current Rate2, but in Microsoft 365, it is Final Rate 2 and Current Rate 2, with a space between Rate and 2.
I recreated a pivot table using the same data range and added to data model (in Microsoft 365), in the data range, it shows column title Final Rate 2. I saved the file then opened it in my old laptop with Excel 2016, when I try to fresh the pivot table, it gives me an error below:
" An unexpected error occurred (file ' pcassemblycommand.cpp', line 199, function 'PCCalcProps::UpdateIdOfObject') "
To sum up the issue:
1. The workbook that I created in Excel 2016, when I refresh pivot tables in Microsoft 365, it shows error due to the auto column title change - adding a space in the name, which is different from what excel was doing in Excel 2016.
2. If I create a same model in Microsoft 365, when I refresh the pivot table, I get error message.
I have other team members using the same model, and we also share the model with internal business partners. I must make sure the model works well in both 365 and 2016.
Who can help with this issue? Could anyone on the support team or development team help to fix this issue?
Oct 19 2023 12:38 PM
On this forum are people who have no contacts with support team. Just users who have different experience and who is willing to share such experience. answers.microsoft.com forum is more oriented on support.
Another option for us is on ribbon Help->Feedback-Report a problem. Such feedbacks are monitored by Microsoft, but don't expect any answer or quick fix.
And of course, paid support is always an option. With it you definitely will be answered.
Back to your issue. I have no 2016 to test, but I'd try to use explicit measures removing implicit ones. Other words, don't drag Final Rate 2 into the Values of PivotTable pane. Instead create in Power Pivot measure like
Final Rate 2, Total:=SUM( MyTable[Final Field 2] )
and use it in PivotTable. With renaming of the field on Final Rate2 measure shall pickup such change and continue to work. Implicit measure in such case will disappear. In general it's a best practice to use explicit measures only and avoid implicit measures. Again, I didn't test this with 2016, jus played with renaming.
It could be another compatibility issues with old versions, some are widely known, some not.
Oct 19 2023 01:06 PM