# Data Model telling me there are duplicates I can't find



## Moxie425 (Jun 12, 2018)

Trying to make a connection from a data table to a lookup table. Everytime I do I get the following message: "The relationship cannot be created because each column contains duplicate values. Select at least one column that only contains unique values."

I cannot find any duplicate values in the look up table column. I have even gone to the data source (excel worksheet) to run the delete duplicates function and it says no duplicates found. I also initially found an empty row in the table and deleted it and then reloaded the data. It showed one less row was loaded from before so that row is no longer in the data model but still getting the error. This is a text column to text column lookup. Help!


----------



## gazpage (Jun 13, 2018)

Did you check for blanks? Did it bring in blank rows at the bottom, or between rows?

Otherwise you could create a query that just returns the column you would expect to have no duplicates, create another query where you use the remove duplicates function, and then merge the two queries with an anti-join to see what is left.


----------



## macfuller (Jun 13, 2018)

Also make sure when you eliminate duplicates in Power Query that you are turning off case sensitivity - PQ default is opposite that of Power Pivot.

Example:

xxx = Table.Distinct(PreviousStep, {Field Name, Comparer.FromCulture(Culture.Current, true)})


----------



## bkjohn2016 (Jun 16, 2018)

I had a similar problem a while back.  Have you tried cleansing for whitespace?  Go to your excel table and wrap the column in =Clean(Trim(TextField)).  I would then apply conditional formatting to the new column to highlight duplicates to see if it produces any results.


----------

