Dear all, I have a table with just one column, I tried to remove the duplicate in the column via power query. However, once I loaded to the dashboard I use count and count(distinct) both give me different number as the same number is expected. Best regards,Eric

Problem is, that Power BI has two different ways of handling data in two different situations. 1. Remove duplicates in Query Editor - it IS case sensitive, eg. "EMPLOYER" and "employer" are two different strings (are not duplicates)2. Building a relation - it IS NOT case sensitive, eg. "EMPLOYER" and "employer" are the same strings (are duplicates), therefore I can't build a relation Microsoft, please, fix this "feature" it is really annoying. Work with data one way in the application.

ImkeF try: Table.Distinct(Table, {"ColumnName", Comparer.OrdinalIgnoreCase})

Anonymous Haven't even thought of that, but computer says "yes" :-) Table.Distinct(Table.FromRecords({[A="one", B=1, C=2], [A="ONe", B=1, C=3]}), {{"A", Comparer.Ordinal}, {"B", Comparer.OrdinalIgnoreCase}} )

Well, if your table just consists of one column, you can actually use this formula: Table.ExpandListColumn(#table({"ColumnName"}, {{List.Distinct(Source[ColumnName], Comparer.OrdinalIgnoreCase)}}), "ColumnName") It's a bit of a bugger, because the only way I found to use Comparer.OrdinalIgnoreCase (which will ignore case sensitivity) was to use it in list. So if anyone has an idea how to make this a bit smarter, you're more than welcome :-) http://www.thebiccountant.com/2016/10/27/tame-case-sensitivity-power-query-powerbi/

AnonymousTotally awe!! Thank you so much! & so it looks with multiple columns: = Table.Distinct(Table.FromRecords({[A="one", B=1, C=2], [A="ONe", B=1, C=3]}), {{"A", Comparer.OrdinalIgnoreCase}, {"B", Comparer.OrdinalIgnoreCase}} )

"Remove Duplicate" doesn't remove all duplicate

15 Replies

ImkeF
Community Champion
9 years ago
This will happen when the terms have different case profiles. Pls check out if this article helps: http://www.thebiccountant.com/2015/08/17/create-a-dimension-table-with-power-query-avoid-the-bug/
- ImkeF
  Community Champion
  9 years ago
  Well, if your table just consists of one column, you can actually use this formula:
  
  Table.ExpandListColumn(#table({"ColumnName"}, {{List.Distinct(Source[ColumnName], Comparer.OrdinalIgnoreCase)}}), "ColumnName")
  
  It's a bit of a bugger, because the only way I found to use Comparer.OrdinalIgnoreCase (which will ignore case sensitivity) was to use it in list. So if anyone has an idea how to make this a bit smarter, you're more than welcome :-)
  
  http://www.thebiccountant.com/2016/10/27/tame-case-sensitivity-power-query-powerbi/
  - ImkeF
    Community Champion
    9 years ago
    So if you want a distinct of all columns in the table, it's pretty easy:
    
    Table.Distinct(Table, Comparer.OrdinalIgnoreCase)
    
    Still need to figure out how to handle column-selection in it.
Greg_Deckler
Community Champion
9 years ago
How many rows do you have? I have seen one other user reporting this and that user had millions of rows. I would report this as an Issue. https://ideas.powerbi.com/forums/360879-issues

Any chance you can post a link to the data so that this issue can be recreated?
- Anonymous
  Not applicable
  9 years ago
  Hi smoupre,
  
  Yes I have millions of row in the database. My apology I cannot post the data.
  
  I have posted this issue in the link you mention. Hopfully they come out with something more convenient.
  
  Anonymous and ImkeF my data is not case sensitive. Yet this happen. I'd tried your code just in case but the results are the same.
  - ImkeF
    Community Champion
    9 years ago
    Anonymous another thing you can try is to trim & clean before the remove-duplicates-step. Maybe there are some issues with non-printable characters or sth similar:
LarsSchreiber
Responsive Resident
9 years ago
Hey Anonymous@

I wrote a post already in February about the different understanding of duplicates of Power Query and Power Pivot here, but ImkeFs idea of using the Comparer.OrdialIgnoreCase porperty is great and simple.

Thanks a lot :-)

Forum Discussion

"Remove Duplicate" doesn't remove all duplicate

15 Replies

Related content

Removing "duplicates"

"Remove Duplicates" taking forever to refresh for 2 column table

Issues in "Remove Duplicates"

"Remove duplicates" removes unique values in the format of whole number

"Remove Duplicates" in Power Query Editor is not working