error duplicate value

[Anonymous]

Hi,

I receive suddenly error when loading my tables that there is a duplicate value in one of my tables which is not allowed for a one-to-many relationship.

I however checked the table and I cannot find any duplicate value, especially because I put in the query editor to remove all duplicates. 

Any idea how I could possibly solve this?

Regards

[TomMartens · Accepted solution]

Hey @Anonymous ,

sometimes these errors are based on the fact that there are different spellings, this means that apple and Apple are considered differently by Power Query.

To get rid of this issue I first lowercase or UPPERCASE the value I want to use for the relatioship, then I remove duplicate values.

Hopefully, this helps to tackle your challenge.

Regards,

Tom

[Anonymous]

Thanks both 🙂

thanks to your advice I found that there was a blank space added at one of the values.

[Martin_D · Accepted solution]

Hi @Anonymous ,
A popular cause of this is that Power Query is case sensitive whereas DAX by default is not case sensitiv. Thus a remove duplicates step in Power Query might keep values like "name" and "Name" as distinct values wheres the data model considers them duplicates. A remove duplicates step on a text or untyped column in Power Query is in general not an appropriate means to create a unique key column for the datamodel. As a workaround you can duplicate the column that shall contain your unique keys, then transform the copy to all lower case, then remove duplicates on the new column, then remove the new colum again.

If this solution does not relate to the cause of your problem, then a versatile approach to debug the problem is: 

• create a table visual
• put the field from the "one" side of your relationship into the first column of the table visual without aggregation
• put the same field into the second column of the table visual with aggregation "count"
• sort the table visual by the second column decending

This will show you which values are considered duplicates at the top. This might help you to identify the cause of the problem and solve it.

Cf.:

Power Query after remove duplicates step appears as:

Is loaded into Power BI as (the first occuring uppercase and lowercase spelling is applied for all duplicate values):

And appears in the table visual as:

BR
Martin

[TomMartens · Accepted solution]

Hey @Anonymous ,

sometimes these errors are based on the fact that there are different spellings, this means that apple and Apple are considered differently by Power Query.

To get rid of this issue I first lowercase or UPPERCASE the value I want to use for the relatioship, then I remove duplicate values.

Hopefully, this helps to tackle your challenge.

Regards,

Tom