- Excel Book Excerpt
Excel Feature X Won’t Work in PowerPivot
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Feature X Won’t Work in PowerPivot
Problem: I’ve been using pivot tables for a decade. Feature ______ that I use all the time won’t work in PowerPivot.
Strategy: This is why I don’t take every data set through PowerPivot. Say that you have a 10 million row text file. You import that file into Excel. The data in that text file is converted to an OLAP Cube, compressed using a vertical compression technique and inserted into the Excel file as something that the I.T. people like to call a Binary Large OBject (also known as a BLOB in geek-speak.)
When you first open a workbook that has data in PowerPivot, you will briefly see Excel report that data connections have not been refreshed. This message appears while the PowerPivot add-in is re-connecting to the data. It will disappear in a few moments after opening the workbook.
This also means that your pivot table is no longer a pivot cache pivot table. It is now considered an OLAP pivot table. When you have data in Excel and create a pivot table, you are creating a normal pivot cache pivot table. When you connect Excel to an OLAP cube, you are creating a special type of pivot table called an OLAP pivot table.
There is a Microsoft Knowledge Base article that discusses all of the features in pivot tables that stop working when you have an OLAP pivot table. While none of those limitations ever applied to Excel data, they are mostly applying to PowerPivot data. The PowerPivot team worked to mitigate some of those issues, adding code to PowerPivot to allow you workaround some of the limitations. They tried to prevent you from ever seeing the bizarre OLAP PivotTable Field List, but it occasionally comes back.
I guess my point in all of this is not to blame PowerPivot for these features that aren’t working. These are features that don’t work in OLAP Pivot Tables. If anything, the PowerPivot team worked to narrow the list.
See the complete list of differences between OLAP pivot tables and pivot cache pivot tables at http://support.microsoft.com/kb/234700.
Here is a partial list. All of these are features that I wrote about in the pivot table topics in this book and won’t work in PowerPivot.
- ● You can’t use the Show Report Filter Pages command to replicate the pivot table for every customer.
- ● You can’t group items. This means you can’t roll daily dates up to months, quarters, and years. You can’t manage territories. You can’t create a better top 10 report.
- ● You can’t use Show Items With No Data to force months without any records to appear in the pivot table.
- ● You can’t double-click a pivot table cell to drill down and retrieve the detail.
- ● Custom names that you enter in the Active Field box are lost when you remove the field from the pivot table and then later add it back.
- ● You can’t create calculated fields or calculated items. However, the DAX Measures feature actually is a million times better than calculated fields, so this one is OK.
- ● You can’t use all eleven functions to summarize your data. As discussed in the next topic, PowerPivot gives you an obscure way to use five of the eleven calculations, but it is not where you would expect it to be.
- ● The GETPIVOTDATA formula is wacky, which makes it significantly harder to parameterize your GETPIVOTDATA formulas. Where a regular pivot table would generate an argument pair of ““, the OLAP version of GETPIVOTDATA uses “[Sales].[Customer]”,”[Sales].[Customer].&[Astonishing Shoe Inc.]”. This means that you have to concatenate “““[Sales].[Customer]””,””[Sales].[Customer].&[“ before the customer name and then “]” after the customer name.
There are some other features that don’t work. I never used any of these, so they are not that big of a deal to me:
- ● You can’t mark subtotal rows with an asterisk.
- ● You can’t enable background queries.
Back in 2010, I wrote the world’s first book on PowerPivot. Published by QUE, it is called PowerPivot for the Excel Data Analyst. There is a whole chapter in that book which discusses these limitations in detail. That chapter has the following type of discussion over and over: “When you create a regular pivot table, you can use this really cool feature to do X. Did you know that? I bet you never knew that because hardly any one ever knows about this feature. You would love and embrace that feature in a pivot table if you just knew about it before this moment. I think I am really cool because I am MrExcel and I use this feature all the time. Oh, by the way, let me dash your hopes because now PowerPivot won’t support that feature.”
Do you see the irony there? 90% of the world don’t use Excel. 90% of the people using Excel don’t use pivot tables. 90% of the people using pivot tables don’t use these super advanced pivot table features. So, the PowerPivot limitations won’t matter to most of the world.
Figure 968 It seems like a small group affected by OLAP limitations.
You have to zoom in to 400% to even see that wedge of the pie.
Figure 969 I knew that the 400% zoom would come in handy someday.
Even though most people won’t be affected, it is still an estimated 7 million people are going to run into an OLAP limitation when they try to use PowerPivot. You could sell out Yankee Stadium, Wembley, Soldier Field, the Los Angeles Coliseum, Rogers Centre, all other NFL stadiums, all MLB baseball stadiums, all NBA arenas and you would not have enough seating capacity for all of the people potentially affected.
For more resources for Microsoft Excel: