cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
robertDTS Frequent Visitor
Frequent Visitor

Data Refresh Fails, Error message claims duplicate ID value in table

I have a PBIX file where when I go to refresh my data from a SQL server the refresh eventually fails with an error message that says one of my tables contains duplicate IDs. I went to my actual SQL DB and ran a query on the table for the exact ID provided by the error message. Only one row is returned by the straight query, therefore there are no duplicate values in my table.

 

I'm thinking this is some sort of red herring and that the real issue lies either in a relationship or somewhere else in the data. Any suggestions on how to troubleshoot? I've updated PowerBI to the latest released version.

8 REPLIES 8
Super User
Super User

Re: Data Refresh Fails, Error message claims duplicate ID value in table

Can you share the exact error message that you are getting? Also, duplicates in DAX are not necessarily the same as in SQL so can you share the format of some of your ID's? DAX may be considering things to be duplicates that are not duplicates in SQL.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


robertDTS Frequent Visitor
Frequent Visitor

Re: Data Refresh Fails, Error message claims duplicate ID value in table

RefreshFail.PNGPowerBI Refresh ErrorSQL_query.PNGSQL QuerySQL_result.PNGSQL Result

Hi, here are images from PowerBI and SQL.

 

There is not much DAX in this data model. There are a series of Measures to cacluate the "Latest" SWC since each row has a unique data time stamp. The SWC table has some modifications in the Query editor, some look-up columns are expanded and the date column is duplicated so that I can have the date in multiple user readable formats.

 

Super User
Super User

Re: Data Refresh Fails, Error message claims duplicate ID value in table

Try doing a Trim and Clean on that field in Power Query. If you have it in there with a trailing space, for example, or maybe a leading zero I believe the model will consider that a duplicate but your SQL will not find that.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


robertDTS Frequent Visitor
Frequent Visitor

Re: Data Refresh Fails, Error message claims duplicate ID value in table

No dice, which I can't say I'm suprised. We authored the application that is filling the SQL DB and while our app is not perfect, one part that is rock solid is the SWC ID. Its the unique key for that table in SQL too, so SQL will not allow duplicates (though I agree there can be situtations where something might not be seen as a dupe by SQL).

Super User
Super User

Re: Data Refresh Fails, Error message claims duplicate ID value in table

Here's what I would do. Create a new Power BI file and copy over your query for this table. Run the import/refresh. Since it is not part of a relationship, you will not get that error and then you will be able to better assess what is causing the duplicate issue. I can't imagine you are getting that error on a lark, something is causing it.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


robertDTS Frequent Visitor
Frequent Visitor

Re: Data Refresh Fails, Error message claims duplicate ID value in table

Yeah, that is more or less where I've gotten to. Made a copy of the PBIX and started to isolate and delete stuff to see what came of it. Looks like I found a relationship causing the issue. We'll see.

 

Thanks for replying though!

 

-R

tnielsen Occasional Visitor
Occasional Visitor

Re: Data Refresh Fails, Error message claims duplicate ID value in table

I am experiencing the same issue when importing a file. The file format has not changed for months and so has relationships to other files. 

The column that returns the error only contians unique values in form of ID's, validated that using excel. For somereason powerbi claims that the column contains ,,,None,Functional which are values from another column in the data set. the columns are 9 columns appart in the data set. 

 

Error Message is below.

Column 'ID' in Table 'ReportExport' contains a duplicate value ',,,None,Functional' 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. Table: ReportExport.Cluster URI:WABI-US-NORTH-CENTRAL-redirect.analysis.windows.netActivity ID:debdaefc-f0cb-4ae0-b698-b77851d4e999Request ID:2305575e-f6be-c22d-0bb4-549b25ac04f4Time:2019-01-25 15:59:14Z

 

I have no idea how to solve the suddenly introduced issue.

 

Highlighted
samanthasampath Frequent Visitor
Frequent Visitor

Re: Data Refresh Fails, Error message claims duplicate ID value in table

Hi, did you ever find out why this was happening? I encountered the same error message when I was trying to load the query to the model so I removed the column containing the duplicate values -->successfully loaded to the model-->went back to the query and deleted the step for removing the column---> successfully loaded back to the model containing the column with duplicates. 

 

I have since tested several dashboards and gone through the same step of loading the model with the product ID (which continues to contain duplicates) and the query has loaded successfully without me having to go through the additional step of removing the column and adding back in. I'm not able to understand why it happened or why I couldn't replicate the error message. I am worried that any auto-refreshes once I publish might cause the query to fail at some point.