How can I create a relationship in excel for multiple columns?

Question

I'm trying to create a relationship between two tables in powerpivot. However, my tables don't have any keys. What I would like to do is create a SQL-Unique-Constraint-like relationship, which is based upon multiple values combined, being the key.

For example:

Table1 columns are First, Last, Address, Phone

Table2 columns are the same.

I want to create a relationship in excel that is the equivalent of

select * from Table1 full join Table2 on 1.Fist=2.First and 1.Last=2.Last and 1.Address=2.Address

However, the create relationship dialogue doesn't allow multiple columns to selected. I tried going the route of just creating multiple 1-column relationships. However, relationships also cannot include columns were there are duplicate values in the column.

I have a feeling I may just be approaching accomplishing this from the wrong direction. Any help is appreciated! Thank you.

Answer 1

Zee,

You are right that PowerPivot does not natively support multi-column relationships. There are however 2 work arounds:

• Add a key to each table of the respective columns concatenated together and providing this is unique in at least one the relationship can be created. If you have a situation where neither table has unique keys then an intermediate table of unique keys could be created using SQL.

• Technically multiple relationships can be created between tables but only one can be active. There is a DAX function called USERELATIONSHIP() which can use inactive relationships. This is an advanced technique.

Your solution may well be to combine the two tables in your source SQL query. Jacob

Answer 2

If all you want to do is inner join using 2 or more columns, please consider creating a calculated column that concatenates the 2 or 3 columns in each of the 2 tables and then create a relationship between them.

I have had similar cases and used this technique.