Most Pivot Table Fields Disappear on Refresh/Refresh All

[S_Abdullah]

I have created a dashboard from pivot tables of demo data on COVID-19. Initially the pivot tables were from solely created from one data sheet but later other pivot tables (and charts) needed data from a second sheet. Excel created a Data Model to do this (have pivot tables from different source sheets). Now, when the data is refreshed, for one table or using refresh all, most of the fields for the numerous pivot tables disappear. (There doesn’t seem to be any pattern to the field/tables that remain.)

How do I stop this (the fields disappearing) from happening?

Note: “Autofit columns on update” has been disabled.

 

[Alex Blakenburg]

Are the fields that are disappearing value fields or row/column fields ?
The most likely reason is that the column names changed.
The value fields are more likely to disappear the row/columns seem to be slightly more tolerant.

How are the underlying tables being refreshed is it possible that the headings have changed ?

 

[S_Abdullah]

The value fields disappear but column fields do as well.
I did need to add 3-5 new columns after I'd started creating pivot tables, so some columns moved, and needed to edit some column names due to typos. Everything seemed to have shifted without issue when I made the changes.
Does this mean I need to redo every pivot table and chart? (There are about 75 pivot tables!)

 

[Alex Blakenburg]

In the source table(s) moving columns didn't seem to worry it and renaming non-value columns didn't seem to worry it either, even when the name was used in a relationship.
Changing the name of a value column did lose the data from the pivot. (I didn't test using Explicit measures)

I think if you have lost data it would seem likely you will need to rebuild the pivots.
I would be interested to know what you are doing to end up with 75 pivot tables on the one set of data.

 

[S_Abdullah]

In the source table(s) moving columns didn't seem to worry it and renaming non-value columns didn't seem to worry it either, even when the name was used in a relationship.
Changing the name of a value column did lose the data from the pivot. (I didn't test using Explicit measures)

I think if you have lost data it would seem likely you will need to rebuild the pivots.
I would be interested to know what you are doing to end up with 75 pivot tables on the one set of data.

Yes, it does seem like I can't avoid recreating every pivot.

(I'm making a dashboard of COVID-19 data with 5741 individuals and almost 100 indicators.)

 

[Alex Blakenburg]

I don't think anything with that many pivots can be considered a dashboard
Typically you have a summary Pivot & Chart and then you select what you want to drill further down on.
You might want to look at using Power BI for your application.

 

[S_Abdullah]

I'll check into it.