New measures not appearing in field list

[xls_mycroft]

I have an existing Excel file (2013, 32bit) with a rather complex data model that is populated mainly by PowerQuery as well as some configuration tables in this workbook.

I recently integrated another data source, i.e. one table returned by PowerQuery now contains additional columns. I also added additional measures.

Here's the strange thing: While the new columns appear in the field list of the existing (power)pivot tables, the measures do not! I tried all kind of refreshs (right click on the pivot table, update all in the PowerPivot ribbon, Refresh all in the PowerPivot window, Refresh in PowerQuery query side window) - but none adds the measures.

However, if I add a new PowerPivot table, the measures are included - but only in the new PowerPivot table, not in the other ones. Unfortunately, this approach is not feasible, as rebuilding all the existing pivot tables is a rather large effort that I'd like to avoid.

Did anyone experience this behavior? And if so, is there any solution?

Thanks!

 

[RoryA]

Does this blog post help?

 

[xls_mycroft]

Thanks RoryA - and sorry the delayed response.

I tried that - without any impact. After some bad experience earlier, I now usually manage to leave PowerQuery dataset so that they are not turned read-only...

The whole thing really looks like the data model is corrupt. Here are some more indicators:
* I can add slicers to the "old" pivot table as usual. However, if I try to add a slicer to the new pivot table, I get an error message "The PivotTable report is invalid. Try refreshing the data...". After doing the refresh, the error message disappears - but upon clicking "OK" in the "Insert slicer" dialog, nothing happens
* Same behavior when trying to insert a timeline
* When trying to connect an existing slicer to the new pivot table, I get the same error message as above - even after refreshing the data

Any idea?

 

[RoryA]

Not really. I'd probably tend to agree that it sounds like it's corrupt.

 

[SOQLee]

I am experiencing the same issue with Excel 2016 (Microsoft Office Professional Plus 2016). Would appreciate if anyone could explain why this is happening and how to prevent or fix this.

 

[pjmorris]

the only way I've sorted out a corrupt file is to rebuild it - which is not the most helpful suggestion!

But if your problem is simply missing measures - could it be that they're 'attached' to the wrong query/table in the PivotTable list and hence you've just not seen them? (Sorry, that's stating the obvious, but sometimes I need to be reminded )

 

[SOQLee]

The file I'm working on has become quite complex containing over 100 measures and I needed to add few more.

No changes were made to the data sources or tables designs.

I wrote the new measures in Powerpivot data view; 'Refresh All' in Powerpivot, PowerQuery and Excel views, then went back to the existing report wanting to drag/drop the measures; and couldn't find them listed in the table's field list. I refreshed all again, checked connections, etc.

To sort this out, I rebuilt the report in a new worksheet, where the new measures appeared in the field list. BUT when I toggle back to the previous report; hoping for the same; the new measures were not listed in the fields.

Could the issue have something to do with the number of measures that is allowed? I am really hoping that I don't have to rebuild!