Refresh imported data in Power Pivot

When you use the Power Pivot add-in to refresh previously imported data, you can see whether refresh succeeds, fails, or is cancelled.

In Power Pivot, click Home > Get External Data > Refresh or Refresh All to re-import the current table or all of the tables in the data model.

Status of the refresh is indicated for each connection used in the data model. There are three possible outcomes.

"Success" reports on the number of rows imported into each table.

"Error" can occur if the database is offline, you no longer have permissions, or a table or column is deleted or renamed in the source. Verify the database is available, perhaps by creating a new connection in a different workbook.

"Cancelled" means that Excel did not issue the refresh request, probably because refresh is disabled on the connection.

Tip:  Are you sharing this workbook with other people who might want to refresh data? Help your colleagues avoid data refresh errors by reminding them to request permissions on the data sources providing the data.

Table properties show queries used in data refresh

Data refresh is simply rerunning the same query that was used to get the data in the first place. You can view, and sometimes modify, the query by viewing table properties in the Power Pivot window.

1. To view the query used during data refresh, click Power Pivot > Manage to open the Power Pivot window.

2. Click Design > Table Properties.

3. Switch to Query Editor to view the underlying query.

Queries are not visible for every type of data source. For example, queries are not shown for data feed imports.

Connection properties that cancel data refresh

In Excel, you can set connection properties that determine the frequency of data refresh. If refresh is not allowed on a particular connection, you'll get a cancellation notice when you run Refresh All or attempt to refresh a specific table that uses the connection.

1. To view connection properties, in Excel, click Data > Connections to view a list of all connections used in the workbook.

2. Select a connection and click Properties.

3. In Usage, under Refresh control, if the checkbox is cleared for Refresh this connection on Refresh All, you will get a cancellation when you try Refresh All in the Power Pivot window.

Refresh data on Office 365

How you share your workbook will determine whether data refresh can occur. On Office 365, you cannot refresh data that comes from an external data source. Suppose you create a workbook in Excel, import data from a server on your network, create a PivotTable, and then upload the workbook to Office 365. On Office 365, you can work with the PivotTable, but you won't be able to use the Data >Refresh to update its data.

You'll get an External Data Refresh Failed error message, along with the name and location of the connection that failed to refresh.

Refresh data on SharePoint Server 2013

On SharePoint Server 2013, you can schedule unattended data refresh on the server, but doing so requires that Power Pivot for SharePoint 2013 is installed and configured in your SharePoint environment. Check with your SharePoint administrator to find out if scheduled data refresh is available.

Top of Page