Help Pivot Tables caches!

[FranckGbadamass]

Hello everyone,

I'm struggling with my file.

I've built a data model from PowerQuery to PowerPivot and then created a Pivot Table, but when I delete the data from the source data it still appears on my pivot table.

As you can see below from PQ engine there is no data in my table but in my pivot table I still continue to see CapAll data

Empty table from Power Query

Pivot Table pic with deleted data appearing

I've also done some research on google and find that pvt caches are the issue and to fix it I should change in Pvt options ==> then Data ==> Change retain items from deleted data source from Automatic to None, but in my case, this field is grayed.

Thanks in advanced for your help.

[jennratten · Accepted solution]

Hello - this article explains why your option is greyed out.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/71f2c315-4ff5-4a8e-acec-d99d6caf81cb/powerp... 

This article suggests checking the setting in File - Options - Data. If the "Prefer the Excel Data Model when creating PivotTables, QueryTables and Data Connections.

https://www.mrexcel.com/board/threads/pivottable-option-to-modify-number-of-items-to-retain-per-fiel... 

This article provides the steps for removing the old items manually and automatically.

https://www.contextures.com/xlPivot04.html