Data Model error

[Erin412]

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. 

[#2]

@Erin412 

For me renaming of columns in data model works, main issue here is to rename correctly proper columns.

If that's the same error reason.

[#3]

@Sergei Baklan 

How did you determine what columns needed renamed? How do I know what's incorrect vs. correct? 

[#4]

@Erin412 

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.

[#5]

@Sergei Baklan 

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?

[#6]

@TianlinZhu 

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. 

[#7]

Thank you so much for the response. You were 100% right on the implicit measure issue. I'll update the model and remove the implicit ones. I tested refreshing the pivot table that I created from the newer version excel, and I got errors. I guess avoiding duplicate column title names could be a solution. Thank you again for the answer. I appreciate it.

[#8]

@TianlinZhu 

Glad it helped. 2016 is far beyond of my experience, almost forget it. However, general principals are the same. In particular nothing implicit.

[#9]

Happy New Year! Thank you so much for all your help. God bless you!

[#10]

@Sergei Baklan you are the god? <3

Fixed my Problem!

[#11]

@Robin1999 

Glad it helped, thank you for the feedback.