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

Refresh failed - Column 'X' in Table 'Y' contains a duplicate value (This is a Invalid Error)

I have had a report that has been running on the PowerBI.com Service for many months using our on-premise gateway to access our SQL Server and until recently it has been running without issue, refreshing the DB 6 times a day per the Schedule.

 

Recently the report started Failing Refresh indicating "There's not enough memory to complete this operation".  To address this, I configured the DB with Start and End Parameters, Assigned the Filter, and Turned on Incremental Refresh.  When I published the Report it ran the On-demand Refresh and Completed Secusfully.   However, the next refresh Failed indicating "Column 'RootID' in Table 'SPSOrderProfileAncestorSegmentView' contains a duplicate value '<pii>-2147477907</pii>' 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."

 

When I Queried the Table, on our SQL Server, I found there was Only One Record with that ID Identified in the error.  There is no reason why the queried table would ever have a Duplicate Key-Value and the Relationships that I have created in the Power BI Database accurately identify the SPSOrderProfileAncestorSegmentView as the "One" side of the Many Relationship.

 

Nothing I have tried has resolved the issue and Every Time I Publish the Report the Initial Refresh Always Completes without issue, but every subsequent refresh Fails with the same error.

 

How can I identify and resolve this issue?

5 REPLIES 5
soptim_vond
Frequent Visitor

Hi everyone,

 

I am experiencing the same issue pulling data from an oracle database.

Everything is fine in pbi desktop, but as soon as I use incremental refresh in the service (for exactly the same data i.e. no changes in time dimension etc.) I get the error message related to duplicate values in a primary key column. Even after adding a step "remove duplicates" on the column on question (which is actually not necessary, because there cannot be duplicates in this column in the source) I keep getting this error.

 

Does anyone have any suggestions on how to tackle this?   

 

Thanks in advance,

 

Nico

V-lianl-msft
Community Support
Community Support

Hi @BHinote ,

 

It is suggested you check and manage the relationshipsRemove the invalid relationships.

Made a copy of the PBIX and started to troubleshoot to see what came of it.

Try to manually modify or create table relationships in power bi desktop.

 

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

Joorge_C
Resolver II
Resolver II

Hi @BHinote  Before refreshing (if possible) in Desktop, remove the relationship which is being indicated as the problem.

 

Then complete the refresh.

 

Once complete, look through your data and see if you can spot the duplicate Value.

lbendlin
Super User III
Super User III

Check for NULL values in your key column too.

BHinote
Frequent Visitor

Thank you for your suggestion.   There are no NULL Values in the key column.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors