Forum Discussion
Impactful IndividualIncremental refresh - delete source data
i am using incremental refresh in Power BI with SQL Server as source however records in the source table may get deleted anytime , in this case, the historical partition ( or archived data) of incremental refresh would continue to show entries of those records that have been deleted until and unless that historical partition is refreshed , i do not want to refresh the whole historical data or partition again and again which could be time consuming task.
can this issue be resolved by creating delete flag (Yes/No) in the source table used for incremental refresh? or by using detect data change option available in incremental refresh ? if the answer is yes then i would like to test these options for confirmation and would like to know the approach for testing these options , what should be the range of historical data retention i should consider and also the period of incremental refresh for testing purpose?
Hi powerbiexpert22,
Correction (edited): in the first version of this reply I implied that "Detect data changes" would also re-check historical partitions. It does not: it only applies to partitions inside the incremental refresh window. Historical partitions are never re-read by a policy refresh. Updated answer below.
Hard deletes are the one thing incremental refresh cannot see on its own: a row that no longer exists cannot change any date column. So the first step is a soft delete in the source that updates the row instead of removing it:
UPDATE dbo.Sales SET IsDeleted = 1, ModifiedAt = SYSUTCDATETIME() WHERE SalesId = @id;Then it depends on where the deleted row sits:
Row date inside the refresh window (e.g. last 3 days) -> handled by the normal scheduled refresh (Detect data changes on ModifiedAt just avoids reprocessing unchanged partitions in that window) Row date in the historical/archive range -> NOT picked up by the policy; refresh that partition explicitly:POST https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/datasets/{datasetId}/refreshes { "type": "full", "applyRefreshPolicy": false, "objects": [ { "table": "Sales", "partition": "2026Q309" } ] }Exclude IsDeleted = 1 rows in the model (Power Query filter or measures). A small scheduled job can find which months had deletes since the last run (max ModifiedAt per month in the source) and refresh only those partitions with the call above, so you never reprocess the full history.
For testing, use a short policy (archive 2 months, refresh last 3 days), soft-delete one row from last month, run the targeted partition refresh, and check which partitions were processed:
EVALUATE SELECTCOLUMNS ( INFO.PARTITIONS (), "Partition", [Name], "RefreshedTime", [RefreshedTime] )So my overall approach would be:
Soft delete in source + update ModifiedAt -> Recent rows: normal incremental refresh -> Historical rows: targeted partition refresh (API/XMLA, applyRefreshPolicy false) -> Exclude IsDeleted rows in the model; verify with INFO.PARTITIONSAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
4 Replies
v-kathullacCommunity Support
Hi powerbiexpert22 ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya- v-kathullac
Hi powerbiexpert22 ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya 
jubinsoniMemorable Member
Hi powerbiexpert22,
Correction (edited): in the first version of this reply I implied that "Detect data changes" would also re-check historical partitions. It does not: it only applies to partitions inside the incremental refresh window. Historical partitions are never re-read by a policy refresh. Updated answer below.
Hard deletes are the one thing incremental refresh cannot see on its own: a row that no longer exists cannot change any date column. So the first step is a soft delete in the source that updates the row instead of removing it:
UPDATE dbo.Sales SET IsDeleted = 1, ModifiedAt = SYSUTCDATETIME() WHERE SalesId = @id;Then it depends on where the deleted row sits:
Row date inside the refresh window (e.g. last 3 days) -> handled by the normal scheduled refresh (Detect data changes on ModifiedAt just avoids reprocessing unchanged partitions in that window) Row date in the historical/archive range -> NOT picked up by the policy; refresh that partition explicitly:POST https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/datasets/{datasetId}/refreshes { "type": "full", "applyRefreshPolicy": false, "objects": [ { "table": "Sales", "partition": "2026Q309" } ] }Exclude IsDeleted = 1 rows in the model (Power Query filter or measures). A small scheduled job can find which months had deletes since the last run (max ModifiedAt per month in the source) and refresh only those partitions with the call above, so you never reprocess the full history.
For testing, use a short policy (archive 2 months, refresh last 3 days), soft-delete one row from last month, run the targeted partition refresh, and check which partitions were processed:
EVALUATE SELECTCOLUMNS ( INFO.PARTITIONS (), "Partition", [Name], "RefreshedTime", [RefreshedTime] )So my overall approach would be:
Soft delete in source + update ModifiedAt -> Recent rows: normal incremental refresh -> Historical rows: targeted partition refresh (API/XMLA, applyRefreshPolicy false) -> Exclude IsDeleted rows in the model; verify with INFO.PARTITIONSAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
- ShivekMaharaj
Community Champion
Hi powerbiexpert22,
I would separate two different cases here: recent deletes and historical deletes.
The important limitation is that Detect data changes is not a hard-delete detector.
Microsoft’s incremental refresh configuration guidance explicitly notes that deleted rows are not detected by change detection. It only works with a soft-delete pattern where the source row still exists and the datetime column used for change detection is updated.
So a source design such as:
IsDeleted = 1 ModifiedDate = current timestampworks much better with incremental refresh than physically removing the row immediately.
Your Power Query can then exclude:
IsDeleted = 1and the next refresh of that period removes the row from the semantic model.
The second case is when the deleted record is already in a historical partition.
Microsoft’s incremental refresh overview explains that rows outside the rolling refresh period become historical and are no longer refreshed during the normal policy refresh.
So if a row from, for example, 18 months ago is deleted in SQL Server, a normal 6-month incremental refresh will not revisit that historical partition and will therefore not remove it from the model.
For those cases, I would use a targeted historical refresh rather than refreshing the entire model.
Microsoft’s advanced incremental refresh guidance supports selectively refreshing historical partitions through XMLA, and the enhanced refresh REST API can also target specific table partitions.
So the production pattern I would normally use is:
recent data → soft delete + ModifiedDate updated → normal incremental refresh picks it up historical correction/delete → identify the affected period → explicitly refresh that historical partitionI would not rely on Detect data changes alone for hard deletes.
For testing, you can definitely shorten the policy temporarily, for example:
archive = 2 months refresh = 3 daysThen soft-delete one recent record and one older historical record and confirm that:
recent deleted row → disappears after normal incremental refresh historical deleted row → remains until the historical partition is explicitly refreshedThat gives you a very clear demonstration of how the policy behaves.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
