duplicates issue

[Ranjith_96]

Hi Power BI Community,

I am getting below error when I try to refresh in Power BI Cloud (First time full refresh, configured incremental refresh).

The initial refresh is not getting succeeded becuase of the below issue,

Column '<oii>sub_id</oii>' in Table '<oii>fact_subscirbers</oii>' contains a duplicate value '1153' 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..

I have only two tables, fact_Subscribers and fact_billings

Fact_subscibers columns,

sub_id

subscribedon

serviceid

operatorid

partnerid

fact_billings column

billed_date

sub_id

amount

so, the realtion is Many to One from fact_billings to fact_subscribers. i have given relationshipfrom billings table to subscirbers table based on sub_id which is many to one. And I have ensure there were no other relationship, since these two were the only tables I have in my relationship.

I dont know why Im getting this fact_subscirbers</oii>' contains a duplicate value '1153' error. eventhough there is no duplicates in that table. i checked in database as well using this value which is mentioned as duplicated but the value is present only once in the table.

everytime it is giving different different values as duplicates.

 Can someone please help me on this, Any help would be greatly appreciated.

note: i have configured incremantal refresh, and im getting this error when i try to do the full refresh for the first in power bi service.

[Anonymous]

Thanks for the reply from @aj1973 , please allow me to provide another insight:

Hi, @Ranjith_96 

Regarding the issue you raised, my solution is as follows:
1.First of all, based on the error, we think we still need to check for hidden duplicates to make sure that there are no hidden duplicates in the "fact_subscribers" table. You can try using deduplication in PowerQuery.

Of course, you can also turn on the following settings in PowerQuery to see if there are invalid values in the data.

2.Second, check if the data types are consistent:

Double-check that the data type for sub_id matches in the fact_subscribers and fact_billings tables. Sometimes, mismatches in data types can lead to unexpected behavior.
 

Here is a screenshot of the official documentation:

Here's how to change the data type in PowerQuery:

3.Then verify the relationship to make sure that the relationship you established between the "fact_billings" and "fact_subscribers" is correctly defined as many-to-one, with the "fact_subscribers" on the "one" side.

4.Finally, check the incremental refresh policy: make sure that the incremental refresh policy is configured correctly and that the partitions generated by the policy are not introducing duplicates.

Of course, you can also try to temporarily disable incremental refresh and try a full refresh to isolate if the issue is related to the incremental refresh configuration.
Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft...

Best Regards,

Leroy Lu

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

[Ranjith_96 · Accepted solution]

Thanks @Anonymous  .  I figured out the problem. It is because I gave RangeStart >= and RangeEnd <=. Ideally one has to be less than without =

[Anonymous]

Hi,@Ranjith_96 

We are very glad to know that the issue has been resolved. If you wish, consider accepting your solution as a solution that will also benefit other community members who have the same problem as you and find a solution faster.

Of course, if there is anything else we can do for you, please do not hesitate to contact us.

Looking forward to your reply.

Best Regards,

Leroy Lu

[aj1973]

Hi @Ranjith_96 

Open Power BI desktop and try to refresh the report. Most likely you will get the same error message. Try to figure it out.