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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors