Unable to create relationship between tables

[kkanda]

Hello All,

This is not a  new problem. When I tried to link two columns of different tables in the Relationship pane, I keep getting the message that one table column must have unique values. I found many answers on the internet and I tried all of them. I have done the following on the table column (which is text)  which must have unique values:

1. I cleaned and trimmed the contents.

2. I converted the contents to uppercase.

3. Removed all duplicates and null values.

4. On the Excel source worksheet, I used a macro to detect any duplicate values and removed them. 

5. Removed all blank rows - this column has 285K values. 

6. Removed rows which have special characters like #.

Despite all cleaning and formatting the source worksheet and in Power BI, the above message will not go away. This is preventing me from establishing the relationship. 

I will appreciate if the experts can suggest any other method to make this column values unique. 

Thanks in advance

Krishna

[kkanda · Accepted solution]

Hello All, 

Thank you for your responses to solve my problem. Finally, I found that out of 278K cells, less than 20 cells have text including "?". I removed these rows and I was able to create the relationship. 

[Anonymous]

Hi @kkanda 

Try the following steps to solve the problem:

1. Use the TRIM() function to ensure that there are no leading or trailing spaces. 

2. Use the COUNTROW() function to create a calculated column to count the number of times each value occurs, to see if there are any values greater than 1.

3. If appropriate, consider creating a composite key by combining this column with another unique value column.

Refer to: Tips and tricks for creating relationships in Power BI Desktop 

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kkanda]

Hi @Ritaf1983 

Thanks for the response. The data types of the columns for both the tables are Text. I checked for blank rows and removed these rows. Both tables are large tables - the main table has 100K rows and the concerned column has repeated text values. It cannot be filtered to contain unique values as we need all rows of the table for further calculations. The second table has 285K rows and that is the table I am working on to get unique values in the related column. This is a large data set and sample values or a truncated tables may not help.

[Ritaf1983]

Hi @kkanda 
please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

[Ritaf1983]

Hi @kkanda 
I need to see the data to help....