Refresh issue SQL DB

[filipovski]

When refreshing the dataset in PowerBI Desktop, often I receive the following error, stopping the refresh:

Column 'SalesId' in Table 'Sales' contains a duplicate value '15a2e46d-0879-ec11-b85a-6045bd8ea0bb' 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

In my report there are around 30 tables, but this error happens for just two of them. The value is a primary key, and it's unique.
The only solution I found is to refresh the dataset continuously until the error goes away, usually after a dozen of attempts.
Also, this never happens in the published report

Why does it happen? Is there any better solution other than refresh continuously?

[v-xiaotang]

Hi @filipovski 

Do you mean that when you connect to the power BI dataset in the desktop and click refresh button, an error will be reported? But if you keep clicking refresh button, it won't have an error prompt?

Will it work if you clean trailing and leading spaces in your data in PQ Transformation?

Here is a similar problem I saw on the issue forum. Please check whether it is useful: https://community.powerbi.com/t5/Issues/Unable-to-refresh-because-of-blank-values-that-actual-don-t/...

Best Regards,

Community Support Team _Tang

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

[TrItalo94]

Hi, I'm a colleague of @filipovski who in the meantime has left the company.

We finally solved the problem by adding a step to remove duplicate rows in the Power Query Editor for the two columns that gave us that error.

It's still not clear to us why it happened in the first place, since SalesId and the other column are the primary keys of their database tables and as such had no empty/null/duplicate values.

Also, when we first wrote about this problem, it would solve itself by just repeating the refresh until it didn't work, but as time passed and more work was done it took more and more attempts to successfully complete a refresh of the data.

I suspect it might have been a cache problem, but clearing it didn't solve anything.

I would like to ask any moderator if it can set this message as the accepted solution

[amitchandak]

@filipovski , On of the table SalesId, and that is one side on 1 to Many relationship.

That table is getting a null or duplicate value. You have to check that table. Blank/null value can also cause the issue 

[filipovski]

SalesId is the primary key of table Sales. I've checked again for caution, but there are no blank nor duplicate values for that field. Even if that were the case, it wouldn't explain how repeating the refresh multiple times works