Forum Discussion
Helper IIAutodetect Relationships
This feature doesn't seem to be working for me. Although, the column names are identical on other tables. I'm wondering if this is due to the error I get when I try to create the relationships manually
"You can't create a relationship between these two columns because one of the columns must have unique values."
I can right click one of the column and remove duplicates but then I'll potentially lose needed data, right?
Thanks.
Hi brs09j,
Basically what the error message is complaining about is that relationship you're trying to create between two tables has an issue. One of that common column must have unique numbers. Only One to Many or One to One relationship are supported. In your scenario, it looks like a Many to Many relationship that might lead to such an error.
To work around the Many to Many problem, please refer to this blog which has provided two workarounds:
- Edit one the query and make the column a distinct list of the key values by using “Remove Duplicate Rows” on it.
- Add an intermediary table made of the list of distinct key values to the model, which will then be linked to both original columns
Best regards,
Yuliana Gu
5 Replies
- MattAllington
Community Champion
Yes, this would be the cause. Have a read of my article here to help you understand what you need. https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/
v-yulgu-msftMicrosoft Employee
Hi brs09j,
Basically what the error message is complaining about is that relationship you're trying to create between two tables has an issue. One of that common column must have unique numbers. Only One to Many or One to One relationship are supported. In your scenario, it looks like a Many to Many relationship that might lead to such an error.
To work around the Many to Many problem, please refer to this blog which has provided two workarounds:
- Edit one the query and make the column a distinct list of the key values by using “Remove Duplicate Rows” on it.
- Add an intermediary table made of the list of distinct key values to the model, which will then be linked to both original columns
Best regards,
Yuliana Gu
