Forum Discussion
I cannot remove duplicate values
THere are a few things you can check. Power Query is case sensitive, so Apple, APPLE, and apple will not consolidate down to Apple if you remove duplicates. All 3 will remain. You need to change them all to proper, upper, or lower case.
"Apple" and "Apple " are not the same either. The latter has a space. Use TRIM or maybe CLEAN. TRIM is better for spaces, CLEAN will remove non-printing chars.
Other than that, you will need to provide data. Remove Duplicates works just fine, there is no bug. So it is something in how you are doing it or in the data that is preventing it.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
I ran into the same issue even after I used the Text.Trim function. I was able to solve the issue by removing HIDDEN CHARACTERS. What are hidden characters you may ask. I don't know, but you can remove them by using the Text.Clean function. That may clear up the issue, but I would say that since you cannot see the characters that are hidden and those are causing the issue that Remove Duplicates does not work just fine and that there is a bug. Microsoft should either show the hidden characters so they don't register as duplicates that aren't duplicates or automatically remove the hidden characters. Instead, what we have are duplicates that are not being removed.
this is the M Language that ended up working:
let
Source = Table.Combine({#"NamesBasic sql", #"NamesBasic Exchange"}),
CleanEmails = Table.TransformColumns(
Source,
{
{"EmployeeEmail", each Text.ToLower(Text.Trim(Text.Clean(_))), type text}
}
),
RemoveHiddenChars = Table.TransformColumns(
CleanEmails,
{
{"EmployeeEmail", each Text.Select(_, {"a".."z", "A".."Z", "0".."9", "@", ".", "-", "_"}), type text}
}
),
#"Removed Duplicates" = Table.Distinct(RemoveHiddenChars, {"EmployeeEmail"})
in
#"Removed Duplicates"