Bring Historical Corrections Into Power BI Incremental Refresh

This synthetic example shows how an after-the-fact correction can be missed by routine Power BI refresh processing. A sales order dated 2026-08-15 is corrected on 2026-09-10, yet a scheduled refresh with a seven-day window leaves the report unchanged. Three layers must agree: source truth, the selected model partitions, and the published report output. The playbook defines a correction contract for grain, keys, partition dates, and late-update allowances; traces each change through the incremental refresh policy; applies a supported recovery route, such as selective XMLA backfill on an eligible capacity; and reconciles business keys and measures before a publish, restate, or hold decision. The goal is not a green refresh status. It is evidence that each intended business-row correction reached the model and the report.

Write a Historical Correction Contract

Before addressing a missing update, formalize a correction contract that ties business intent to technical settings. Document the fact table's grain and business key (for example, OrderID), the partition date (for example, OrderDate), and the last-modified timestamp or change marker, if one exists. Record the source-data owner and the authority permitted to approve corrections. Define the allowed reporting delay, such as up to 10 business days after financial close, separately from the refresh window. State whether late updates and deletions are permitted and, if so, which types.

Link the contract to semantic-model documentation and governed semantic definitions so every owner agrees on grain and meaning. Do not assume that the incremental refresh window matches the correction window. A model might refresh seven days of data while the business accepts corrections for 30 days. Compare those horizons side by side and approve any gap explicitly.

Require the source owner to provide evidence of late updates and deletions for each reporting cycle, such as rows with ModifiedDate later than the previous refresh or explicit tombstones. The BI team then treats this immutable change ledger as the acceptance baseline rather than reconstructing history after publication.

Inventory the Policy and the Available Recovery Tools

Document every relevant detail of the incremental refresh setup and execution environment:

• Model/table identity: Name and path of the dataset, table, and partition key.

• Retention vs refresh periods: e.g. “Store 5 years, refresh 7 days.” Include whether ‘complete days’ setting is on.

• RangeStart/End parameters: Confirm their names, data types (DateTime recommended), and how they map to the event date.

• Data source details: Connection string, gateway (on-premises or cloud), credentials used, and privacy levels.

• Workspace and capacity: Record the workspace license or capacity, whether XMLA Read/Write is enabled, and the permissions held by the recovery operator. Do not infer advanced partition access from the policy alone.

• Dataset size & version: Desktop file size, service dataset size, and Power BI Desktop version.

• Load behavior: Is a dataflow or datamart involved upstream? (Those patterns differ.)

• Affected downstream reports: Which reports/dashboards rely on this dataset.

Record these items in a configuration table. The values below are synthetic examples and must be replaced with the examined environment:

Broader Power BI platform selection context can explain why the environment exists, but it should not replace the operational inventory. Keep this review focused on the semantic model, policy, capacity, gateway, source access, and recovery permissions actually under examination.

If the workspace or capacity does not provide a read/write XMLA endpoint, or the operator lacks permission, advanced partition operations through SSMS or Tabular Editor are unavailable. Record that as a recovery constraint. Unsupported tools are a hold condition, not a reason to invent an API workaround.

Assign an owner before any recovery action. If the plan includes an XMLA partition refresh, the semantic-model owner or BI lead must approve the object scope. Power BI capacity migration checks provide useful surrounding context for validating administrative roles, but every TMSL or manual backfill still needs its own reviewed target list and sign-off.

Make Time Boundaries Explicit and Testable

Construct a small test of the partition filter logic using an explicit M expression, not the UI dialog. For example, if RangeStart and RangeEnd are your parameters, the documentation shows writing two Table.SelectRows steps on the date column:

#"Filtered Rows"  = Table.SelectRows(Source, each [OrderDate] >= RangeStart),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [OrderDate] <  RangeEnd)

Use Microsoft's incremental refresh configuration guidance to verify the half-open interval: make only one boundary inclusive, such as OrderDate >= RangeStart and OrderDate < RangeEnd. This prevents a boundary row from entering adjacent partitions. The documentation is not fully consistent about the Desktop UI path: the overview says the standard Custom Filter UI cannot reference the parameters, while the configuration article describes a parameter-based Custom Filter route. For this lab, inspect the M expression and the native source query rather than assuming that a particular Desktop build resolves the discrepancy.

Record the effective service-generated interval and its time-zone basis rather than assuming that it matches the Desktop preview. In a synthetic test, the run record might show RangeEnd at 2026-09-18 00:00 UTC and RangeStart seven days earlier; complete-day settings and policy granularity can change the actual boundaries. Store the observed values with the test evidence.

Use a Single Inclusive Boundary

Make sure your filter logic assigns each row to exactly one interval. Test this by inserting boundary-case rows. For example, if your refresh windows are daily, load one row with OrderDate = 2023-01-07 00:00 and another with OrderDate = 2023-01-08 00:00 (midnight boundary). The first should fall into the prior partition (>= RangeStart of the old window) and the second into the next. Use a lookup by business key to confirm: no key should appear in two partitions.

This verifies the documented half-open pattern and prevents double counting at adjacent daily, weekly, or monthly boundaries.

Distinguish Event Time, Modification Time and Refresh Time

Assign each timestamp one role. OrderDate is event time and determines the partition. LastModified is change time and can be polled by detect-data-changes logic. The refresh execution timestamp is operational evidence only. A row whose OrderDate is inside the current refresh range is eligible because of event time; LastModified does not move it to another partition. Conversely, a recent LastModified value does not move an older event-date row into the current partition.

Add a time-zone boundary case. When the partition key is date-only but the source event is timestamped, derive the expected date with the documented business time zone before comparing it with UTC service boundaries. Store that derivation in the test record.

Map Every Source Change to the Partition It Needs

Create a change ledger listing every type of source update and what it implies. For example:

Each ledger entry identifies the old and new partition dates and the exact intervals that must be revisited. Do not infer historical reach from the refresh job timestamp. When a row moves across the partition key, treat removal from the former interval and insertion into the new interval as separate obligations. Established data warehouse management practices provide useful upstream context; here, the same discipline is applied to Power BI partition recovery.

Always list all affected intervals. For C2 above, even though ChangedAt is recent, the event date is in a month already archived. So we know we must refresh that historical partition. Never assume that because “refresh ran yesterday” that it automatically caught a change from two weeks ago. Our ledger keeps track of the intended coverage.

Test What Change Detection Sees and Misses

If using the DetectDataChanges feature, verify what it actually detects. Recall that it is a filtering optimization, not a comprehensive change data capture. It will only cause a partition to be refreshed if the maximum value of the chosen change column in that partition has increased since last run. Test this explicitly.

Inspect the Maximum Change Marker in Scope

Suppose LastModified is the change marker. Detect data changes compares the maximum marker value for a period with the value recorded previously. To exercise a miss, first let another row establish a later maximum, then correct an older row without advancing that maximum. For example, if the stored maximum for the September 10-16 partition is 2026-09-16 18:00, correct a different row and set its LastModified to 2026-09-15 09:00. The partition contains a changed row, but its maximum marker is unchanged, so the optimization can skip reloading it. Verify the poll and source queries. The expected observation is no data retrieval for that partition even though the correction ledger records a change.

Separate Hard Deletes from a Defined Soft-Delete Contract

Test physical deletion and soft deletion as separate contracts. A hard delete removes the source row and therefore removes the evidence that a general maximum-value detector could poll. A soft-delete design retains a tombstone and updates a marker, but the loading query and the detector must be defined so the detector can still observe that marker.

• Hard delete: Remove a row from the source table. Detect data changes has no tombstone to observe, so the partition can be skipped and the deleted row can remain in the model. If that partition is explicitly refreshed, the row should disappear because the source query returns one fewer row.

• Soft delete: Retain the row as a tombstone, set IsDeleted to true, and advance its change marker. Define the detector so it can observe the tombstone, while the model-loading query excludes deleted rows. Test both paths rather than assuming that one filter satisfies both requirements.

Treat the M expression below as the model-loading query. It excludes soft-deleted rows from imported data. If change detection is enabled, separately verify that its polling expression can still observe the tombstone marker before the row is excluded:

Filtered = Table.SelectRows(Source, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd and [IsDeleted] = false)

The hard-delete and soft-delete tests therefore require different evidence. An explicit historical refresh can remove either row from the model, but the change detector can react only to evidence that remains queryable.

Reproduce the Missing Correction Without Changing the Grain

Create the following disposable table only in an isolated test database. The SQL is illustrative and state-changing; replace object names and dates before execution:

CREATE TABLE FactSales (
    OrderID INT,
    OrderDate DATE,
    Amount DECIMAL(10,2),
    LastModified DATETIME,
    IsDeleted BIT
);

-- Base data: some orders in the past week
INSERT INTO FactSales VALUES
(1001, '2026-09-10', 500.00, '2026-09-10 08:00', 0),
(1002, '2026-09-08', 200.00, '2026-09-08 09:00', 0),
(1003, '2026-09-09', 300.00, '2026-09-09 10:00', 0);

For this synthetic lab, configure a two-week retention period and a seven-day refresh period. Capture the initial load, then apply the following mutations in the isolated fixture:

-- Late update: Order 1002 amount corrected on Sep 12 (new LastModified)
UPDATE FactSales
SET Amount = 250.00, LastModified = '2026-09-12 07:00'
WHERE OrderID = 1002;

-- Hard delete: Remove Order 1003 on Sep 13
DELETE FROM FactSales WHERE OrderID = 1003;

-- Date move: Change Order 1001 to previous day (should span partitions)
UPDATE FactSales
SET OrderDate = '2026-09-09', Amount = 520.00, LastModified = '2026-09-12 08:30'
WHERE OrderID = 1001;

Before applying any mutation, capture the initial import and list every business key by partition date:

After applying the source changes and running the intended refresh path, assert the following expected outcomes:

• Order 1002’s new amount should appear (reflecting late correction).

• Order 1003 should be gone.

• Order 1001 should no longer appear on Sep 10; instead, its updated amount 520.00 shows on Sep 09.

Capture the refresh log, partition metadata, and source-side query evidence. Use the following expected reconciliation table:

Check that total sums of the table reflect the expected corrected values. (Do not hide missing rows by aggregation.) Ensure no row appears twice. In this lab, all changes except 1001 moved entirely without replication; depending on policy, moving could require refreshing both old and new partition to avoid duplicates.

This synthetic fixture is designed to show how each change should manifest without changing the fact grain. Keeping the grain at OrderID avoids introducing a merge-cardinality problem. Treat the table values as expected assertions, not observed results, until the lab has been executed in an isolated environment. A missing or duplicated key is a visible failure, not a successful refresh.

Select a Supported Historical Recovery Route

If a change is missing, you must decide how to refresh the affected partition(s) or the entire model. Always start by listing exactly which partitions (by name or date range) need reloading. Then choose from supported methods. Treat this as an operations procedure, not a hack.

Target the Required Partitions Through XMLA

When the examined workspace and capacity provide a read/write XMLA endpoint, inspect the live model through an approved client such as SQL Server Management Studio or Tabular Editor. Do not derive partition names from dates. Copy the actual database, table, and partition identifiers into a reviewed nonproduction TMSL request:

{
  "refresh": {
    "type": "full",
    "applyRefreshPolicy": false,
    "objects": [
      {
        "database": "SalesModel",
        "table": "FactSales",
        "partition": "FactSales_Month202609"
      }
    ]
  }
}

The TMSL refresh request is a nonproduction example. Under Microsoft's advanced incremental refresh guidance, object selection defines the scope; applyRefreshPolicy=false only prevents the policy from redefining that request. It does not make a table-wide command selective by itself. Inspect the live model for the exact database, table, and partition names, authorize the object list independently, and verify that only those partitions changed. Run the state-changing command in an isolated environment first and record the rollback or cleanup path.

Handle Workspaces Without the Advanced Path

If the advanced XMLA path is unavailable or unauthorized, choose a separate, approved operational route. There is no one-click equivalent to a selectively targeted partition refresh:

• Correction-window redesign: Temporarily widen the ordinary refresh period only after estimating source load and capacity impact. If observed corrections arrive within 14 days, a reviewed 14-day refresh period can revisit them, but it also changes normal processing scope and must be scheduled and approved.

• Approved rebuild or broader reprocessing: Reload the required source history and republish only with a preserved model definition, report-binding plan, permission review, and rollback. Treat this as a disruptive recovery, not a substitute for targeted partition management.

In either case, check source data retention. If the source no longer has the old “pre-correction” values (e.g. deleted logs), you may not be able to restore them. Always confirm that the source is “replayable” for that period. If not, the only option may be to hold the report and detail the unfixable discrepancies.

Every recovery command needs an owner-approved target list. Do not connect unsupported scripts directly to the service model store. When no documented route can reach the affected period, stop execution and issue a clear hold notice to stakeholders.

Reconcile Keys, Deletions and Measures After Backfill

After any recovery action, rigorously verify the results. First, compare the set of business keys in the model against the expected keys from the source ledger, for each affected partition. For example, produce a table like:

Use this to check: are all expected updates present? Are any extra rows lingering? If a row was moved across a partition key (as in the table above), ensure it does not appear twice or in the wrong place. Confirm that deletions are fully absent. Check for duplicates of the key in any single partition (which would violate the assumed 1:N relationship rule).

Next, recalculate the model totals. For additive measures such as total sales, each affected interval should match the expected source sum. An overall total that happens to match is not proof of row correctness; reconcile business keys first. If the pre-backfill monthly total was short by $20, the post-backfill discrepancy should be $0.

If any measure changed unexpectedly (e.g. count of orders dropped by 1 due to a deletion), verify that in the ledger too. For composite calculations (ratios, running totals), manually recalc for a sanity check on a small sample.

Document each assertion as an expected or observed result. A synthetic example might state: “Seven rows in the September 8 interval match the expected keys and amounts, no duplicates remain, and total sales equal $7,250 rather than $7,230.” Do not promote that statement to an execution result until the evidence exists. Any unresolved key, deletion, or amount difference keeps the report on hold.

Prove the Source Work Is Bounded

When you refresh a partition (or run the change-detection queries), it’s informative to capture how much work was done. For example, turn on SQL Profiler or Extended Events on the source database. Verify that for each partition query, the WHERE clause matched exactly the intended date range. Note how many rows were scanned versus returned. If query folding is working fully, the source should only return the needed rows. If partial folding occurred, maybe more data was pulled.

Use a synthetic observation template like the following, then replace every value with evidence from the executed run:

DirectQuery and hybrid real-time patterns are outside this lab. In a separate design that uses them, verify their source predicates independently rather than treating them as interchangeable with Import-mode partitions. Microsoft's Power Query folding guidance distinguishes full, partial, and absent folding. Inspect the generated source query and source-side workload; a correct preview does not prove that the service refresh performed bounded work.

Record representative runtime, rows scanned and returned, gateway behavior, and model resource use. Historical processing may take longer than routine incremental work, but the acceptable duration is environment-specific. Split or reschedule the recovery when it exceeds the approved maintenance or capacity boundary.

Do not claim that the method universally improves refresh performance. The evidence must show that the intended date predicates reached the source and that only the approved slice was processed. Extra scans, a missing index, or a query timeout are separate failures to diagnose with the incremental refresh troubleshooting guidance before publication.

Protect the Next Scheduled Refresh

Once corrections are applied, ensure the normal incremental refresh still proceeds correctly and doesn’t undo your fix. Check the scheduled refresh job history. If you used XMLA, the dataset in the service now contains the corrected data. On the next scheduled refresh, the service will still apply the policy normally. However, beware of overlapping commands: if you ran manual refreshes via XMLA, turn off (or carefully coordinate) the scheduled refresh until after they complete to avoid conflicts.

Also, note that publishing a new PBIX from Desktop will reinitialize the partitions (effectively dropping history). So do not republish the desktop version unless you intend to wipe and reload history. Keep a record of the amended model definition in source control or version history to ensure any rebuild uses the corrected query logic. If you must distribute a.pbix, mention that it no longer contains the historical changes that were applied only in the service.

Finally, verify that the regular refresh runs at the next scheduled time and includes any late data up to that point (including the corrections). If your correction-window policy relied on timing, update any calendar. For example, if you performed a backfill on day 15, confirm that the day 22 refresh still covers through day 22 as usual. Essentially, ensure the incremental window “rolls forward” from where it should, not from the old, uncorrected state.

This way, the system is left in a consistent state, and future runs become additional evidence of stability rather than an uncontrolled variable.

Publish a Restatement or Keep the Report on Hold

Deciding whether to publish the report after recovery depends on impact. Prepare a brief report or dashboard for stakeholders summarizing what changed. Include: which partitions were affected (e.g. “Added 3 orders in 2026-09-08 partition, removed 1 in 2026-09-01, updated 2 amounts”), how key metrics shifted, and attach the reconciliation proof (like the tables above). Show a side-by-side of the key output metrics before vs. after the fix.

A simple decision table can help:

A matching total can hide compensating row errors. Record each restatement under self-service BI governance, including the corrected periods, affected measures, approval, evidence, and consumer notification. Any unresolved difference remains visible even when the refresh service reports success.

If publishing, include a note (in the report or release notes) summarizing the corrections (“On 2026-09-18, historical data for Sep-2026 was updated to reflect late entries; see audit log for details”). If holding, clearly state why (“Reconciliation identified missing records in June 2026; report currently withheld pending data fix”). This ensures transparency. Finally, archive the “old” version of the report dataset (if possible) before it is replaced, to preserve evidence of what users saw prior to correction.

Adopt a Correction-Aware Operating Routine

To prevent surprises, embed this process into regular operations. For example, establish a 30-day cycle like:

• Day 1: Data steward confirms no further edits are expected for closed period T (aligns with business close dates).

• Days 2–5: Run the test queries/fixtures on the preceding period, following the steps above to detect any missed changes.

• Day 6: Perform any approved backfills (via XMLA or full refresh) and reconciliation.

• Day 7: Finalize report/restatement decision and freeze new data for publication.

• Weekly afterward: Track metadata (max LastModified) and compare to source audits; adjust future data contract terms if late deliveries regularly slip.

Assign the source owner to deliver late-change evidence, the BI owner to execute the fixture and reconciliation, and the reporting owner to approve publication or restatement. Tie the correction window to observed business processes. For example, a financial close date can inform the review schedule, but it does not create a universal number of refresh days.

Connect the Exercise to Business Intelligence Foundations

This exercise reinforces data modeling, extract-transform-load logic, data-quality checks, reporting, data warehousing, SQL for BI, and KPI monitoring. Readers building those foundations can review the Refonte Learning Business Intelligence Program. The public programme page lists three months and 8-10 hours per week; Power BI incremental refresh, XMLA administration, TMSL, and this specialist correction lab are not presented here as confirmed curriculum coverage.

Treat Refresh Success as One Piece of Evidence

A successful refresh status is only one piece of evidence. Publication requires agreement among the source-change ledger, the affected partitions, the reconciled business keys and measures, and the report output. When those layers agree, the evidence supports publication or an explicit restatement. When they do not, the unresolved correction remains visible and the report stays on hold.