Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

I cannot remove duplicate values

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.

1 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

8 REPLIES 8
chris_kelley
Helper I
Helper I

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"

edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Using Trim worked.  Thank you.

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!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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).

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors