In a previous post, I used a recent PowerPivot issue of mine to highlight some great Microsoft Technical Support I received from a member of the Excel team, Ms. Susie Martin.
As far as the actual bug, we did come to a resolution on this yesterday and I wanted to share it in case it helps others which oddly since I could not find others who had experienced it may mean that I was the only one dumb enough to find a way to cause it – both a source of pride and embarrassment (now if we got PowerPivot t-shirts for finding bugs – it would be all pride) but anyway.
Periodically I could do something in my PowerPivot workbooks that would stop the PowerPivotFieldList from populating and it would display empty as below. And then you are done maintaining slicers, maintaining measures, etc becomes impossible.
What caused the issue?
After researching this for just about 3 weeks, the Excel Technical Support Escalation team reported that the underlying issue was a measure which had duplicated the Display Name of another Measure – and that this was raising an internal exception in the code, and that is why the Field List would no longer populate – and since then you can no longer visit the Display names of the Measures – it was then game over once you saved the worksheet and then tried to come back in later.
The side effects?
The odd thing is that once this happened, even displaying PowerPivot worksheets that were just fine or even had no defined measures, would sometimes display the same symptom of having an empty PowerPivot Field List and so we know there was something being “tripped” at the App level. So then I would randomly experiment with PowerPivot worksheets that did work until my PowerPivotFieldList would work again – sometimes it could take an hour of hacking around but one individual (mak.m) in MSDN Support forum actually suggested a unique solution that did work for me on the first try when I was able to recreate the original issue with an offending worksheet.
but basically this person suggested I toggle the Language setting in the PowerPivot settings button to something else (I selected Taiwan since it was at the top), exit Excel, and then come back in and set it to English, and that did work the first time I tried it on a spreadsheet which had no duplicate measure Display Names but yet the PowerPivot Field List had gone blank.
(When doing this you have to be careful in remembering which button is the “Yes” button when the language is changed See what I mean below? Maybe I should not have selected “Taiwan” as the language to toggle between English with – I bet there is a much more “english-friendly” language option to toggle between but this was kind of fun )
And forcing the Language to refresh on the PowerPivot ribbon strip and PowerPivot windows seemed to do the trick. Weird but I have to admit – effective.
Below is the final response I got back from Microsoft. Now I admit that allowing 2 of my measures to get named to the same Display Name was certainly bad form on my part – however the silent response by Excel in that case and providing no way out is certainly problematic. But at least now I know how it is caused, and how to avoid it in the future.
(in my specific case I had 2 measures – one measuring “Year over Year Gains”, and the other “Year over Year Percentage Gains” that had both been accidentally given the same friendly Display Name “Year over Year Growth” – doh! )
Microsoft’s response to this matter
So, the new thing I learned today?
NEVER allow Measures to duplicate their Display Name in a PowerPivot worksheet
Hope this helps someone else who happens to search for this very situation. Thanks to the Excel team for helping me understand this maddening situation and how to avoid.