Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Duplicate Value

Hi, 

Everything worked good and suddently I get an error when trying to refresh the data set.

The error says: "Column 'robot_id' in Table 'bi_steady_updates' contains a duplicate value '00119341460' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table."

I checked the table and there is no duplicated. Anyway I went to the Power Query and deleted duplicated values, but I still get the error.

The only way not to get it, is to edit the relation between the tables, I can't make a 1:1 relation with this table (as it really has duplicated values).

Any ideas?

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I tried to delete duplicated in the Power Query, but still got the same error when refreshing.

    Finally I have deleted the row in the database as a workaround and that let me refresh now.

    Thanks!

10 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    6 years ago

    Hi. I think I remember two times this happend to me with different cases. The first one was having null or blank values that won't let you create the relationship and will show you like duplicates. The second one is in case the column is a text column. If you have text column first modify the column to upper o lower case before removing duplicates to be sure you are not having values like "a35728" that won't remove if you have another like "A35728".

     

    Hope any of those helps.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I just did it again and see that there are 2 records with the same ID in the table.

      When checking in the database there is only one record. Is a MongoDB and I'm using an ODBC connector to bring it to PowerBI.

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        6 years ago

        Hi Anonymous ,

        You could remove the duplicate rows in query editor and try again.

        Best  Regards,

        Cherry

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Absolutely great tip about the lowercase/uppercase thing.  I would have assumed that "remove duplicates" would match match "abc123" and "ABC123", but it doesn't.  Once I added a "trasnform to uppercase" before my "remove duplicates", I could then create the 1-to-many relationship I wanted.

  • sabirguiri's avatar
    sabirguiri
    Frequent Visitor
    6 years ago

    The best practice that I would suggest is that you don't delete the rows but do the following.

     

    1. Go to Model on the left bar and then click 'Manage relationships' under the 'Home' menu.

    2. Select which relationship is causing the issue and then deleted it.

    3. Open Power Query and then go to the 'Add Column' menu and click 'Index Column From 1'

    4. Refresh all queries and then click 'Load & Apply'.

     

    This worked well for me without having to delete records.

     

    Sabir

  • jayaxe's avatar
    jayaxe
    Advocate I
    5 years ago

    I found another root cause why this happens and it's the "Auto-detect relationship" function being the culprit. As my company has many entities around the world, I have multiple tables to append/combine. The "Auto-detect relationship" function which was turned on by default, connected some tables without my knowledge (e.g. Customer Name (2) to Invoice No. (2) Customer Name). At first everything was ok, since there was no duplication and the relationship was fine. But the moment there was a duplicate Customer Name in the unique list table, this auto created relationship now becomes "Many to Many". 

     

    Therefore the solution is to delete all these unneccesarily created relationships and also turn off  the "Auto-detect relationship" function. Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I don't have any relationship between the table still detected wrong relation and making errors.This helped me

      Thanks alot. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello Viewers ,

    I am Facing Error when i use PBI Cleaner Tool -

    " Column 'Key' in Table 'StorageSize' contains a duplicate value 'Distinct number of units Material.MATERIAL' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table. "