Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

"Remove Duplicate" doesn't remove all duplicate

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 ...
ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity 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's avatar
ImkeF
Icon for Community Champion rankCommunity 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    ImkeF try:

     

    Table.Distinct(Table, {"ColumnName", Comparer.OrdinalIgnoreCase})
    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      9 years ago

      Anonymous

      Totally 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}} )
      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        ImkeF that's cool, so you could potentially mix-and-match case sensitivity? Like Column1 ignores case, Column2 doesn't? I didn't test far enough to try anything like that. I just noticed that the second argument in Table.Distinct is a list by default if you let the query editor generate the code for you, so I tried adding Comparer.OrdinalIgnoreCase to the list.