PowerPivot Annoyances

Sometimes they are referred to as features, sometimes “bugs,” and other times by the harsher term “defect.” By whatever name, PowerPivot for Excel contains the sometimes curious sort behaviors common to any complex software product in the initial release.

Disabled PowerPivot Add-In

Upon starting Microsoft Excel, you may find the PowerPivot ribbon item is missing, as illustrated in Figure 11-1. It is, of course, exceedingly difficult to do work in PowerPivot for Excel without the PowerPivot ribbon menu. The missing PowerPivot ribbon menu is the principal symptom of a disabled PowerPivot add-in.

Figure 11-1. Missing PowerPivot add-in

In order to get your PowerPivot for Excel environment back up and running, perform the following steps. First, select the File ribbon item and then the Options sub-menu, as circled in Figure 11-2.

Figure 11-2. File Options sub-menu

Selecting the Options sub-menu will display the Excel Options panel, illustrated in Figure 11-3. Select the Add-Ins menu item, circled in Figure 11-3.

Figure 11-3. Excel Options panel

The Add-Ins panel, illustrated in Figure 11-4, will display all of the currently installed Excel extensions. Note the PowerPivot for Excel add-in is a COM-based (Component Object Model) add-in.

Figure 11-4. Microsoft Office add-ins

Also note the PowerPivot for Excel add-in is in the group of inactivated items. Following the next set of steps will re-enable the add-in, restoring the PowerPivot for Excel features. Using the Manage pulldown illustrated in Figure 11-5, select COM Add-Ins (recall PowerPivot for Excel is COM-based) and click the Go button.

Figure 11-5. Managing COM add-ins

The dialog for managing available COM add-ins, illustrated in Figure 11-6, will be displayed. A disabled PowerPivot for Excel add-in will have an empty check box in the list of available add-ins. Click the check box corresponding to PowerPivot for Excel, ensuring it is in a “checked” state, and click the OK button. The PowerPivot for Excel add-in will be activated, and the PowerPivot menu will reappear on the ribbon.

Figure 11-6. Activating the PowerPivot for Excel add-in

Calculated Column Missing

As you utilize calculated columns in your PowerPivot data, it is important to remember the PowerPivot Field List is not immediately synchronized with the PowerPivot data metadata. For example, after adding a calculated column to a PowerPivot table, the PowerPivot Field List in Excel will display the “PowerPivot data was modified” warning, as circled in Figure 11-7.

Figure 11-7. “PowerPivot data was modified” warning

However, it is important to remember that pressing the Refresh button does not refresh the PowerPivot Field List. In order for the new calculated column to appear in the field list, you must send a query to the underlying SSAS database. Simply interact with any of the slicer selections, even changing the selection illustrated in Figure 11-7 from Boise to any other value and back. Changing the selection will result in the new calculation appearing in the PowerPivot Field List.