Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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!

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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. "

jayaxe
Advocate I
Advocate I

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.

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

Thanks alot. 

sabirguiri
Frequent Visitor

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

ibarrau
Super User
Super User

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.


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

Happy to help!

LaDataWeb Blog

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.

Anonymous
Not applicable

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.

Hi @Anonymous ,

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

Untitled.png

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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!

1. The rootcause is the table which has been automated related to other tables which you don't know. Click the table to check all of its relationship, then delete the unwanted relationships. After that, everything works normally.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors