Forum Discussion

kdlfarm2007's avatar
kdlfarm2007
Regular Visitor
5 years ago
Solved

Duplicate values that do not exist?

Power BI: Duplicate Value Issue

Hi All,

 

I've been getting 1 of 2 error messages when I try to refresh:

 

1) Data source error: Column 'PartNum' in Table 'job head part cost' contains a duplicate value '<pii>00125-002</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. Table: job head part cost.

 

2) Data source error: Column 'PartNum' in Table 'Erp PartCost' contains a duplicate value '<pii>00125-002</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. Table: Erp PartCost.

 

However, when I look into both of those tables, neither actually has a part number 00125-002. Rather, each has a part number 00125-000 (but no 00125-002).

 

What should I do to fix/troubleshoot this? Thanks!!

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi kdlfarm2007 

     

    To avoid losing anything, you can create a copy of the pbix file to troubleshoot this. In the copied file, remove all the relationships between tables 'job head part cost'/'Erp PartCost' and other tables, then try to refresh. If refreshing successfully without error, try Amit's suggestion to create a table visual to show the count of each number in Column 'PartNum' in each table. If there are duplicate values, their count numbers will be larger than 1.

     

    Additionally, do you execute any transformation on data to reduce rows in Power Query Editor? If not, you can also check the duplicate values at the data source side. If previous refreshings were all successful, this may be caused by newly added duplicate values at the data source side.

     

    A common cause is the upper/lower case of the values. Power BI Desktop is case insensitive in the model, which may lead to duplicate values when the data source is case sensitive.

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    kdlfarm2007 , Create a visual on the table job head part cost, hope this on 1 side use PartNum and count of part num. Sort the count and check you do not have any row with more than 1. Also, check you do not have any null value for PartNum. Do the same for other table

     

    I doubt you either have null value or duplicate value.

    Also make sure , you close and open power bi file once

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    Hi kdlfarm2007 

     

    To avoid losing anything, you can create a copy of the pbix file to troubleshoot this. In the copied file, remove all the relationships between tables 'job head part cost'/'Erp PartCost' and other tables, then try to refresh. If refreshing successfully without error, try Amit's suggestion to create a table visual to show the count of each number in Column 'PartNum' in each table. If there are duplicate values, their count numbers will be larger than 1.

     

    Additionally, do you execute any transformation on data to reduce rows in Power Query Editor? If not, you can also check the duplicate values at the data source side. If previous refreshings were all successful, this may be caused by newly added duplicate values at the data source side.

     

    A common cause is the upper/lower case of the values. Power BI Desktop is case insensitive in the model, which may lead to duplicate values when the data source is case sensitive.

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

    • tsibilski's avatar
      tsibilski
      Advocate I
      2 years ago

      I had the same issue. You can't spot it in the Power Query. Counting rows in DAX shows the outlier. Solution: TRIM in Power Query.

  • ptpaton's avatar
    ptpaton
    Frequent Visitor
    1 year ago

    Error occurred when I tried to convert from direct query to import. Duplicate values on a column that was an identity column used as a primary key in SQL server, so I knew there were no nulls or duplicates. The duplicates were created because I merged a query and expanded a column. Apparently direct query can handle this but import can't. Removed the expanded column, converted to import, remerged the query and reexpanded the column did the trick