I cannot remove duplicate values

[Anonymous]

I had issues with another dataset where it says there are two more values than distinct values.  I tried changing it by removing duplicates and and changing it all to Upper case.  I had the same issue on a different dataset.  No matter what I do, I can't remove duplicates.  I could just do a many to many relation since it will act like a one to many since the rows are identical, but I would prefer an actual one to many relation.

[chris_kelley]

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. 

[chris_kelley]

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"

[edhans]

You cannot remove duplicates in Power BI's data model - you can filter, but not remove.

In Power Query, you can, There are two ways to do it. Right-click on a column and that will remove duplicates from that column regardless of what is in the other columns. Or, in the grid view, click the little table icon in the upper left corner to get the menu. Near the bottom is Remove Duplicates - this will only remove rows that are duplicates - every value in a column is checked.

Note though that this will NOT remove all nulls, which will cause a problem if you are trying to create a DIM table with a 1 to Many relationship. DIM tables cannot have nulls in their primary key, so usually I do both - remove duplicates on the primary key, then remove empty.

If that isn't what you need, please give us more info. We cannot see your dataset nor do we understand your data.

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.

[Anonymous]

I have tried to remove duplicate values in Power Query.  The duplicates aen't from null values.  For some reason I cannot delete them.

[edhans · Accepted solution]

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.

[Anonymous]

Using Trim worked.  Thank you.

[edhans]

Perfect @Anonymous - so it was extra spaces somewhere. Trim will safely remove leading and trailing spaces for you - never in the middle though, so no actual data changed. Glad I was able to help!

[Anonymous]

I fixed it by making the column in power query instead of in BI.  However, my other dataset still has ther problem (this column is not calculated).