Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Thor2022
Frequent Visitor

Power BI force me to apply many to many relation

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

Thor2022_0-1664788272522.png

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

Thor2022_1-1664788383519.png

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

Thanks in advance 

Regards 

 

1 ACCEPTED SOLUTION
PabloDeheza
Solution Sage
Solution Sage

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.

PabloDeheza_0-1664799933163.png

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!

 

View solution in original post

3 REPLIES 3
PabloDeheza
Solution Sage
Solution Sage

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.

PabloDeheza_0-1664799933163.png

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!

 

Thanks 

I checked that 

Thor2022_0-1664817686060.png

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??? 

Thor2022_1-1664817953533.png

 

Now I am very confused 😞 

Regards 

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors