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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors