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

Moxie425

New Member
Joined
May 16, 2018
Messages
3
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!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.
 
Upvote 0
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)})
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top