Feature X Won’t Work in Power Pivot
March 23, 2023 - by Bill Jelen
This is not a regular pivot cache pivot table that you’ve been using for the last 10 years. That data in your Excel worksheets is now in the Data Model. Even though the Data Model is stored inside of your Excel file, it gets treated like external data.
A lot of features that you might love in regular pivot table are not available when the data is stored externally. This was particularly bad in version 1 of Power Pivot. The later versions of Power Pivot now in Excel have mitigated some of the problems. But, you will still run into problems:
- You can not group data. Excel 2016 adds the ability to group Dates. But if you want to create a stratification of invoice amounts, you can not group like a regular pivot table.
- Double-clicking to drill-down will only return 1000 rows
- Pivot tables and Slicers are not automatically sorted by custom list. If you have access to the Power Pivot tab in the ribbon, you can use the Manage icon, then Design, and specify that a column such as Month Name should be sorted by another column such as Month Number.
- You can’t use the Show Report Filter Pages command to replicate the pivot table for every customer.
- The
GETPIVOTDATAformula is wacky, which makes it significantly harder to parameterize yourGETPIVOTDATAformulas. Where a regular pivot table would generate an argument pair of ““, the OLAP version ofGETPIVOTDATAuses “[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.
This article is an excerpt from Power Excel With MrExcel
Title photo by Håkon Grimstad on Unsplash
