Dataset based on dataflows failed to refresh - claims duplicates, there are none

[AliceW]

Hi team,

I've recently started using dataflows and love the concept! However, I have a problem and I could use your help.

I have two dataflows: Account and Opportunity. Each has one table.

In the dataset, I have linked them via a one-to-many relationship.

I have scheduled the refresh of both dataflows and dataset.

It worked fine for about 6 refreshes, when I got this error:

Column 'Account ID' in Table 'Account' contains a duplicate value '<pii>215584</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: Account.

Which I know it does not, as I included a step, in Power Query, in the Account dataflow, to remove duplicates for this specific column.

What do I do, please?

Thank you in advance,

Alice

[v-diye-msft]

Hi @AliceW 

Kindly refer to the same solved thread for some hints:

https://community.powerbi.com/t5/Desktop/Refresh-error-quot-contains-duplicate-value-and-this-is-not... 

If not fit your issue, please kindly raise it to the support team for better advice:

https://powerbi.microsoft.com/en-us/support/ 

[AliceW]

unfortunately, the suggestion marked in there as solution doesn't work for me. The relationship between tables is 'many-to-one', and the issue is with the 'one' side.

[AliceW]

Update: I've also removed duplicates in the dataset itself and that did the trick. However, I shouldn't have to, should I? I'm confused.

[frano72]

Hi,

If you removed duplicates in PQ inside the pbix file then that explains the original error you got and why you now say its working.

You are correct in saying you shoudln't have to do that...if the PQ step in the dataflow was working correctly.

You need to investigate that dataflow PQ code....

Any chance it was just a timing issue, ie the dataflow hadn't finished refreshing when you did your first test ?

[AliceW]

That dataflow code was so basic - just connect to the table in the database, remove some columns, change some names, and... remove duplicates.

And the dataset refresh is scheduled half an hour after the dataflow one, and a refresh lasts about 7 minutes.

Super weird...