Forum Discussion

FilipK's avatar
FilipK
Resolver I
5 years ago
Solved

Duplicate value error while refresh without having a duplicate

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</...
Need Help
  • FilipK's avatar
    FilipK
    5 years ago

    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.

FilipK's avatar
FilipK
Resolver I
5 years ago

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's avatar
v-kkf-msft
Community Support
5 years ago

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's avatar
    FilipK
    Resolver I
    5 years ago

    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's avatar
      Standef
      Helper I
      3 years ago

      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.