Not able to change Many to Many relation to One to Many relation

[chitti5]

Hi All,

I have two tables, each with over 40 columns. I want to connect them using a unique value which is alphanumeric that contains numbers,alphabets and special characters. However, when I tried, I ended up with a many-to-many relationship due to duplicate values in the columns. I can't append the tables because of their size.

I attempted to add a bridge Dim table (deleted all duplicate values from the Dim Table column) to create a one-to-many relationship with each table, but I'm still unable to achieve the desired relationships. Could the issue be that the unique value column contains commas and slashes?

I tried removing commas and slashes from the unique value column and even separated the columns containing these characters, but it didn’t solve the problem. The data types of both columns from both tables are also same i.e. "text". I’m still unable to establish the desired relationships between the tables. Can you help me figure out what’s going wrong?

Screenshots below:

[rohit1991]

Hi @chitti5

This usually happens when your supposed unique column has hidden issues like extra spaces, case mismatches, or special characters.

Try cleaning it with TRIM, CLEAN, and UPPER/LOWER in Power Query. Also double-check for blanks or duplicate values using DISTINCTCOUNT in DAX. Even one blank or mismatch will block the relationship.

[Greg_Deckler]

@chitti5 So, the most common causes of this:

1. As far as the semantic model is concerned, "a1" and "A1" are identical
2. Having even a single blank value in each table