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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      ImkeF and Anonymous my apology for the late reply.

       

      I tried ImkeF method it still doesnt work however I tried the "grouped by" function in "transform" tab and it works.

       

      Just extra step.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Anonymous your data source may not be case sensitive, but if the columns in question contain letters then Power BI will be case sensitive about them. But I do also like that non-printable character idea ImkeF.