Analyse in Excel - how to refresh manually overwritten values in pivot table

[Anonymous]

Hi,

I'm using 'Analyze in Excel' to create pivot tables connected live to a data model in PBI Service (Premium).

I recently noticed that users can overwrite any cell values in the pivot table. Unfortunately those new values are permanently retained even after refreshing the pivot table afterwards.

We already tried various things to remove the impacted column from the pivot table, then refresh (or refresh-all), then add it back.

Or save and close the workbook without the impacted column, open it again, then add it back and refresh.

It seems that the 'manually' replaced value is cached somewhere. But I cannot find any option to clear this cache.

Or is there something else going on? If this is meant to be a 'feature', it's a very horrible feature. I don't see what value it should have to overwrite data manually, when you cannot fix it be simply refreshing it.

In the example below, I just recreated the issue by fetching records from the calendar table. After overwriting 'FY22-Jul' with 'aaaa', there is no way to get back to 'FY22-Jul'. It just seems to impact the display in the pivot table grid though. In the Filters or Slicers, the correct value 'FY22-Jul' is always visible.

P.S. The FY-Month attribute is sorted in the data model by the FY period (1=Oct, 2=Nov,...). This sort order is correctly applied in the pivot table grid, but seems to be ignored in filter and slicers. Feature or bug?

Installed AiE version: x64_16.0.695.0_SQL_AS_OLEDB.msi

OS: Windows 10 Enterprise (v10.0.19044) - 64bit

Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.21008) 64-bit

[hannahhillarys]

Did anyone find a solution to this please?

[Hariharan_R]

Hi @Anonymous 

You also can use clear option.

Thanks

Hari

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

My Blog :: YouTube Channel :: My Linkedin

[lbendlin]

In the Pivot table options you can choose to store the data or not - switch that off. When you close and reopen the file the cache should be flushed.

for your PS:  yep, bug, sort of.  "Analyse in Excel" doesn't know anything about the "sort a coumn by another column" feature of the Power BI dataset.