Power Pivot Relationships between 2 tables not working

[kjnjanx]

I have two tables that I'd like to connect into 1 pivot table using Power Pivot. However, when I attempt to do so - it gives me an error that the relationship can't be created due to duplicate values. How do I correct that so I can overlay the data together? Thanks!!

[CK76]

At least one of the related columns must have only unique values. Otherwise, it will cause ambiguity in data relationship.

One way to workaround this issue, is to use PowerQuery/Get & Transform to create dimension table to tie the two tables together (using List.Distinct() and List.Combine()).
However, this requires Excel 2013 or later. As Excel 2010 does not support loading data model from PowerQuery.

Alternate solution is to create dimension table in Excel worksheet, then load to data model.

Do note that attribute (Row/Column label) must be from Dimension table. Otherwise, you'll end with ambiguity in data model and will cause Pivot Table to evaluate using Cartesian Join.

If you need further help, I'd recommend uploading sample workbook. To upload, use "Go Advanced" button and follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.