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