“This workbook has a Power Pivot data model created using a previous version of the Power Pivot add-in. You’ll need to upgrade this data model with Power Pivot in Microsoft Excel 2013”.
Look familiar? It means you opened up an Excel 2010 workbook in Excel 2013 or later, and that workbook includes an embedded Power Pivot data model built using a previous version of the Power Pivot add-in. You might see this message when you try to insert a Power View sheet in an Excel 2010 workbook.
In Excel 2013 or Excel 2016, the data model is an integral part of the workbook. This message lets you know that the embedded Power Pivot data model needs upgrading before you can slice, drill, and filter the data in Excel 2013 or Excel 2016.
-
Before upgrading, check for issues known to cause problems during upgrade.
-
Save a copy of the file now in case you need the previous version later. After upgrading, the workbook will only work in Excel 2013 or later. More about: what’s different in an upgraded model.
-
Click Power Pivot > Manage to begin the upgrade.
-
Acknowledge the upgrade notification.
The message is “This workbook has a Power Pivot data model created using a previous version of the Power Pivot add-in. You’ll need to upgrade this data model with Power Pivot in Microsoft Excel 2013.”
Click OK to close the message.
-
Upgrade the data model. After upgrade, you can no longer work with the workbook in the previous version of Power Pivot.
The message starts with “The workbook contains a Data Model that was created with a previous version of Power Pivot.” It ends with “Do you want to upgrade the workbook?”
Click OK to upgrade the workbook.
-
Save and reload the workbook. This step locks in the changes that were made during upgrade. Once you save the workbook, you cannot roll back. Reloading should only take a few seconds, depending on the size and location of the workbook.
The message starts with “The workbook upgrade has finished successfully but Excel is still in compatibility mode.”
Click Yes to reload the workbook.
Upgrade is now finished. If you got other messages during upgrade, upgrade might have failed, or met with partial success. In some cases, you’ll need to make manual changes to the workbook or data model to fully upgrade to Excel 2013 or Excel 2016. Read the following sections in this article to learn more.
In this article
Known issues
Remove or rebuild linked tables that are no longer associated with a source table in Excel
Outdated and orphaned tables are not upgraded with the rest of the model, resulting in data deletion or unusable data. You can avoid this problem by ensuring existing linked tables are associated with an existing source table in Excel.
-
In Power Pivot for Excel 2010, click a linked table in the Power Pivot window.
-
Click Linked Table on the ribbon.
-
Verify that Excel Table points to an existing table. If the table was renamed in Excel, click the down arrow in Excel Table to choose the renamed table.
-
Click Go to Excel Table to verify the linked table resolves to a source data table in Excel.
-
If Excel Table is empty, do one of the following:
-
Delete the linked table in the model if it’s no longer used.
-
Unless the following issue also applies, you are now ready to upgrade the workbook.
Remove query drillthrough tables
Another upgrade issue applies only to Power Pivot data models based on Analysis Services cubes that support drillthrough actions. If a workbook contains drillthrough query tables, upgrade will appear to succeed at first, but then returns an upgrade error each time you click on a field in a PivotTable.
-
In Power Pivot for Excel 2010, locate any drillthrough query tables in the workbook.
A drillthrough query table is created in a workbook when you right-click a measure and select Show Details. The resulting table appears as a separate sheet in the workbook. The first line starts with “Data returned for …”
-
Delete each sheet that contains drillthrough query tables. You can recreate them after upgrading.
-
Save the workbook.
-
Upgrade the workbook.
-
Recreate the drillthrough query tables if you need them. Right-click a calculated field in a PivotTable, and click Shows Details. A drillthrough query table will be created in a separate worksheet.
Remove password protection
Password protected workbooks that also contain a Data Model will fail upgrade with this error:
“Unable to upgrade the Data Model in this workbook.”
To work around this problem, remove the password prior to upgrading.
-
Open the workbook in Excel 2013 or Excel 2016.
-
Click File > Info > Protect Workbook >Encrypt with Password.
-
Clear the asterisks and then click OK.
-
Save the workbook.
-
Click Power Pivot > Manage to restart the upgrade.
-
Add password protection back to the workbook.
Remove restricted access
Workbook Data Models that have restrictions on print, edit, or copy will fail upgrade with this error:
“Unable to upgrade the Data Model in this workbook.”
To work around this problem, remove the restrictions prior to upgrading.
-
Open the workbook in Excel 2013 or Excel 2016.
-
Click File > Info > Protect Workbook >Restrict Access.
-
Check Unrestricted Access to remove the restrictions.
-
Save the workbook in Excel.
-
Click Power Pivot > Manage to restart the upgrade.
-
Add access restrictions back to the workbook.
Specific columns in a model are mentioned in upgrade messages
When errors occur during upgrade, and the error message indicates which fields are causing the problem, follow these steps:
-
In Excel 2013 or Excel 2016, go to the PivotTable or report that contains the fields that upgrade is having trouble with.
-
Remove the fields from just the PivotTable or report. The Field List will continue to list the fields; they just won’t appear in the report.
-
Save and close the workbook.
-
Reopen the workbook.
-
Click Power Pivot > Manage to restart the upgrade.
-
Assuming upgrade was successful, add the fields back to the PivotTable or report.
Can’t update a data source connection in an upgraded workbook
You created a workbook in Excel 2010 with a connection to an external data source. You open the workbook in Excel 2013 or Excel 2016. When you try to change a PivotTable in the workbook, you see a message that you must update the PivotTable. You try to update it, but you see this message:
"Excel was unable to get necessary information about this cube. The cube might have been reorganized or changed on the server. Contact the OLAP cube administrator and, if necessary, set up a new data source to connect to the cube."
When you try editing the existing connection, you can see the name of the data model, but you can’t modify the data source.
Problem
The connection to the data source is no longer valid and you need to update it, but you can’t update it in Excel 2013 or Excel 2016.
Solution
-
Open the workbook in Excel 2010 with the Power Pivot add-in.
-
On the Power Pivot tab > Manage Data Model to open the Power Pivot window.
-
In the Power Pivot window, click Home > Connections > Existing Connections.
-
Select the current database connection and click Edit.
-
In the Edit Connection dialog box, click Browse to locate another database of the same type but with a different name or location.
-
Save and close the workbook.
-
Open the workbook in Excel 2013 or Excel 2016.
Finish a partial upgrade
In most cases, a workbook upgrade should go smoothly but sometimes further changes are required to complete the job. In this section, you’ll learn how to respond to specific errors indicating a partial or incomplete upgrade. The workbook is upgraded but missing certain functionality.
KPI images are missing
If KPI images (colored or graph icons) are missing, remove the KPI from the Values area of the Field List, and then add it back again.
DAX calculation upgrade error: We couldn’t upgrade this workbook properly…
Some DAX calculations require manual changes before the workbook is operational. If DAX expressions or queries are incompatible with Excel 2013, the workbook is only partially upgraded. You’ll see this message:
“We couldn’t upgrade this workbook properly. Saving could result in some of your Excel data features not working. For more details, go here: http://go.microsoft.com/fwlink/?LinkID=259419
Here is the error we got: Cannot query internal supporting structure for column ‘<table>[<column name>]’ because they depend on a column, relationship, or measure that is not processed. Please refresh or recalculate the model.”
If you get this error (and you may get it several times), be sure to note which tables and columns are indicated so that you can go directly to those tables. You will need to edit each DAX expression:
Note: If you’re asked to allow Power Pivot to save, close and reopen the workbook, click Yes.
-
In the Power Pivot window, open the table mentioned in the message.
-
Click on the column that has an error icon and #ERROR values. You should see a DAX expression in the formula bar.
-
Look for the following known DAX compatibility issues:
Nulls indicated as double quotes with no values (“”) are no longer allowed. Replace the double-quotes with a zero.
LOOKUPVALUE, CONTAINS, or SWITCH attempted to compare incompatible value types. You might need to specify a [Type] in the argument, or ensure both value types are Numeric, String, or Boolean. If you need to convert one of the values, you can use the VALUE or FORMAT function.
More errors might continue to appear in Excel while you are fixing DAX expressions. If you cannot edit a formula, switch back to Excel to see if an error is blocking further action. Click OK to close the error, return to Power Pivot to continue your work.
After you’ve fixed the DAX calculations and error messages no longer appear, consider the workbook as fully upgraded.
PivotTable functions that reference “Power Pivot Data” are no longer valid
Excel cube functions that specify a connection to Power Pivot model from the previous release must be manually updated to the new model name, “ThisWorkbookDataModel”.
|
Previous version |
Excel 2013 |
|
=CUBEVALUE("Power Pivot Data",[Measures].[TotalSales]) |
=CUBEVALUE("ThisWorkbookDataModel",[Measures].[TotalSales]) |
-
In Excel, on each worksheet, click Formulas > Show Formulas to show the calculations used in each sheet.
-
For Excel cube functions that specify a connection, look for “Power Pivot Data”.
-
Replace “Power Pivot Data” with “ThisWorkbookDataModel”.
-
Save the workbook.
Data Deletion: when upgrade deletes data in your model
“Upgrade has been unable to upgrade the following linked tables <tablename>. These tables have been removed from the model.”
In special cases, data is deleted from a model during upgrade. Data deletion occurs when you have an orphaned linked table in your model that cannot be traced to an associated table in the same workbook, either because the source table was deleted or renamed.
Upgrade succeeds, but the linked table is gone
A linked table is table that you create in a worksheet and then add to a data model so that you can relate it to other tables and add DAX calculations. Upgrade will delete a linked table if the source table no longer exists in the workbook.
Here is the scenario: You created or imported a table in Excel, added it as a linked table in Power Pivot where it became part of the data model, and then deleted the original table upon which it was based. The model retains the linked table even though the table in Excel is gone.
Later, when you try to upgrade the workbook to Excel 2013, the following error occurs.
“Upgrade has been unable to upgrade the following linked tables <tablename>. These tables have been removed from the model.”
If possible, cancel upgrade so that you can modify the model or create a backup in case you want to refer to the table later:
-
Click OK to close the message “Upgrade has been unable to upgrade the following linked tables”.
-
When asked whether to save, close, and reopen the workbook, click No.
-
Close the file in Excel 2013 without saving it.
-
Open the workbook in Excel 2010 and correct the problems with linked tables:
-
Linked table is no longer needed? Delete it from the model or restart upgrade in Excel 2013 and let upgrade remove the table for you.
-
Excel table was renamed but the linked table was not? Update the connection information in Power Pivot, as described in step 1 at the top of this page.
-
Linked table needs to stay because it’s used in calculations or in a PivotTable, but the original Excel table is gone? Recreate the source table in Excel and update the link:
-
Copy the rows from the linked table in the Power Pivot window.
-
Paste the rows into a worksheet in Excel.
-
Format the rows as a table.
-
Name the table.
-
Go back to the Power Pivot window.
-
Click Linked Table > Excel Table and then select the table you just created.
-
Save the file.
-
-
-
After making corrections, open the file in Excel 2013.
-
Start upgrade again by clicking Power Pivot > Manage.
If you already saved the workbook, you cannot roll it back a previous version. You will need to recreate the linked table from scratch. More about: Add worksheet data to a Data Model using a linked table.
What’s different in an upgraded model
In most cases, an upgraded workbook is identical to its predecessor. However, there a few changes in the newer version worth mentioning.
Slicers and column descriptions no longer appear the Field List
In previous versions, a PivotTable Field List displayed column or field descriptions as Tooltips. In Excel 2013 or later, the Field List does not support Tooltips. Any column descriptions that exist in the model will be ignored in data visualizations in Excel.
Slicers are supported, but you now create them in a different way. The Field List does not provide options for creating a slicer.
Upgraded workbooks no longer work in a previous version of Excel and Power Pivot
Before Excel 2013, there were two previous versions of the Power Pivot add-in. Both versions work exclusively with Excel 2010. These versions are:
-
SQL Server 2008 R2 Power Pivot for Excel
-
SQL Server 2012 Power Pivot for Excel 2010
After you’ve upgraded a data model to Excel 2013 or Excel 2016, you can open the workbook, but not interact with or edit, the model-based Pivot reports in Excel 2010. When opening an Excel 2013 or Excel 2016 workbook data model in Excel 2010, you will get the following error if you click a slicer, change a filter, or drag a new field onto the PivotTable:
“Initialization of the data source failed”.
This error occurs because data interactions, such as clicking a slicer, generate a query that is sent to the data model (the model is the data source of the PivotTable). Because the data model is no longer valid in Excel 2010, you will get a data source initialization error instead of data interaction.
Suppose you now try to open the data model in a Power Pivot window. The model will not open, and the following error occurs:
“This workbook contains an Excel data model that is created in a newer version of Excel. You can open this workbook in an older version of Excel, but you will be unable to load or work with Power Pivot when it coexists with a Excel data model.”
There are no workarounds; upgrade is forward-only. Before upgrading, check with co-workers to ensure that you are all upgrading to Excel 2013 at the same time. Additionally, you might also want to temporarily keep separate versions of your workbooks if your organization plans to use Excel 2010 and Excel 2013 or Excel 2016 side by side.
More about: Power Pivot version compatibility
