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.

Solution

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