Different Ways to Update Data in PowerPivot

SQL Server 2012

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.

When you import data from an external data source into your workbook, you can choose to keep the data as is, to capture a point in time for further analysis, or you can update the data from the source whenever you choose. The latter option is handy if you are getting data from relational databases that contain live sales information, or data feeds that are updated several times a day.

There are two kinds of data refresh:

Manual refresh. You can refresh the data in the workbook manually at any time. You can refresh all data, which is the default, or you can manually choose the tables and columns to refresh for individual data sources.

Automatic or scheduled refresh. If you have published your workbook to a PowerPivot Gallery or Sharepoint site that supports PowerPivot, you or the Sharepoint administrator can create a schedule for automatically updating the data in the workbook.

In addition to getting updated data from an existing source, you will need to refresh data in your workbook whenever you make changes to the schema of the source data. These changes can include adding columns or tables, or changing the rows that are imported.

This topic provides the following information:

  • How to perform manual data refresh in the workbook.

  • How to schedule automatic data refresh through SharePoint.

  • How to make changes to data sources, such as connection properties.

  • How to update the data in the workbook after source data has changed.

For additional troubleshooting tips and requirements, see Troubleshoot Data Refresh.

Note Note

Note that addition of data, changing data, or editing filters always triggers recalculation of any columns or formulas that depend on that data source. This may take some time. For more information, see Recalculate Formulas.

If you need to update column metadata or get recent data for designing new formulas, you can manually refresh your data at any time. The following topics provide instructions about how to manually refresh a data source and to edit its properties.

Manually Refresh Data in a PowerPivot Workbook

Edit the Properties of an Existing Data Source

You can schedule data refresh to occur at regular intervals when you save a PowerPivot workbook to a SharePoint site. Using connection information in the workbook or in the schedule, the server will connect to the data sources and retrieve updated data on your behalf.

For information about setting up a workbook for automatic refresh from SharePoint, see the following topics:

Save to SharePoint

Automatically Refresh PowerPivot Data in SharePoint

If you need to change the data associated with a workbook, you can use the tools in the PowerPivot add-in to edit the connection information or to update the definition of the tables and columns used in your PowerPivot data.

The changes that you can make to existing data sources include the following:




  • Edit the database name or server name

  • Change the name of the source text file, spreadsheet, or data feed

  • Change location of the data source

  • For relational data sources, change the default catalog or initial catalog

  • Change the authentication method or the credentials used to access the data

  • Edit advanced properties on the data source

  • Add or remove a filter on the data

  • Change the filter criteria

  • Add or remove tables

  • Change table names

  • Edit mappings between tables in the source and tables in the workbook

  • Select different columns from the source

  • Change column names

  • Add new columns

  • Delete columns from the workbook (does not affect data source)

Note Note

In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this topic. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.

To make changes, follow these steps in the PowerPivot window:

  • On the Design tab, in the Connections group, click the Existing Connections button, choose your connection, and use the editors that are provided. For more information, see Edit the Properties of an Existing Data Source.

  • Click the tab that contains the table that you want to modify, and then on the Design tab, in the Properties group, click the Table Properties button. For more information, see Edit the Properties of an Existing Data Source.

  • Select the column that you want to change by clicking on its header, and then in the Home tab, use the controls in the Formatting group to modify the column's properties. For more information, see Set the Data Type of a Column.

When you import a data source, you can define filters to restrict the total number of rows that are imported. This may be handy when you are beginning to develop a set of data for analysis and want to work with just a subset of the data for better performance. You might also want to create different workbooks for different regions: for example, you could use the same data source but filter on a particular region. For more information, see Change the Rows that Are Imported from a Data Source.

If you later decide to add more data back in you do not need to recreate the model with a different filter; instead, you can simply edit the filters that are applied to the data source to allow more data, or less.

Community Additions