"One of the Columns Must Have Unique Values"

[sgross]

I'm getting an error message, but one of the columns definitely does have unique values.

In Excel, I've highlighted duplicates, deleted duplicates, changed formatting to catch things that were technically duplicates with dashes in-between the numbers, and deleted duplicates all over again. According to Excel, there are no duplicates left. Not a one. One sheet is all unique values.

Any idea why I'd still be getting this error? I've tried everything I can think of. I am bamboozled.

[Clinton_Earl]

I experienced a similar issue.  I used "remove duplicates" within the query editor but PowerBI still showed the column as "many" when trying to build a relationship between my tables.  I found that one blank row was causing this issue (and it was NOT removed by using "remove duplicates") and was fixed by adding a step to "remove empty" on the field's filter.  Now, works like a charm!  I wish it was more apparent that issue.  Just sharing my thoughts for someone else who may have the same issue.

[Anonymous]

After a lot of screaming about this myself, I learned some things which I have posted to another thread on the same issue here: https://community.powerbi.com/t5/Desktop/Can-t-create-a-relationship-between-two-columns-because-one...

[Diego-mx]

This is the most frustrating error EVER!  
I'm getting the same error, and even went to filter the column by "Remove Duplicates" button in the query editor. 
Doesn't fix it.  
I hope someone knows what's going on. 

[sgross · Accepted solution]

Cracked it!

I scrubbed through manually (thankfully it was only a few hundred entries, rather than thousands) and found two entries with unnecessary spaces tacked onto the end - making them technically unique values, invisible to any Remove/Highlight functions!

Worth noting that, before I stumbled upon these, I ran a Find and Replace in Excel, attempting to find spaces and replace them with nothing; clicking into the 'find' field, there was already a space in there (from a previous operation, I guess?) meaning I'd accidentally been searching for and trying to replace TWO spaces, rather than one. So, that's why it didn't work!

Cheers for your help, everyone!

[Greg_Deckler]

Glad you got it fixed. You can use the trimming function in the query editor to remove trailing spaces.

[sgross]

I'll be sure to give that a go next time, thank you!

[Greg_Deckler]

Perhaps you have some blanks or something? I would use the "remove duplicates" in the query editor as well as remove errors. Also, use the drop down in the column and look for any wonky values. Also check uppercase/lowercase spelling if it is a text field.

[sgross]

Nope, unfortunately not!

No blanks in the unique list (Used 'Remove Empty'), ran 'Remove Duplicates' in Power BI's Query Editor as well as Excel's...

Went back into Excel, ensured that all letters (only 'D' and 'T' appear in amongst the numbers) are upper case, and ran 'Remove Duplicates' again... still no joy!

[v-chuncz-msft]

@sgross,

Use DAX SUMMARIZE Function to have a check.

[KHorseman]

Also check your case. Excel sees "word", "Word", and "WORD" as duplicates. Power BI's query editor does not, but once you load the query the relationship editor will see them as duplicates.