Forum Discussion

BHinote's avatar
BHinote
Frequent Visitor
5 years ago

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?

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

    • BHinote's avatar
      BHinote
      Frequent Visitor
      5 years ago

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

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi BHinote ,

     

    It is suggested you check and manage the relationships. Remove 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.

  • soptim_vond's avatar
    soptim_vond
    Frequent Visitor
    5 years ago

    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