Error when creating a relationship

[cnpdx]

Hello,

I am trying to create a relationship between all my tables using "account name unique" (which should be unique), and my other tables, such as "account name" in the opportunity table (it's a Zoho CRM database).

In this example, I need it to be a one to many relationship (Account name unique>>>Account_Name in opportunities)

The problem is that I keep getting this error message: "You can't create a relationship between two columns because one of the columns must have unique values"

I have created many relationships in Power BI databases with success and understand the concept of one to many, but for some reason, the system thinks this field is not unique values. Things I have tried to get the system to accept the relationship:

-I used the query editor to remove duplicates from "account name unique", so the names should be unique

-I used the query editor to remove errors from "account name unique"

-exporting the "account name unique" to Excel, and searching for duplicate values. There were none

-I do not have the chance to use an ID or other numeric key field (unfortunately), account name is the only thing that will relate

What can I do to get Power BI to accept this relationship?

Thank you!!

[anithat]

Check if you Nulls or blanks in the specified column.

[lawsonbe]

You probably should not have to do this but a last resort would be to add a unique ID column and just give a number. You would then have to use that record along with all the entries in your source tables. Again, a little bit of trouble now but it may save you some headaches later.

[cnpdx]

If I make a unique ID, what am I matching it up to? Neither the fact or dim tables have a unique ID. So if I make a unique ID on the fact table, how does that help me relate records in the dim table? 

[lawsonbe]

You would have to change the entries in the dim and fact tables to use the ID instead of the actual name for this to work. You would really be building a more traditional relational database. Again, may not be worth the time. I have seen a couple of articles cautioning against trying to recreate a database structure in PowerBI. There is probably a much smarter way to do it.

[Rfranca]

@cnpdx
This kind of mistake has already happened to me.
In this situation I do a manual validation with the help of Excel.

1. Copy all records from the "account name unique" column of the ACCOUNT NEME CLEAN table to an Excel worksheet;
2. Copy all records from the "account_name" column of the OPPORTUNITIES table to an Excel worksheet;
3. Use the MATCH () function to find duplicates

It always works out.

Another careful detail with records with special caractees may present problems in table relationships.

[cnpdx]

I tried the Match function, which gives me the relative placement of the name in the list. How does that help me?

[Rfranca]

@cnpdx

When the MATCH () function does not return error this means that all records have their respective relationship.

Use the COUNTIF () function to check how many records are related between each table. The result must be 1 for all rows.

Also check empty line this can cause in errors in the relationship.