After you create 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.
-
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.
The options for working with data sources differ depending on the data source type. This procedure uses a simple Access database.
Follow these steps:
-
In the Power Pivot window, click Home > Connections > 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. The provider and properties are different for different types of data sources.
-
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.
-
Click Save > Close.
-
Click Home > Get External Data > Refresh > Refresh All.
The tables are refreshed using the new data source, but with the original data selections.
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.
Follow these steps:
-
In the Power Pivot window, click Design > Properties > Table Properties.
-
The name of the current table is shown 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.
-
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 model, select the box beside the column name. The actual data will be 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.
-
-
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.
-
Click Refresh to load updated data into your model.
To change any source data that you associate 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) |
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
