Edit the Properties of an Existing Data Source
Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.
After you have created a connection to an external data source, you can later modify that connection in these ways:
You can change the connection information, including the file, feed, or database used as a source, its properties, or other provider-specific connection options.
You can change table and column mappings, and remove references to columns that are no longer used.
You can change the tables, views, or columns that you get from the external data source. For more information, see Change the Rows that Are Imported from a Data Source.
Modify a Connection
The first procedure illustrates how to modify a data connection. Because the options for working with data sources differ depending on the data source type, this procedure uses a simple Access database.
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 change the external data source used by a current connection
In the PowerPivot window, click the Home tab, and in the Connections group, click Existing Connections.
Select the current database connection and click Edit.
For this example, the Table Import Wizard opens to the page that configures an Access database. However, depending on the type of data source you are changing, the provider might be different, and also the properties that are available.
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 in order to see the new data.
Click Save, and then click Close.
On the Home tab, in the Get External Data group, click Refresh, and then click Refresh All.
The tables are refreshed using the new data source, but with the original data selections.
Note
If the new data source contains any additional tables that were not present in the original data source, you must re-open the changed connection and add the tables, as described in Change the Rows that Are Imported from a Data Source.
Edit Table and Column Mappings (Bindings)
This procedure describes how to edit the mappings after you change a data source.
To edit column mappings when a data source changes
In the PowerPivot window, click the Design tab, and in the Properties group, click Table Properties.
The name of the current workbook 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 workbook, you can toggle between the two sets of column names by selecting the options Source or PowerPivot data (workbook).
To change the table that is used as a data source, for Source Name, select a different table than the current one.
Change column mappings if needed:
To add columns that are present in the source but not in the workbook, select the checkbox beside the column name.
The actual data will be loaded into the workbook the next time you refresh.
If some columns in the workbook are no longer available in the current data source, a message appears in the notification area that lists the invalid columns. You do not need to do anything else.
Click Save to apply the changes to your workbook.
When you save the current set of table properties, any invalid columns are automatically removed and new columns are added. A message appears indicating that you need to refresh the tables. Click Refresh to load updated data into your workbook.