The November Power Pivot for Excel 2013 update delivers a better experience when using Power Query with Power Pivot as well as support for Key Performance Indicators (KPI).

These fixes are delivered as part of following updates (there are 32- and 64-bit versions of each):

  • KB3039739—Update for Office 2013, which includes the first part of the Power Pivot and Power Query working better together fix.
  • KB3039800—Update for Office 2013, which includes the second part of the Power Pivot and Power Query working better together fix; the issue will not be resolved if you install just one of these patches.
  • KB3085502—MS15-099 security update for Excel 2013, which enables single sign-on (SSO) for ADAL on cloud domain-joined computers, respecting additional icon sets for a KPI in Power Pivot and several other fixes.

Using Power Query with Power Pivot

For data in Power Pivot that has been added through Power Query, the following operations can now only be done through Power Query, which resolves conflicts previously seen in Power Pivot.

  • Edit Table Properties
  • Column-level changes: Rename, Data type change, Delete
  • Table-level changes: Rename, Delete

A detailed KB article on the subject can be found here, and the scenario is outlined below:

  1. Using Power Query, import your data from a data source of your choice.

2.  Once your Power Query data manipulations are complete, select Close & Load To…

3.  When prompted, select Add this data to the Data Model and then click Load.

4.  Next, open the PowerPivot window, select a column on the table you just loaded using Power Query and rename that column. The same will apply to delete column, delete table and edit table as mentioned above.

5.  An attempt to rename a column in the model created with Power Query is blocked with a message that data originated in Power Query should be updated in Power Query; doing this prevents conflicts between the two tools.

KPI support

Power Pivot allows customers to define KPIs with various settings such as the target value, icon styles and KPI description.

The Excel 2013 update for KPIs includes:

  • Additional icon sets for a KPI status, including five levels of icon sets.
  • Alignment with the absolute KPI target value data type with that of the type of the measure. As a result, both Value and Goal measures now show the same data type on a PivotTable.

We hope these updates will increase your analysis productivity with Excel! If you have suggestions or feedback, visit Excel UserVoice.