Forum Discussion
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 within the desktop, I'm receiving duplicate rows but the 'modified' and 'created' dates are different. One shows 30/01/2022 the other is 01/02/2022.
My incremental refresh is configured to store data for the last two years, and refresh rows for the last three months based on 'created' date, and automatically detect changes is enabled and set to watch 'modified' date.
I know if I do a manual refresh, it'll likely fix the issue as it'll do a normal load from the report, but I don't want to do this, I'd rather incremental worked as I would hope.
When I query the row within the service, only the one row is returned - I add this step after the 'canary load' step at the end of all the steps on the workspace.
Can anyone assist, or is this expected behaviour?
Many thanks,
Dayna
21 Replies
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.
Thank you - this was causing my issue, I never would have thought to look for this.
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.
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.
This worked for me, thank you!
Hello,
I'm not seeing duplicates in the service though, only when consumed within the report. Is this expected behaviour for the incremental refresh?
Thanks,
DaynaCan you confirm that your M query is shaped according to the rules I mentioned?
I've not configured this other than in the UI within the PowerBI service, it has automatically created this:
Table.SelectRows(#"Changed column type 2", each DateTime.From([pro2created]) >= RangeStart and DateTime.From([pro2created]) < RangeEnd)
Hi Dyana,
My solution for this case was make a buffer for the sorted set by the modified date (descending), and then remove the duplicates.
...
#"Linhas classificadas" = Table.Buffer ( Table.Sort(#"Colunas removidas", {{"Id", Order.Ascending}, {"Modificado", Order.Descending}})),
#"Duplicatas removidas" = Table.Distinct(#"Linhas classificadas", {"Id"}) ...Obs.: without applying the buffer, power bi does not guarantee that the first row of sorting will be kept.Hi I have a similar issue
we have last updated date field across which we are enabling incremental refresh
I followed as per MS documentation
currently we enabled refresh for last 15 days.
so what's happening is it is updating and refreshing the data for these 15 days window.
now problem here is if I have a record which last updated 4 months back and is updated today again, it is not removing the 4 months back row and is getting added resulting in duplicateshow can we adress this issue ??
That field is not suitable for incremental refresh. Use an immutable field like Create Date. You can theoretically use the Last Modified Date for change tracking but ideally you should know your data and should know which partitions to refresh when.
