Some more PowerPivot oddities to share tonight based on issues I had encountered while building dashboards.
For returning readers, you may remember how not long ago I was plagued by a PowerPivot Slicer issue where the PowerPivot Field list would go completely blank and unusable.
Well I have 2 issue/quirks to share tonight and the first was bringing back some bad memories of that previous error.
First issue (Slicers missing from PP Field List)
During a demo to a client, we suddenly realized that the criteria we had selected as Horizontal and Vertical slicers in the Power Pivot Field List were no longer visible on any of our worksheets – yikes!– new worksheets to this Excel spreadsheet were OK, and the Slicers on those previous sheets still functioned perfectly – they just did not display in the Power Pivot Field list and so were no longer manageable. The Power Pivot Field list ends up looking like this, even though there are a few Horizontal and Vertical Slicers that had been previously configured:
Thankfully, I came across Chris-BI’s blog post and his subsequent bug report entered into Microsoft’s Connect database
He goes on to describe the bug in such fine detail, and then how to reproduce it, and his workaround – and I was so thankful that:
- It wasn’t just me
- Someone could show me how to reproduce it
- And that there was a workaround
His workaround involves rebuilding your PowerPivot worksheet from scratch but this time make sure that EVERYTIME you enter the worksheet to always click on a PivotTable, PivotChart or a Slicer before going straight into the PowerPivot window to see the underlying data – many is the time when all I want to do is open a worksheet and go straight to the PowerPivot screen to refresh the underlying data, and it is believed that causes the bug to become enabled, and once you save the worksheet and come back in, you may find your Slicers to be missing from the Power Pivot Field List – so far, I have followed his advice since rebuilding dashboards, and so far no problems. Its clumsy but it’s a workaround.
If you encounter this or want to see any Microsoft responses, his Connect issue is located here. Chris-BI – many thanks for not just finding this but documenting it for others! And Microsoft – if this is not on the list to get fixed, you are scaring the hell out of me thinking what bigger bugs are there that require more attention than issues like these but I still love your tool and will work around whatever it takes to use it.
Second Issue (Conditional Formatting Disappearing from PivotTables)
Some time back I had noticed that sometimes my PowerPivot worksheets were not retaining their conditional formatting after refreshing – I even entered a Microsoft Connect issue to report this very behavior and now this week, I have figured out what triggers this as well.
Let’s say that I have a column of some numbers I wish to highlight and add some conditional formatting
(See how I actually extended the formatting area just beyond the last column – that becomes important)
Then I add some formatting rules such as these – Making cells with positive values to be shaded green and those with negative values to be shaded red…
And then just as things are looking good like this…..
you try to Refresh the data
and your formatting is suddenly gone and looking like this
There were cases where I had Dates along the x-axis and I had extended the formatting region to cover some future expansion columns (and other cases when I just accidentally included an extra column next to the Pivot Chart). When you do that, the Conditional Formatting disappears once you refresh the data (even if you have selected the “Preserve Cell Formatting On Update” option in the PivotTable options).
Lesson – When Applying Conditional Formatting, apply only to the actual cells populated by that PivotTable – otherwise it will seem to work until you refresh and it disappears.
Last thing I’ll mention tonight is some time ago, I mentioned the value in picking up some Excel skills. I mean look at me….Christmas tree images in my bar graph below (for an employee chart on annual results of our charity drive – don’t worry, this was not in Production )
One area that irritates me is Pie Charts with too many values
Ideally there would be an option to merge all the tiny values into a piece of pie labeled “Other” but found a workable solution the other day when I stumbled across the Filter/Sort option for the data in this chart (actually found it while changing the chart type to Bar Graph), and there it was, in the Sort and Filter options
There I could select a filter of the Top 10 values and clean up my piechart
It has the obvious limitations in that the next refresh of data may require me to change it to allow more or less in that filter and it actually leaves out the tiny pieces you are filtering out so the proportions are no longer exact – but who cares, it’s a pie chart, right? (ok, I rarely think a pie chart is a good choice but it does happen once in rare while – the gist of this section is that I did not know I could apply filtering inside a pivot chart, I had only applied them to pivot tables previously ) but it reminded me again of how little I really know all of the options inside of Excel….and how I continue to get more dangerous each day.