Autodetect Relationships

[brs09j]

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.

[brs09j · Accepted solution]

@MattAllington

It's loading now.

[v-yulgu-msft · Accepted solution]

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

[MattAllington]

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/

[brs09j]

@MattAllington

The website won't load for me.

[brs09j · Accepted solution]

@MattAllington

It's loading now.

[v-yulgu-msft]

Hi @brs09j,

Have you resolved the problem now? If so, would you please kindly mark the corresponding reply as an answer or sharing your resolution so that it can benefit more users? If you still have any question, please feel free to ask.

Regards,
Yuliana Gu