PowerPivot and Linked Table Names


When PowerPivot creates a linked table, it appears to assign an arbitrary name to the import. The table name in powerpivot (Table1, Table2 etc) is then usually renamed as part of the design process. However, this practice is sloppy because the table name in excel is different to that of powerpivot and the name in excel is poorly defined (which may confuse anyone updating data at a later stage). This post looks at methods of managing linked tables in powerpivot and excel in order to provide more robust models.

Import (without name)

The standard way of creating a linked table in powerpivot is simply to select the data range, click on the powerpivot ribbon and select ‘Create Linked Table’. Once this is done the ‘Create Table’ dialog displays (usually the user indicates the table has headers) and the table is imported into powerpivot.

The imported table is then renamed in powerpivot with a double click (or right click à rename) so that table name has meaning in the pivot table.

In excel, we can see that this operation has also created an excel table by selecting the ‘Name Manager’ button from the Formulas ribbon. A dialog showing all tables and ranges in the workbook is displayed. Note that Table1 has been created.

The creation of the linked table in powerpivot has defined a table in excel and then created a connection for that table in powerpivot. We can see this in powerpivot by the definition of the linked table.

While this may achieve the outcome of creating a usable powerpivot model, it can become frustrating for the user (or anyone updating excel data) because the definitions in excel (ie Table1) are not the same as the table names in powerpivot (eg Dates).

Importing a Named Table

One way around this is to define the excel table and its name before the excel data is imported into powerpivot. This is easily achieved by selecting a formatting style from the ‘Format as Table’ button in the Home ribbon. The name of the table can be edited using the name manager (Formulas à Name Manager à Edit (after the appropriate table has been selected)).

Now, when the powerpivot linked table is created, it is automatically imported with the same name as the excel table.

Rename the Table and Manage the Import

Where the powerpivot table is created first and then the name of the table is changed in excel, the definition of the table in powerpivot will be in error. This does not present as a problem until the data in powerpivot is refreshed. When the data is refreshed, an ‘Errors in Linked Tables’ dialog is shown (as below) so that the connection can be managed.

The options for management (as below) are rather self-explanatory and allow for;

  • The selection of another excel table. Note that this is any excel table (defined table) that is not linked to a power pivot table.
  • Materialising the table (that is, removing the link to excel)
  • Deleting the powerpivot table from the model

The selection of an existing excel table completely redefines the import so that the refresh will alter the columns and data in the table so that the powerpivot table will be exactly the same as the excel table. While this may seem intuitive, the implication for the model is enormous because the linked table in powerpivot can change structure (adding columns etc).

Other Linked Table Management

Alternatively, a linked table can be managed by the ‘table definition’ button in the ‘Linked Table’ ribbon in powerpivot (as highlighted below). When selected, the dropdown shows all tables that have been defined in the excel workbook (regardless of their usage as linked tables), and, upon table change, imports the new definition and data.

If the excel table is already used as a linked table in the powerpivot model, the user is warned that the table is already used in a link and continuing will break the link (as below). If the user continues, the ‘old linked table’ is materialised and cannot be updated.

Conclusion

The use of linked tables in powerpivot provides a flexible way of importing, managing and using data in the powerpivot model. However, the automatic creation of linked tables without good naming conventions between powerpivot and excel may be confusing as the model is further developed, changed and updated. In-fact, every excel workbook model regardless of the use of powerpivot should implement good naming conventions. However, this can be managed with excel and powerpivot in various ways at various stages of model development.

Advertisements

2 thoughts on “PowerPivot and Linked Table Names

  1. I’m rather frustrated by the requirement of using Excel Table names in the model – mainly due to the fact that Excel Table names cannot have spaces in them. This means that if you want to have consistent dimension and fact table names, you cannot use spaces in Power Pivot either.

  2. In Excel 2013 we can change a table name via Formulas > Name Manager > Edit. This will be automatically carried forward to PowerPivot > Linked Table > Excel Table name

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s