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</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?

 

 

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.

5 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    5 years ago

    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'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.