Duplicate values that do not exist?

[kdlfarm2007]

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!!

[v-jingzhang · Accepted solution]

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]

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.

[amitchandak]

@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