Forum Discussion
Very simple 'Remove Duplicates' not working
I need to very simply remove duplicates for one column in a query. I want that one column to be the matching ID so I can match a 'many-to-one' relationship. I have tried doing all of the below steps but still end up with duplicates and no way to get any relationship to work except for many-to-many.
After Selecting the column I want to remove duplicates on and Right Clicking that column --->
1.Remove Errors
2.Filtered Rows (blanks) [This probably isn't doing anything to help?]
3.Trimmed Text
4.Cleaned Text
5.Remove Duplicates
after performing all of these steps, I still get duplicates in this field. What else can I do to 'normalize' these varchar text lines?
I cannot upload a .pbix (company policy) but I can email anyone personally the pbix or images of the problem.
This is in a 'SQL Server Database' import data option.
The field is a VarChar Text field and it is a "Manufacturer Number" with lots of various characters.. IE: -=,.!#$%^&* etc....
I received a couple email requests to send the PBIX out. As I was trimming down the 77mb file to a more manageable size, I tested the data in excel and also change my query. There definitely was duplicates in that column. I have fixed the problem by removing the 'Group By' function in the SQL code. I did not know Power BI would retain that 'group by' functionality after data had been retrieved. This makes me wonder what else Power BI retains from SQL code.
3 Replies
Sometimes this involves upper and lower case issues. I'd be interested in seeing example/sample data if you want to send in a private message.
Anonymous ,
After Selecting the column I want to remove duplicates on and Right Clicking that column --->
1.Remove Errors
2.Filtered Rows (blanks) [This probably isn't doing anything to help?]
3.Trimmed Text
4.Cleaned Text
5.Remove Duplicates
How do you trim the column? Please check if there's still some values in the column contains blank such as "#$%as1 ".
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I received a couple email requests to send the PBIX out. As I was trimming down the 77mb file to a more manageable size, I tested the data in excel and also change my query. There definitely was duplicates in that column. I have fixed the problem by removing the 'Group By' function in the SQL code. I did not know Power BI would retain that 'group by' functionality after data had been retrieved. This makes me wonder what else Power BI retains from SQL code.
