A Power BI report can refresh successfully and still show the wrong number. This often happens after a source change: an Excel file moves from a local folder to SharePoint, a column is renamed, a key changes format, or a new version of the workbook changes the grain of the data.
Do not start by rewriting the measure. First find the earliest point where the data stops matching expectations. Then repair that layer and test the report under the filters people actually use.
Find the first point where the numbers diverge
Choose one affected KPI and one fixed period. Write down its business definition, such as “posted orders excluding cancellations, by order date.” Capture the report result and an independently calculated source result using the same date range, status rules, currency, and rounding.
In Power Query, make a temporary diagnostic copy of the query and inspect the output after each applied step. Compare row count, distinct business keys, nulls, data types, and the KPI’s additive amount at these points:
- The raw source, immediately after navigation.
- After type conversion and renamed-column steps.
- After filters, merges, and expanded columns.
- The loaded fact table, before relationships and measures affect the visual.
If the raw source already differs, check that the report is reading the intended file, worksheet, table, folder, or SharePoint site. If the raw rows are right but the query output is wrong, the break is in a transformation. If the loaded fact is right but the visual is wrong, move to the model and DAX.
Keep the original query intact while diagnosing. Use a copy or a controlled branch so that refreshing the report does not replace a working production result during investigation.
Check Power Query after a source change
For a local Excel to SharePoint move, update the source and navigation steps deliberately. A local File.Contents path and a SharePoint connector do not return the same navigation structure. Confirm the site URL, document library, folder path, file name, and selected table or sheet. Avoid selecting a file only by a partial name if several versions can match.
Inspect every applied step after navigation. Common failures include:
- A
Changed Typestep that points to a renamed or removed column. - A
Removed ColumnsorReordered Columnsstep that assumes a fixed schema. - A merge whose key changed from number to text, gained leading zeros, or contains whitespace.
- A filter that excludes new categories or dates.
- An expanded nested table that now produces duplicate fact rows because the lookup key is no longer unique.
- A query that previews correctly under one credential but cannot refresh in the Power BI Service with its configured credentials.
Check query folding and privacy settings when the connector or transformation changes. They may affect performance or refresh behavior, but they do not establish that the resulting business numbers are correct. Reconcile rows and key totals independently.
For recurring files, standardize the incoming schema before combining files: require the business key and required columns, normalize types and whitespace, and surface a clear error when a required field is absent. Do not silently replace a missing amount or key with zero; that hides a source contract change.
Verify relationships and table grain
If the loaded fact rows and base amount match the source, inspect Model view. Confirm that dimension keys are unique, fact foreign keys have matches, relationships are active where expected, and filter direction follows the intended star schema.
A relationship can become ineffective after a source change even when its columns still look similar. For example, a product key may be numeric in one file and text in another. A merge can also duplicate fact rows if the lookup side now contains multiple records per key. Compare fact row count and distinct transaction count before and after merges, and check orphan keys separately.
Put the raw additive measure and the dimension field on a simple table visual. If the total is correct but the same value repeats on every category row, the category is probably not filtering the fact table. If category values add up to more than the total, look for many-to-many paths, duplicate dimension keys, or a measure that intentionally removes filters.
Prefer a clear star schema with one-way dimension-to-fact filtering. Use bidirectional filtering only when the model requires it and the behavior is understood. Do not repair a broken relationship by adding ALL or REMOVEFILTERS to a measure; that can make the visual look stable while discarding the user's selection.
Make measures respect slicers and totals
Start with a base measure that preserves the current filter context:
Net Sales =
SUM ( Sales[NetAmount] )
A derived ratio should use the same filtered base measures in its numerator and denominator:
Margin % =
DIVIDE ( [Gross Margin], [Net Sales] )
Do not average row-level percentages or category percentages to calculate a total percentage. The total should be recomputed from the filtered numerator and denominator. This pattern naturally responds to date, region, product, and other slicers as long as those dimensions have valid relationship paths to the fact table.
Some measures need different logic at detail rows and totals. For a distinct entity count, the total is usually a distinct count across all selected entities, not the sum of the visible group counts. For a non-additive allocation, calculate the total from the underlying inputs rather than adding displayed results. Use ISINSCOPE only when the business definition explicitly calls for different row and total behavior; document why and test both branches.
When a measure should ignore one particular slicer, remove only that dimension’s filter and preserve the rest. Avoid broad ALL ( Sales ) unless the measure is explicitly meant to ignore every filter on the fact table. Broad filter removal is a common reason a KPI stops responding to slicers after a quick fix.
Test the change as a small matrix
Use a known source extract or a saved set of expected results. Validate at least:
- The overall total with no report filters.
- One category, then two categories, and the grand total.
- A single date, a month, and a period spanning a year boundary if time logic is involved.
- A slicer combination that previously produced the wrong result.
- Blank, zero, duplicate-key, and newly added category cases where they apply.
For additive values, visible group values should reconcile to the total when the groups partition the data. For ratios and distinct counts, do not require the visible rows to add up; compare the total with its proper source calculation. Capture both the expected and actual results so the check can be repeated after the next source update.
Validate an Excel to SharePoint migration
Before switching the production query, compare the local workbook and SharePoint file for the same snapshot. Confirm that they contain the same rows, headers, key uniqueness, date coverage, and totals. Check whether the SharePoint file is the intended version and whether a temporary or archived copy could be selected by the query.
After updating credentials and the source, refresh in Desktop and then run a refresh in the Power BI Service. Desktop success alone does not prove that the gateway, cloud connection, privacy configuration, or service credentials are ready. Review refresh history and confirm that the refreshed timestamp advances. Then repeat the KPI reconciliation against the same source snapshot.
If the report uses incremental refresh, verify the partition window and confirm that historical partitions are not stale after the source change. A successful refresh of only the recent partition does not guarantee that older data was reloaded correctly.
Release checklist
Before publishing the repair, confirm that:
- The source location and selected object are explicit and point to the intended file or table.
- Required columns, data types, business keys, and expected grain are checked.
- Query row counts, distinct keys, nulls, and totals reconcile at the right stages.
- Relationships filter the fact table as intended, with no unexpected duplicate or orphan keys.
- Measures preserve relevant slicers and calculate totals according to their business definitions.
- The report passes the saved validation cases in Desktop and the Service refresh succeeds.
- The refresh timestamp and history show current data, and the report owner knows what to monitor.
The durable fix is usually a small one: repair the broken query step, restore the key or relationship, or correct the measure’s filter logic. The important part is finding the first layer that diverges and keeping a repeatable check for it. That turns “the dashboard looks wrong” into a specific defect that can be fixed and verified.