Incremental refresh - delete source data

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

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.