Forum Discussion
Remove duplicates not working.. Can't create relationship
Hello,
I have a dataset with customer and a discount structure for that customer per part. I created a concatenate in the edit query screen of the customer, and there are a couple duplicates. Highlighted the Concatenate column, and clicked remove duplicates.
Tried creating a relationship and it didn't work. Created a summary table of the concatenate and COUNTROWS, and lo and behold there are a handful that have a count of two. Why is this happening?
Thanks
Yes, I double and triple checked. Everything was done the same way as in my example. Must be an error due to such a large data set.
23 Replies
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
I just had the same problem with joining two tables after I made sure to remove duplicates on the key column in one of the tables and getting the error message about duplicate values.
The issue actually was a NULL value in a single row in the table in my key column. Power BI was complaining about duplicates but in reality it was the null value that was preventing it from joining the two tables. I did a row filter and removed the NULL value and the two tables connected together just fine.
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!
Thanks, this works for me (in Apr 2022).
Removing duplicates using PowerQuery's menus doesn't remove NULL values so "Remove Empty" on the row after removing duplicates solves the issue as mentioned by aar0n.
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),]
Would have to see some sample data where the problem could be reproduced.
The same exact steps in the edit query in this situation worked. They do not with my large data set (10 Million lines & about 20 duplicates).
I would report this as an Issue: https://ideas.powerbi.com/forums/360879-issues
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.
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
