Power BI force me to apply many to many relation

[Thor2022]

Hi All 

I have a table with column A, 
in the Power BI query : 

by using "Add as New Query", I separate this column, convert it to a table, and remove duplicate. 

Now I have a new table B with one column of unique values which can be used as the primary key to connect table A in one to many relations. 

The problem is that whenever I try to do that in Data Model it gives me Many_to_many relation

even when I want to change the Cardinality to one to many. it rejects it. 

Could anyone help me understand how this could happen and why? 

Thanks in advance 

Regards 

[PabloDeheza · Accepted solution]

Hi there!

Double check that the column has unique values, it seems like it doesn't. You can select the column in Power BI and check what it says at the bottom like this.

I also read in another post that upper and lower cases where messing this up in some cases, so you can trys converting all to lower or upper before removing duplicates.

Let me know if that helps!

[Thor2022]

Thanks 

I checked that 

so it seems that we have one value extra, despite that I applied to remove duplicate !!!??? 

What should I do in this case to find this value? 

adding to that, Power Query, shows 223 unique and distinct values??? 

Now I am very confused 😞 

Regards 

[Imrans123]

That is very strange. HEre are three scenarios and suggestions

1. Are there more power query transformations after the remove duplicates? If so, do you think the duplicate came in on one of those steps? Quite often I've seen situations where a merge query happens and depending on the type of join, it creates a duplicate. You could maybe move remove duplicates at the end if this is the case 

2 You could troubleshoot the particular datapoint giving you the issue. Make a matrix, on the rows, throw in the unique column, and in values, throw in the count of the unique column. Sort descending by count and see which value has a count of 2. One you know which datapoint is giving issue, go into power queyr, after remove duplicates, filter out that particular value, then go through all other power query transformations and see on which step the duplicate shows up

3. It's highly unlikely, but it maybe inconsistency in text that power query may be ignore. If the column that shuould have 222 unique entries is a text column, make a copy of that column, text tranform and trim and clean. Remove duplicates from the cleaned up column and see whether the new column gives you the desired unique values. If so, do the same text transformations on the columns which has a relationship with this table.