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.