Make changes to an existing data source in Power Pivot

After you create a connection to an external data source in the Excel Data Model using Power Pivot, 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.
• 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.

Change the external data source for an existing connection

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

Follow these steps:

1. In the Power Pivot window, click Home > Connections > Existing Connections.
2. 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.
3. 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.
4. Click Save > Close.
5. Click Home > Get External Data > Refresh > Refresh All.
   The tables are refreshed using the new data source, but with the original data selections.

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 might not have the same names, even if they contain similar data. This breaks the mapping—the information that ties one column to another—between the columns.

Follow these steps:

1. In the Power Pivot window, select Design > Properties > Table Properties.

2. The name of the current table appears in the Table Name box. The Source Name box contains the name of the table in the external data source. If columns have different names in the source and in the model, you can switch between the two sets of column names by selecting Source or Model.

3. To change the table used as the data source, select a different table for Source Name.

4. Change column mappings if needed:

   • To add columns that are present in the source but not in the model, select the box beside the column name. The data is loaded into the model the next time you refresh.
   • 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.

5. Select Save to apply the changes to your workbook.
   When you save the current set of table properties, Power Pivot automatically removes any missing columns and adds new columns. A message appears indicating that you need to refresh the tables.

6. Select Refresh to load updated data into your model.

Changes you can make to an existing data source

To change source 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 (doesn't affect data source)

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.