Forum Discussion
Remove duplicates not working.. Can't create relationship
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.
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.
I'm having the same issue but the data set is only around 2000 rows.
Note that there seems to be an inconsisencty in how case sensetivity is handled: the Remove Duplicates feature is case sensetive but the uniquemenss check done when created creating realationshiops between queries is not. To work around this I just format the key column in the query to UPPERCASE. But this is an aside, I'm getting the issue even though cases are the same
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!
Had the same problem. Saw the post about case sensitivity with duplicate removing. Transformed column to all UPPERCASE. Removed duplicates (3rd time)... and then the relationship worked. Make sure everyone reports it so we can get this fixed!
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'.
Thank you so much for mentioning this issue with cases in remove duplicates vs joins! I had been struggling with this all day!
Anonymous
Create a table and then export data to excel and do a duplicate check there.
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.
What if it's a concatenated calculated column which you can't see in Power Query Editor?
Thank you! This solution worked for me to transform the values to UPPERCASE.
This really should get more upvotes. My issue was due to incorrect cases, too. I transformed all values to UPPERCASE and things worked out!
