In excel, why can I create a relationship between Table 1 and Table 2, but I can't create one going in the opposite direction?

Question

I have two tables containing related data; both have a Participant_ID column. I've created a relationship between Table_1 and Table_2 with Table_2 as the lookup table. This is so that by setting up a Pivot Table in a certain way I can see where a value in Table_1's column is not in Table_2 (it will come up as "blank" in Pivot Table). It works fine. I also want to know the opposite, i.e. if there are values in Table_2 that aren't in Table_1. Thus, I've figured that I need to create the reciprocal relationship with Table_2 as my starting table and Table_1 as the lookup table. Whenever I try this, I get:

this relationship cannot be created because one or more columns contains duplicate values

This isn't true - neither of the columns contain duplicates, or any blanks that would be picked up as duplicates. I've checked a million times.

No matter what I do or change, I get this error. I've even tried this on completely new tables with only 1 row of data in each and thus no duplicate values, and once again, the first relationship can be created but when I try to create one in the opposite direction I get the same error message.

Yes, I know I can use other formulas to check if a value is in one table but not another, but I wanted to use a Pivot Table to also easily look at other similar fields between the two tables as well.

It's driving me absolutely crazy. In sum, why can you create a relationship between Table_1 and Table_2, but not between Table_2 and Table_1?

Image 1: The relationship in one direction can be created.

Image 2: The error message I get when I try to create relationship in opposite direction

Answer 1

You are trying to create a relationship loop, which is prohibited according to Microsoft's documentation:

Self-Joins and Loops

Self-joins are not permitted in a Data Model. A self-join is a recursive relationship between a table and itself. Self-joins are often used to define parent-child hierarchies. For example, you could join an Employees table to itself to produce a hierarchy that shows the management chain at a business.

Excel does not allow loops to be created among relationships in a workbook. In other words, the following set of relationships is prohibited.

Table 1, column a   to   Table 2, column f

Table 2, column f   to   Table 3, column n

Table 3, column n   to   Table 1, column a

If you try to create a relationship that would result in a loop being created, an error is generated.