cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SteveDorr Frequent Visitor
Frequent Visitor

Re: Remove duplicates not working.. Can't create relationship

I just had the same issue the error having duplicate values on the key I was using to connect two tables.  I had removed duplicate values based on the key column and I was still not able to join the two tables together.

 

It turns out the problem was a null value in the key column.  I did a row filter and removed the null value and the two tables joined together fine.  The error message was misleading.  

Highlighted
TobiasOwen Member
Member

Re: Remove duplicates not working.. Can't create relationship

I am getting this issue with the latest version of Desktop as well. My dataset is 4,700 rows of data. Remove Duplicates is applied in the query stage, but when the data is loaded into the data model there are duplicates in the data.

 

I cannot manually search for the duplicate values in the query because it says 'Limit of 1000 values reached'.

PedroSimao Visitor
Visitor

Re: Remove duplicates not working.. Can't create relationship

I found a work around to this...

1. Duplicate the query with "Duplicate values"
2. Remove all columns except the one you want to be unique.
3. Group by.
4. Do a "Left Outer" Merge with the original Query

joaosa Visitor
Visitor

Re: Remove duplicates not working.. Can't create relationship

Hello,

 

If you are removing duplicates from a text column (costumer) verify if you dont have upper and lower case letters on the same field.


I.e: "Customer1" and "customer1". If you remove duplicates, power query will consider them as unique references and wont remove any of the them. But while creating relationships you will get an error of duplicate values.

 

BR

aar0n Member
Member

Re: Remove duplicates not working.. Can't create relationship

Another thing to look for - 

 

if you have "null" and blank values, it seems to treat them the same when you "remove duplicates".. but then it considers them not unique in the relationship view...

 

 

what i did that worked, was to click "remove duplicates"  - then inthe "Home" tab in the ribbon, click on the drop-down in "Remove rows", and click on "Remove Blank Rows".

 

after removing those blank rows, everything worked great for me!

 

 

KH11NDR Member
Member

Re: Remove duplicates not working.. Can't create relationship

@TobiasOwen

 

Create a table and then export data to excel and do a duplicate check there.

TobiasOwen Member
Member

Re: Remove duplicates not working.. Can't create relationship

I don't need to check duplicates, I need the query to filter them out properly so I can create a many-to-one relationship that doesn't return an error and mess up the scheduled refresh every time another duplicate is entered on the list.

aar0n Member
Member

Re: Remove duplicates not working.. Can't create relationship

The solution that has worked for me:

 

in the query editor, select your column that you want to be unique and then:

 

1) Click on the "Transform" tab in the ribbon, click on the "Format" button, and then select "UPPERCASE" or "lowercase"

2) make sure the column is still selected, then click on the "Home" Tab, and then click on the "Remove rows" button, then select "Remove Blank Rows"

3) make sure the column is still selected, then click on the "Home" Tab, and then click on the "Remove rows" button, then select "Remove Errors"

4) Finally, click on Remove Duplicates button.

 

 

So far, these steps have always worked for me!

KH11NDR Member
Member

Re: Remove duplicates not working.. Can't create relationship

What if it's a concatenated calculated column which you can't see in Power Query Editor?

mattlawrence Frequent Visitor
Frequent Visitor

Re: Remove duplicates not working.. Can't create relationship

For those who require a different solution, try using an R script to remove the duplicates.

 

I used the following script to solve the same problem:

 

output<-dataset[!duplicated(dataset$myColumn),]

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors