Creating a 2-way relationship between two tables in PowerPivot

[zacc206]

Hi Everyone,

I am having problems finding information around this (perhaps I am not searching with the right keywords), but it seems like it would be a common function of PowerPivot so I came here.

I'd like to create a relationship where I can find a value in table A only when it is not present in table B. Additionally, I'd like to find a value in table B only when it is not present in table A.

I've tried creating a 2-way relationship in PowerPivot but Excel won't let it happen.

I'd like to know:

• Can I create a 2-way relationship in PowerPivot?
  -or-
• Does PowerPivot have the ability to combine 2 existing tables into a virtual table where I could then select values from?

THis would be similar to what would happen in SQL using a JOIN, but I do not understand PowerPivot enough to be able to emulate advanced relationships like this.

Much of what I can find are posts about merging identical tables using PowerPivot. My data set has unique columns with one column acting as a primary key.

I am using Excel 2013 on Windows 8.

 

[Jacob Barnett]

PowerPivot only allows one relationship directly between two tables and at least one of those must have unique values in the column on which the relationship is based.

You don't give any details about the specifics of what you are trying to do but there are a few options:
- use a separate 'central' table of just the unique values connected to both of your current tables may do the job, you can then write measures that do the IF/THEN logic.
- you could combine the tables on import using 'UNION (ALL)', the way to get round possible duplication is to mark the row with the table it came from.
- you could combine the tables using Power Query.

Hope this helps.
Jacob

 

[zacc206]

Hi Jacob,

The data set I am working with has to do with names of employeesand a team each employee is associated with.

Table A - Contains"First Name" column and "Last Name" column. This is theactive employee roster.
Table B - Contains"Full Name" column and "Team Assignment" column. Thisis a list of everyone who's ever been assigned a team (active and non-active).

To create the primary key, I created a calculated column thatconcatenated "First Name" and "Last Name" in Table A (usingPowerPivot).

I am trying to determine:
- Which active employees in Table A do not have a teamassignment (from Table B)
- Which employees from Table B are active employees (from TableA)

The concept of a central table is what I was looking for.Attempting now to do this with Power Query, but I am running into issues.

I might be handling the primary key incorrectly (since I amcalculating it in PowerPivot). If Table A (active roster) is a dynamicallychanging data set (i.e. people drop and add on each refresh), how would I makea primary key? I could create an arbitrary employee ID (1,2,3,4,5,etc.) inTable A, but it would only be accurate until I repopulated the list.