Duplicate value error while refresh without having a duplicate

[FilipK]

I receive constantly an error during refresh on PBI Embedded (Gen 1) instance.

Error message is:

Column 'w_epochtime' in Table 'Data' contains a duplicate value '<pii>0-000-000\0050:1609455620276</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.

The weird thing: There is no duplicate value in my data at all. I've checked that more than twice.

• This column comes directly from SQL view, so no processing in PBI. 
• In PBI desktop I don't receive that error
• In PBI desktop Ionly get one single line when filtering w_epochtime for that entry
• Deleting the entire dataset plus report from the PBI workspace and uploading again didn't help

What else could be worth to check?

[v-kkf-msft]

Hi @FilipK ,

Did you use incremental refresh? you have full data load and that's when there is a duplicate /blank value and causing the issue in the service and not in the desktop since in desktop you are seeing partial data.

Are there any blank values in your column? You can check whether the column is allowed to be null in SQL.

Get “not allowed for columns on the one side of a many-to-one relationship” errors 

You need to check the relationship between the tables in the Desktop Model view. Power BI will automatically create relationships for similar columns.

You may refer to Solved: Duplicate Value - Microsoft Power BI Community.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[FilipK]

Hey @v-kkf-msft ,

thanks for your response!

Yes, incremental refresh is activated indeed. I don't get why that should influence the behaviour. Is that than an internal PBI issue? Quering the view via SSMS reveals there is no duplicate at all.

In PBI Desktop I set RangeStart and RangeEnd to values that covers the timestamp that was mentioned to be a duplicate. In PBI Desktop I don't get that error.

There are no autmated added relations. I've checked that.

[v-kkf-msft]

Hi @FilipK ,

Power BI only updates the refresh range (partition), not any modification.

The data outside the refresh rage must be stable (unchanged).

In other words, if you have a row of data:

w_epochtime	date	value
a	1/1/2021	1

Then update the value of w_epochtime as a to 2 in SQL on 3/31/2021, then when you refresh on the service, powerBI will load the following two rows of data. Because 1/1/2021 is not included in the refresh range. This leads to duplicate value errors.

w_epochtime	date	value
a	1/1/2021	1
a	31/3/2021	2

To avoid the error, you have the following options.
1. Cancel incremental refresh.
2. Expand the refresh range and enable the option "Detect data changes" to reduce the number of refreshes.

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[FilipK · Accepted solution]

Thanks to your ideas @v-kkf-msft! I found out that incremental refresh I need to further look into i and I indeed I was succesful. My filter in DAX was not set correctly.

= Table.SelectRows(dbo_View_..., each [msgtimelocal] >= RangeStart and [msgtimelocal] <= RangeEnd)

Once I've changed it to 

 [msgtimelocal] > RangeStart and [msgtimelocal] <= RangeEnd

everything works fine.

[Standef]

Thanks @FilipK  Had the same problem and you solved it.

In the Native Query I was using "Between" instead of >= and <.
As the incremental refresh was not sending 1 query for all the historical data but several small queries of partitions and putting them back together, the between statement in SQL is including both days (start and end) and that's the reason I had an overlap and a double counting.