Lookup Tables in PowerPivot


You can create lookup tables without creating absolute links in your PowerPivot model. Models that contain absolute file paths to text or Excel documents will fail to refresh. An example of an absolute file path might look like:

  • C:\Reference\Documents\myfile.xlsx
  • \\MyFileServer\Documents\myfile.xlsx

Our servers do not have access to your local resources, including any local or network drives. 

To create a lookup table in your PowerPivot model that is based on an Excel or text file, follow the directions below. 


  1. Open the file for which you want to add a reference and create a new sheet.
  2. Go to the Data tab in the Ribbon and click the Existing Connections button.

  3. Click the Browse for More button.

  4. Navigate to your file, select it, then click the Open button.
  5. Choose the table you want and click OK.

  6. In the Import Data window, select Table and then click OK. (Do not add the data to the Data Model.)

  7. Select the contents of the table you created and copy.

  8. Go to the PowerPivot tab in the Ribbon and click the Manage button.

  9. In PowerPivot, go to the Home tab, click the Paste button.

  10. Name the table and click OK.

  11. Close the PowerPivot window.
  12. In Excel, go to the Data tab in the Ribbon and click Connections button.

  13. Select the connection you just created and click the Remove button. (The connection name will be the same as the file name.)

  14. Optionally, you can delete the newly added sheet from the workbook.
Powered by Zendesk