Share via

  • Article

Create Relationship Dialog Box

The Create Relationship dialog box enables you to create relationships between tables in your PowerPivot workbook or to edit existing relationships. To access this dialog box, on the Design tab, in the Relationships group, click Create Relationship.

For more information about what a relationship is and how creating relationships can help you create more useful data models, see Relationships Overview.

  • Table
    Select the first table in the relationship. This should be the many side of a many-to-one relationship. For example, if the relationship is between a Customers table and an Orders table, the Orders table should be specified for Table, and the Customers table should be specified for Related Lookup Table. A customer can have many orders, but each order is for a single customer.

    Note

    Each table can have only one relationship to another table. Therefore, if you have already used a table in a relationship with the currently selected table, the other table will not be available in the dropdown list for Related Lookup Table.

  • Column
    Select the source column that will be used in the relationship.

  • Related Lookup Table
    Select the second table that will be in the relationship. This table should be on the one side of a many-to-one relationship.

  • Related Lookup Column
    Select the column in the second table that is related to the first column. For every row in the Related Lookup Table, this column must have a unique value.

Duplicate Values and Other Errors

If you choose a column that cannot be used in the relationship, a red X appears next to the column. You can pause the cursor over the error icon to view a message that provides more information about the problem. Problems that can make it impossible to create a relationship between the selected columns include the following:

Problem or message

Resolution

The relationship cannot be created because both columns selected contain duplicate values.

To create a valid relationship, at least one column of the pair that you select must contain only unique values.

You can either edit the columns to remove duplicates, or you can reverse the order of the columns so that the column that contains the unique values is used as the Related Lookup Column.

The column contains a null or empty value.

Data columns cannot be joined to each other on a null value. For every row, there must be a value in both of the columns that are used in a relationship.