Excel Power Pivot & Power Query For Dummies
Book image
Explore Book Buy On Amazon
If you need to edit or delete a relationship between two tables in your Power Pivot data model, you can do so by following these steps:
  1. Open the Power Pivot window, select the Design tab, and then select the Manage Relationships command.
  2. In the Manage Relationships dialog box, shown here, click the relationship you want to work with and click Edit or Delete.

    Use the Manage Relationships dialog box to edit or delete existing relationships.
  3. If you clicked Edit, the Edit Relationship dialog box appears, as shown. Use the drop-down and list box controls on this form to select the appropriate table and field names to redefine the relationship.
Use the Edit Relationship dialog box to adjust the tables and field names that define the selected relationship.

Here, you see a graphic of an arrow between the list boxes. The graphic has an asterisk next to the list box on the left, and a number 1 next to the list box on the right. The number 1 basically indicates that the model will use the table listed on the right as the source for a unique primary key.

Every relationship must have a field that you designate as the primary key. Primary key fields are necessary in the data model to prevent aggregation errors and duplications. In that light, the Excel data model must impose some strict rules around the primary key.

You cannot have any duplicates or null values in a field being used as the primary key. So the Customers table must have all unique values in the CustomerID field, with no blanks or null values. This is the only way that Excel can ensure data integrity when joining multiple tables.

At least one of your tables must contain a field that serves as a primary key — that is, a field that contains only unique values and no blanks.

About This Article

This article is from the book:

About the book author:

Michael Alexander is a senior consultant at Slalom Consulting with more than 15 years’ experience in data management and reporting. He is the author of more than a dozen books on business analysis using Microsoft Excel, and has been named Microsoft Excel MVP for his contributions to the Excel community.

This article can be found in the category: