Microsoft has released a couple of long over due fixes to some long standing Excel 2013 bugs, both of which have been quite painful in their own way. Unfortunately for me the timing couldn’t have been worse. I literally just placed the order for the print run of my new book Learn to Write DAX, and I explicitly talk about the KPI bug in my book – and now it seems it is fixed. I should just be grateful for the fixes I guess, so let me tell you about them.
The KPI Bug
Ever since Microsoft released Excel 2013, the KPI feature that comes with Power Pivot has not worked properly.
KPIs worked in Pivot Tables in Excel 2010 and also in Power View in Excel 2013, but not in Pivot Tables in Excel 2013. Personally I went through a lot of pain trying to work out how KPIs actually worked, and it wasn’t until I had tried many times that I finally realised there were bugs.
In short the KPI feature allows you (more correctly – should have allowed you) to create a kind of graphical Conditional Formatting based on a target you set in the data model (see example below). In the example shown I have set an absolute target for [Margin %] of 45%. When the [Margin %] measure falls below 40% it will display a red X; when between 40% and 45% it will display a yellow !; and when above 45% it will display a green tick.
You can also set a KPI based on another measure (calculated field) such as [Budget], and this has potential to be useful. But in Excel 2013 Pivot Tables it only partially worked – there were 2 issues.
The Icon Bug
It is best practice to select an icon that can be interpreted by people that are colour blind (or when printed in black and white) – eg the icon set shown above. But in Excel 2013 Pivot Tables it didn’t matter which icon set you selected in the above dialog box, you ALWAYS got the first set of round coloured icons. These icons are really bad for colour blind people and black and white print as it is impossible to tell the icons apart (same with the coloured flags and the round coloured dots with black borders). Plus they are just not as good as some of the other icon sets either.
The Try Again Bug
Then when you created the KPI while you had a Pivot Table selected, the Icon would automatically be added to the pivot table after the measure was created (like any other measure), but it would show as a -1 or 1 in instead of the icon – like shown below. If you didn’t give up at this point, but instead removed the KPI from the Pivot Table and then added it back again, then it worked – but of course only the coloured round icon due to the first bug, regardless of the icon you actually selected.
Anyone that actually stuck with KPIs in Excel 2013 Pivot Tables through all this pain would finally end up with a pivot table as shown on the left below. The good news is that the bug is now fixed, and you now get what is shown on the right.
The Dreaded Power Query Bug
The second “bug” is technically not a bug, just an unplanned consequence of building Power Query and Power Pivot in parallel both as separate Add Ins. I wont go into a deep discussion about the bug in this post as you can read about it here. But in short Microsoft has created a fix that explicitly prevents you from altering a table created in Power Query when you are inside Power Pivot. If you try to change a Power Query table inside Power Pivot, you now get a polite message telling you to go to Power Query and make your changes there – problem solved.
I should point out that it is perfectly fine to add calculated columns to your Power Query tables within Power Pivot. But if you want to change the structure of the base table in any way, then you are now forced to do it in Power Query, and this saves you from the perils of the Power Query bug – thank goodness
You can read about the updates and download the patches (if you need to – eg if you are not on o365) here https://blogs.office.com/2015/11/10/power-pivot-in-excel-2013-november-2015-customer-update/
if you want a comprehensive lesson on how to use Power Query, checkout my training course here http://xbi.com.au/pqt