Excel Bug Causing Corrupt Pivot Table when datasource is added to data model

%3CLINGO-SUB%20id%3D%22lingo-sub-2977851%22%20slang%3D%22en-US%22%3EExcel%20Bug%20Causing%20Corrupt%20Pivot%20Table%20when%20datasource%20is%20added%20to%20data%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2977851%22%20slang%3D%22en-US%22%3E%3CP%3EI%20believe%20I've%20come%20across%20a%20bug%20in%20Excel%20and%20I'm%20not%20sure%20where%20to%20go%20with%20it.%26nbsp%3B%20I'm%20hoping%20to%20find%20a%20workaround%2C%20but%20I'd%20at%20least%20like%20to%20see%20if%20anybody%20can%20actually%20confirm%20that%20this%20is%20a%20bug.%26nbsp%3B%20This%20has%20been%20a%20difficult%20one%20to%20explain%2C%20but%20I'm%20hoping%20that%20my%20attached%20sample%20files%20may%20provide%20a%20simple%20reproduction%20of%20the%20bug.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20Office365%20admins%20referred%20me%20here%20to%20see%20if%20I%20can%20get%20any%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EBackground%3A%3C%2FSTRONG%3E%20My%20company%20uses%20hundreds%20of%20pivot%20tables%20that%20query%20data%20from%20our%20SQL%20Server.%26nbsp%3B%20In%20order%20to%20add%20Measures%2FDAX%20Functions%20and%20PowerPivot%20add-in%2C%20I%20have%20been%20choosing%20to%20%22add%20this%20data%20to%20the%20data%20model%22%20to%20take%20advantage%20and%20add%20functionality%20to%20my%20pivot%20table%20reports.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EBug%3A%3C%2FSTRONG%3E%20I've%20recently%20noticed%20that%20if%20a%20pivot%20table%20(with%20a%20data%20source%20included%20in%20the%20data%20model)%20is%20filtered%20and%20a%20refresh%20of%20the%20data%20no%20longer%20contains%20the%20rows%20with%20data%20that%20was%20originally%20selected%20in%20the%20filter%2C%20the%20pivot%20table%20stops%20responding%20entirely.%26nbsp%3B%20It%20no%20longer%20refreshes%2C%20allows%20me%20to%20move%20columns%20around%2C%20drilldown%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ECurrent%20workaround%3A%3C%2FSTRONG%3E%20This%20forces%20me%20to%20either%20clear%20all%20filters%2C%20expand%2Fcollapse%20all%20rows%20prior%20to%20refreshing%2C%20or%20not%20use%20the%20data%20model%20entirely%20and%20lose%20out%20on%20the%20ability%20to%20add%20DAX%20measures.%26nbsp%3B%20If%20a%20user%20forgets%20to%20clear%20filters%2C%20the%20file%20can%20be%20restored%20from%20a%20previous%20version.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EWhy%20workaround%20isn't%20feasible%3A%26nbsp%3B%26nbsp%3B%3C%2FSTRONG%3EThese%20pivot%20table%20reports%20are%20used%20by%20a%20variety%20of%20users%20throughout%20the%20company%20on%20a%20daily%20basis.%26nbsp%3B%20Ensuring%20that%20every%20one%20of%20these%20users%20make%20sure%20to%20clear%20filters%2C%20expand%2Fcollapse%20all%20on%20every%20pivot%20table%20before%20refreshing%20is%20likely%20impossible.%26nbsp%3B%20Inevitably%2C%20a%20user%20will%20fail%20to%20do%20this%20and%20the%20pivot%20table%20report%2Fdata%20model%20link%20will%20end%20up%20corrupt%20and%20a%20previous%20version%20of%20the%20file%20will%20need%20to%20be%20restored.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EDescription%20of%20attached%20files%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI've%20created%20two%20examples%20of%20pivot%20tables%20in%20separate%20xlsm%20files.%26nbsp%3B%20One%20has%20its%20pivot%20table%20datasource%20included%20in%20the%20data%20model.%26nbsp%3B%20The%20other%20does%20not.%26nbsp%3B%20The%20datasource%20of%20both%20pivot%20tables%20is%20a%20simple%20csv%20file%20called%20DataSource.csv.%26nbsp%3B%20There%20are%20two%20csv%20files%20included.%26nbsp%3B%20One%20contains%20data%20for%20%22Germany%22%20and%20one%20contains%20data%20for%20%22USA%22.%26nbsp%3B%20Simply%20toggling%20the%20file%20name%20of%20a%20CSV%20to%20datasource.csv%20and%20renaming%20the%20other%20to%20a%20backup%20filename%20can%20allow%20a%20simple%20reproduction%20of%20the%20bug.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESteps%20to%20reproduce%3A%26nbsp%3B%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E1.%26nbsp%3B%20Download%20attached%20sample%20files.%3C%2FP%3E%3CP%3E2.%26nbsp%3B%20Open%20Excel_NOBUG_NODATAMODEL.xlsm%20(this%20file%20does%20NOT%20include%20its%20datasource%20in%20the%20data%20model)%3C%2FP%3E%3CP%3E3.%26nbsp%3B%20Note%20a%20bit%20of%20the%20filtering%2C%20drilled-down%20fields%2C%20etc.%3C%2FP%3E%3CP%3E4.%26nbsp%3B%20Refresh%20pivot%20table.%3C%2FP%3E%3CP%3E5.%26nbsp%3B%20Pivot%20table%20will%20now%20switch%20from%20%22Germany%22%20data%20to%20%22USA%22%20data.%26nbsp%3B%20This%20works!%3C%2FP%3E%3CP%3E6.%26nbsp%3B%20Open%20Excel_BUG_DATAMODEL.xlsm%20(this%20file%20uses%20the%20same%20datasource%20which%20has%20been%20added%20to%20the%20data%20model).%3C%2FP%3E%3CP%3E7.%26nbsp%3B%20Note%20that%20the%20filtering%2C%20drilled-down%20fields%20are%20the%20same%20as%20the%20previous%20file.%3C%2FP%3E%3CP%3E8.%26nbsp%3B%20Refresh%20pivot%20table.%3C%2FP%3E%3CP%3E9.%26nbsp%3B%20Pivot%20table%20will%20now%20%22break%22.%26nbsp%3B%20You%20can%20no%20longer%20change%20your%20filters%2C%20expand%2Fdrilldown%20etc.%3C%2FP%3E%3CP%3E10.%20Rename%20DataSource.csv%20to%20usa_DataSource.csv%3C%2FP%3E%3CP%3E11.%20Rename%20germ_DataSource.csv%20to%20DataSource.csv%3C%2FP%3E%3CP%3E12.%26nbsp%3B%20Excel_BUG_DATAMODEL.xlsm%20will%20now%20refresh%20properly.%3C%2FP%3E%3CP%3E13.%26nbsp%3B%20Clear%20all%20filters%2C%20expand%20all%20fields%20in%20the%20pivot%20table.%3C%2FP%3E%3CP%3E14.%20Rename%20DataSource.csv%20to%20germ_DataSource.csv%3C%2FP%3E%3CP%3E15.%20Rename%20usa_DataSource.csv%20to%20DataSource.csv%3C%2FP%3E%3CP%3E16.%20Since%20filters%20and%20drilled%20down%20fields%20were%20cleared%20up%2C%20pivot%20table%20will%20now%20refresh%20successfully.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anybody%20else%20reproduce%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2977851%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2979755%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Bug%20Causing%20Corrupt%20Pivot%20Table%20when%20datasource%20is%20added%20to%20data%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2979755%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F553989%22%20target%3D%22_blank%22%3E%40mmichaels%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20can't%20reproduce%20these%20steps%3A%3C%2FP%3E%0A%3CP%3E%3D%3D%3D%3C%2FP%3E%0A%3CP%3E%3CEM%3E6.%26nbsp%3B%20Open%20Excel_BUG_DATAMODEL.xlsm%20(this%20file%20uses%20the%20same%20datasource%20which%20has%20been%20added%20to%20the%20data%20model).%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%3CEM%3E7.%26nbsp%3B%20Note%20that%20the%20filtering%2C%20drilled-down%20fields%20are%20the%20same%20as%20the%20previous%20file.%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%3CEM%3E8.%26nbsp%3B%20Refresh%20pivot%20table.%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%3CEM%3E9.%26nbsp%3B%20Pivot%20table%20will%20now%20%22break%22.%26nbsp%3B%20You%20can%20no%20longer%20change%20your%20filters%2C%20expand%2Fdrilldown%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%3D%3D%3D%3C%2FP%3E%0A%3CP%3EOpen%20the%20file%2C%20PivotTable%20has%20data%20for%20Germany%20and%20data%20model%20for%20USA.%20Open%20any%20filter%2C%20in%20drop-down%20I%20see%20unselected%20items%20for%20USA%20(as%20it%20shall%20be%2C%20they%20are%20taken%20from%20data%20model).%20Select%20any%20few%20items%2C%20PivotTable%20takes%20data%20from%20data%20model%20(for%20USA)%20and%20shows%20selected%20items.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20which%20version%20of%20Excel%20you%20are%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2980612%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Bug%20Causing%20Corrupt%20Pivot%20Table%20when%20datasource%20is%20added%20to%20data%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2980612%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20am%20running%20Version%202110%20(Build%2014527.20276)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20response!%26nbsp%3B%20I%20do%20feel%20like%20perhaps%20this%20bug%20is%20relatively%20new%20as%20I've%20been%20working%20with%20pivot%20tables%2C%20data%20models%2C%20measures%20for%20a%20long%20time%20and%20never%20seemed%20to%20notice%20it%20before.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20all%20files%20downloaded%20exactly%20as%20they%20are%2C%20if%20I%20open%20Excel_BUG_DATAMODEL.xlsm%20on%20my%20computer%20(or%20any%20in%20my%20organization)%2C%20it%20will%20not%20refresh%20and%20change%20the%20data%20over%20to%20USA%20data.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mmichaels_0-1637246132867.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F327994iD6A3A8B5A0C6B583%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mmichaels_0-1637246132867.png%22%20alt%3D%22mmichaels_0-1637246132867.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2980772%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Bug%20Causing%20Corrupt%20Pivot%20Table%20when%20datasource%20is%20added%20to%20data%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2980772%22%20slang%3D%22en-US%22%3EI%20have%20added%20an%20MP4%20video%20demonstration%20of%20the%20bug.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2980785%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Bug%20Causing%20Corrupt%20Pivot%20Table%20when%20datasource%20is%20added%20to%20data%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2980785%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F553989%22%20target%3D%22_blank%22%3E%40mmichaels%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20version%20is%20good%20enough.%20I'll%20try%20bit%20later%20to%20play%20with%20configurations%20if%20will%20be%20able%20to%20reproduce%20the%20bug.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2980963%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Bug%20Causing%20Corrupt%20Pivot%20Table%20when%20datasource%20is%20added%20to%20data%20model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2980963%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20able%20to%20confirm%20that%20this%20is%20a%20bug%20based%20on%20a%20recent%20update.%20I%20reverted%20my%20version%20of%20Office%20to%2016.0.14326.20238%20(2108%2014326.20238)%20which%20was%20released%20on%208%2F25%2F2021%20and%20the%20bug%20is%20gone.%20My%20pivot%20tables%20are%20refreshing%20normally.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20going%20to%20continue%20to%20step%20up%20my%20updates%20of%20Office%20manually%20build%20by%20build%20to%20determine%20what%20update%20caused%20the%20bug.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20reverted%20my%20version%20by%20following%20the%20instructions%20here%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Ftopic%2Fhow-to-revert-to-an-earlier-version-of-office-2bd5c457-a917-d57e-35a1-f709e3dda841%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Ftopic%2Fhow-to-revert-to-an-earlier-version-of-office-2bd5c457-a917-d57e-35a1-f709e3dda841%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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?

7 Replies

@mmichaels 

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?

@Sergei Baklan 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.

mmichaels_0-1637246132867.png

 

I have added an MP4 video demonstration of the bug.

@mmichaels 

Your version is good enough. I'll try bit later to play with configurations if will be able to reproduce the bug.

I was able to confirm that this is a bug based on a recent update. I reverted my version of Office to 16.0.14326.20238 (2108 14326.20238) which was released on 8/25/2021 and the bug is gone. My pivot tables are refreshing normally.

I am going to continue to step up my updates of Office manually build by build to determine what update caused the bug.

 

I reverted my version by following the instructions here:
https://support.microsoft.com/en-us/topic/how-to-revert-to-an-earlier-version-of-office-2bd5c457-a91...

@Sergei Baklan I can confirm that this bug first appeared in Version 2109 (Build 14430.20234) released on 9/28/2021.  I have attached a video of the pivot table being refreshed in Version 2108 (Build 14326.20404) that WORKS PROPERLY.  Then a video of the pivot table being refreshed in Version 2109 (Build 14430.20234) that DOES NOT WORK PROPERLY and results in a corrupted pivot table.

 

Thank you so much for asking me what version I was running.  I have been trying to figure out what I've done wrong or a workaround for weeks.  You question about the version at least led me to question whether this was caused by a build update.

 

I'm not sure where to go from here with this bug.  But at least I know what I'm dealing with.

This bug appears to have been resolved in update 14527.20312 and 14701.20226 released early December 2021.