cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FranckGbadamass
Frequent Visitor

Help Pivot Tables caches!

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 QueryEmpty table from Power Query

Pivot Table pic with deleted data appearingPivot 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.

FranckGbadamass_2-1659724310777.png

 

Thanks in advanced for your help.

1 REPLY 1
jennratten
Super User
Super User

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 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors