Forum Discussion

Dayna's avatar
Dayna
Helper V
4 years ago

Incremental refresh causing duplicate rows

Hello,   I've implemented incremental refreshes on one of our fact tables, called 'Transactions'.   I can see on a given day there's been a change to some rows, and when this dataflow is consumed...
Need Help
lbendlin's avatar
lbendlin
Super User
4 years ago

Duplication can only happen when partitions store overlapping date ranges. You must make sure that of the RangeStart and RangeEnd parameters one is inclusive and one is exclusive

 

For example 

 

Date >=RangeStart and Date < RangeEnd

Date >RangeStart and Date <=RangeEnd

 

are both possible (but not at the same time!)

 

However

 

Date >=RangeStart and Date <=RangeEnd

 

will lead to duplicate rows and must be avoided.

  • DanielAus's avatar
    DanielAus
    New Member
    3 years ago

    Thank you - this was causing my issue, I never would have thought to look for this.

  • Jeanxyz's avatar
    Jeanxyz
    Power Participant
    3 years ago

    I have a similar issue. The data refreshing  failed because Power BI service identified duplicate Issue IDs (which is not allowed in data model because issue ID is my primary key column). When I checked the source data (Jira system), there is no dulicate I have set up the filter query as below:

    Last_Update_Date >=RangeStart and Last_Update_Date < RangeEnd

     

    My guess is during the incremental data refreshing, someone has updated an issue in Jira system causing the Last_Update_Date to be changed during the incremental refreshing process . As a result, this issue is included both in archive data set and refresh data set, causing the same issue to be loaded twice. 

     

    Not sure if this is the root cause and if there is a solution to the issue.

     

    lbendlin 

     

    • lbendlin's avatar
      lbendlin
      Super User
      3 years ago

      This scenario is described in the documentation. Keep in mind that Incremental Refresh expects immutable data.  If your data changes after the fact (which would technically require a differential refresh) then you need to supplement the incremental refresh with an occasional Full refresh of all partitions.

      • Jeanxyz's avatar
        Jeanxyz
        Power Participant
        3 years ago

        I read the documentations, indeed I need to enable 'get the latest data with direct query' in refresh setup page. This is not practical because 1) I will need to update my PB license to a premium per user one and the same for all users who need access to the Power BI report on service, 2) I need to change the storage mode of relevant tables into 'direct query' mode, this also means I need to rewrite all the DAX measures from scratch. 

         

        I'm wondering if there is any other solution. My only need is to shorten the data refreshing cycle from 25 minutes to 5-10 minutes. I'm connecting Power BI to Jira data using a odata connector. 

  • YuriMN's avatar
    YuriMN
    New Member
    2 years ago

    This worked for me, thank you!

  • debdeep's avatar
    debdeep
    New Member
    1 year ago

    Great solution, worked for me. Bookmarked this page if I ever forget it.