Make changes to an existing data source in Power Pivot

After you have created a connection to an external data source in a Data Model, you can use the Power Pivot add-in to change:

  • The connection information, including the file, feed, or database used as a source, its properties, or other provider-specific connection options.
  • Table and column mappings, and remove references to columns that are no longer used.
  • The tables, views, or columns you get from the external data source. See Filter the data you import into Power Pivot.
In this article


Change the external data source for a current connection

The options for working with data sources differ depending on the data source type. This procedure uses a simple Access database.

  1. In Excel, Power Pivot tab > Manage Data Model to open the Power Pivot window.
  2. In the Power Pivot window, click Home > Connections > Existing Connections.
  3. Select the current database connection and click Edit.

For this example, the Table Import Wizard opens to the page that configures an Access database. The provider and properties are different for different types of data sources.

  1. In the Edit Connection dialog box, click Browse to locate another database of the same type but with a different name or location.

As soon as you change the database file, a message appears indicating that you need to save and refresh the tables to see the new data.

  1. Click Save > Close.
  2. Click Home > Get External Data > Refresh > Refresh All.

The tables are refreshed using the new data source, but with the original data selections.

 Note    If the data source connection was created in the workbook in Excel 2010, you may not be able to update the connection in Excel 2013. For more information, see the section "Can't update the data source connection" in Upgrade Power Pivot Data Models to Excel 2013.

Top of Page Top of Page

Edit table and column mappings (bindings)

When you change a data source, the columns in the tables in your model and those in the source may no longer have the same names, though they contain similar data. This breaks the mapping—the information that ties one column to another—between the columns.

  1. In Excel, Power Pivot tab > Manage Data Model to open the Power Pivot window.
  2. In the Power Pivot window, click Design > Properties > Table Properties.
  3. The name of the current table is displayed in the Table Name box. The Source Name box contains the name of the table in the external data source. If columns are named differently in the source and in the model, you can go back and forth between the two sets of column names by selecting Source or Model.
  4. To change the table that is used as a data source, for Source Name, select a different table than the current one.
  5. Change column mappings if needed:
  1. To add columns that are present in the source but not in the model, select the box beside the column name. The actual data will be loaded into the model the next time you refresh.
  2. If some columns in the model are no longer available in the current data source, a message appears in the notification area that lists the invalid columns. You don’t need to do anything else.
  1. Click Save to apply the changes to your workbook.

When you save the current set of table properties, any missing columns are automatically removed and new columns are added. A message appears indicating that you need to refresh the tables.

  1. Click Refresh to load updated data into your model.

Top of Page Top of Page

Changes you can make to an existing data source

To change the data associated with a workbook, use the tools in Power Pivot to edit connection information or update the definition of the tables and columns used in your Power Pivot data.

Here are changes that you can make to existing data sources:

Connections Tables Columns

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 columns

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

Top of Page Top of Page

 
 
Applies to:
Excel 2013, Power Pivot in Excel 2013