Forum Discussion
Excel Bug Causing Corrupt Pivot Table when datasource is added to data model
I believe I've come across a bug in Excel and I'm not sure where to go with it. I'm hoping to find a workaround, but I'd at least like to see if anybody can actually confirm that this is a bug. This has been a difficult one to explain, but I'm hoping that my attached sample files may provide a simple reproduction of the bug.
My Office365 admins referred me here to see if I can get any help.
Background: My company uses hundreds of pivot tables that query data from our SQL Server. In order to add Measures/DAX Functions and PowerPivot add-in, I have been choosing to "add this data to the data model" to take advantage and add functionality to my pivot table reports.
Bug: I've recently noticed that if a pivot table (with a data source included in the data model) is filtered and a refresh of the data no longer contains the rows with data that was originally selected in the filter, the pivot table stops responding entirely. It no longer refreshes, allows me to move columns around, drilldown, etc.
Current workaround: This forces me to either clear all filters, expand/collapse all rows prior to refreshing, or not use the data model entirely and lose out on the ability to add DAX measures. If a user forgets to clear filters, the file can be restored from a previous version.
Why workaround isn't feasible: These pivot table reports are used by a variety of users throughout the company on a daily basis. Ensuring that every one of these users make sure to clear filters, expand/collapse all on every pivot table before refreshing is likely impossible. Inevitably, a user will fail to do this and the pivot table report/data model link will end up corrupt and a previous version of the file will need to be restored.
Description of attached files:
I've created two examples of pivot tables in separate xlsm files. One has its pivot table datasource included in the data model. The other does not. The datasource of both pivot tables is a simple csv file called DataSource.csv. There are two csv files included. One contains data for "Germany" and one contains data for "USA". Simply toggling the file name of a CSV to datasource.csv and renaming the other to a backup filename can allow a simple reproduction of the bug.
Steps to reproduce:
1. Download attached sample files.
2. Open Excel_NOBUG_NODATAMODEL.xlsm (this file does NOT include its datasource in the data model)
3. Note a bit of the filtering, drilled-down fields, etc.
4. Refresh pivot table.
5. Pivot table will now switch from "Germany" data to "USA" data. This works!
6. Open Excel_BUG_DATAMODEL.xlsm (this file uses the same datasource which has been added to the data model).
7. Note that the filtering, drilled-down fields are the same as the previous file.
8. Refresh pivot table.
9. Pivot table will now "break". You can no longer change your filters, expand/drilldown etc.
10. Rename DataSource.csv to usa_DataSource.csv
11. Rename germ_DataSource.csv to DataSource.csv
12. Excel_BUG_DATAMODEL.xlsm will now refresh properly.
13. Clear all filters, expand all fields in the pivot table.
14. Rename DataSource.csv to germ_DataSource.csv
15. Rename usa_DataSource.csv to DataSource.csv
16. Since filters and drilled down fields were cleared up, pivot table will now refresh successfully.
Can anybody else reproduce this?
8 Replies
- I was having similar issue while using any Data Model based Pivot Table and, in my case, the Excell was crashing after some time when i tried to save the file. I reverted my version of Office to 16.0.15128.20224 which was released on May 10 and the bug is gone. My pivot tables are refreshing and Excell is not crashing while saving. Thanks to your post for the idea.
I can't reproduce these steps:
===
6. Open Excel_BUG_DATAMODEL.xlsm (this file uses the same datasource which has been added to the data model).
7. Note that the filtering, drilled-down fields are the same as the previous file.
8. Refresh pivot table.
9. Pivot table will now "break". You can no longer change your filters, expand/drilldown
===
Open the file, PivotTable has data for Germany and data model for USA. Open any filter, in drop-down I see unselected items for USA (as it shall be, they are taken from data model). Select any few items, PivotTable takes data from data model (for USA) and shows selected items.
On which version of Excel you are?
- I have added an MP4 video demonstration of the bug.
SergeiBaklan I am running Version 2110 (Build 14527.20276)
Thank you for your response! I do feel like perhaps this bug is relatively new as I've been working with pivot tables, data models, measures for a long time and never seemed to notice it before.
With all files downloaded exactly as they are, if I open Excel_BUG_DATAMODEL.xlsm on my computer (or any in my organization), it will not refresh and change the data over to USA data.
Your version is good enough. I'll try bit later to play with configurations if will be able to reproduce the bug.
