Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Icon for Impactful Individual rankImpactful Individual
14 days ago
Solved

Incremental 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 increm...
Need Help
power bi
  • jubinsoni's avatar
    jubinsoni
    14 days ago

    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.

ShivekMaharaj's avatar
ShivekMaharaj
Icon for Community Champion rankCommunity Champion
8 days ago

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 timestamp

works much better with incremental refresh than physically removing the row immediately.

Your Power Query can then exclude:

IsDeleted = 1

and 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 partition

I 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 days

Then 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 refreshed

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