Search code examples
exceldata-modelingpowerpivot

excel powerpivot data modeling with 2 non-unique key relation

So I have two tables, Products and Orders.

The Products table contains the following columns: ID, Name, Type.

The Orders table contains the following columns: ID, Name, Quantity.

In both tables, neither ID or Name is unique, but together they are. Is it possible for Excel PowerPivot to make a relationship between the two tables using these two columns?

Solution

  • You cannot create relationship using multiple columns in Powerpivot. You have to create a single column using multiple columns, which make up the key.

    Reference

    When you have multiple columns that make a key you should create a new column, use CONCATENATE or & to create a composite column, and that will serve as your key.