Data Validation & Testing in Power BI: The QA Process That Prevents 'The Numbers Don't Match'

Introduction

"The numbers don't match."

Four words that end careers, destroy trust, and send analysts into spiralling debug sessions that consume entire weekends.

Every Power BI developer hears these words. The question is whether they hear them from a stakeholder in a board meeting (catastrophic) or from their own validation process before the report is published (professional).

Validation is not optional. It's not something you do "if you have time." It's a systematic process that sits between building the report and publishing it. Skip it and you're gambling with your credibility.

---

Core Concept: Trust Is Built on Reconciliation

Reconciliation means comparing your Power BI output against a known-good source - and proving they match.

The process:

1. Identify a source of truth (the system of record - ERP, billing system, accounting ledger)
2. Select a reconciliation metric (total revenue, transaction count, unique customers)
3. Query the source of truth for that metric, with a specific date range and filter
4. Build the same calculation in your Power BI model with the same date range and filter
5. Compare. If they match: validated. If they don't: investigate.

The investigation always reveals one of five root causes:

• Missing data - some records weren't imported (filter too aggressive, join type wrong)
• Duplicate data - some records were imported twice (append instead of replace, bad join)
• Wrong aggregation - SUM where DISTINCTCOUNT was needed, or vice versa
• Type mismatch - dates parsed incorrectly, numbers stored as text
• Filter mismatch - the source query excluded cancelled orders; the Power BI model included them

---

Real-World Application

A SaaS company's CFO flagged that Power BI showed monthly recurring revenue (MRR) as $1.2M while their billing system showed $1.15M. The difference: $50,000. Enough to question every metric in the dashboard.

The root cause: a join in Power Query between Subscriptions and Customers was using a Left Outer Join that matched some subscriptions to multiple customer records (because one customer had two entries with the same email but different IDs). The join created duplicate subscription rows, each contributing to the MRR sum.

Fix: deduplicate the Customers table on email before the join. MRR matched to the dollar.

Time to find: 3 hours.
Time the discrepancy went unnoticed before someone flagged it: 6 weeks.
Cost of those 6 weeks: the CFO never fully trusted the dashboard again.

---

Technical Deep Dive: The Validation Checklist

Level 1 - Row Count Validation

-- Source
SELECT COUNT(*) FROM orders WHERE order_date >= '2025-01-01'

-- Power BI
Row Count = COUNTROWS(FactOrders)

If these don't match, you have a data loading problem. Stop here until they do.

Level 2 - Aggregate Validation

-- Source
SELECT SUM(amount) FROM orders WHERE order_date >= '2025-01-01'

-- Power BI
Total Amount = SUM(FactOrders[Amount])

Match row counts but aggregates differ? Check for type conversion errors (currency symbol stripping, decimal precision) or duplicate rows that have the same key but different amounts.

Level 3 - Dimensional Validation

-- Source
SELECT region, SUM(amount) FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY region
ORDER BY region

Compare against a Power BI matrix: Region on rows, Total Amount as value. If national totals match but regional breakdowns don't, you have a relationship or foreign key mapping problem.

Level 4 - Edge Case Validation

Test with extreme filters:

• A single day (does a daily filter produce the correct daily total?)
• A single customer (does filtering to one customer show their complete history?)
• The earliest and latest dates in the dataset (boundary conditions)
• Null/empty values (are they handled or do they silently disappear?)

---

Building Validation Measures

Create a dedicated _Validation measure group that lives in the model permanently:

V_RowCount = COUNTROWS(FactSales)
V_TotalRevenue = SUM(FactSales[Revenue])
V_UniqueCustomers = DISTINCTCOUNT(FactSales[CustomerID])
V_MinDate = MIN(FactSales[OrderDate])
V_MaxDate = MAX(FactSales[OrderDate])
V_NullAmounts = COUNTROWS(FILTER(FactSales, ISBLANK(FactSales[Revenue])))

Put these on a hidden "Validation" page in your report. Check them after every refresh. If V_RowCount changes unexpectedly, the data pipeline changed - investigate before publishing.

---

Common Mistakes

Validating only the grand total. A grand total can match even when regional breakdowns are wrong - if errors in different regions cancel each other out. Always validate at least one level of dimensional breakdown.

Validating after visuals are built. If you discover a data problem after the report has 15 pages of visuals, the rework is massive. Validate immediately after the data model is built, before creating a single visual.

Using Power BI as its own source of truth. "The Power BI number must be right because it came from Power BI" is circular reasoning. Always reconcile against an independent source. If no independent source exists, reconcile against a manual calculation on raw export data.

Not documenting validation results. A stakeholder will eventually ask "how do we know these numbers are correct?" Having a documented validation log - with source queries, Power BI results, and match/mismatch status - is the difference between "we checked" and "we think so."

---

Pro Tips / Industry Insight

Automated validation with DAX error flags:

Data Quality Flag =
IF(
    [V_RowCount] < 1000
    || [V_NullAmounts] > [V_RowCount] * 0.05
    || [V_MaxDate] < TODAY() - 3,
    "⚠ CHECK DATA",
    "✓ OK"
)

Display this on the validation page or as a card on the main dashboard. It gives an at-a-glance confidence signal.

For production environments: implement a "circuit breaker" - a conditional that prevents the report from rendering if validation thresholds are breached. This can be done with a card visual that shows a warning message when [Data Quality Flag] indicates a problem, overlaying the main dashboard.

The analysts who build trust fastest are the ones who proactively tell stakeholders "here's how I validated this number" - before being asked. That's not defensiveness. That's professionalism.

---

Summary

Validation is a systematic process: reconcile row counts, aggregates, and dimensional breakdowns against an independent source of truth. Validate immediately after model construction, before building visuals. Build permanent validation measures and a hidden validation page. Document results. Automate quality flags. The goal is not to prove your numbers are right - it's to catch when they're wrong before anyone else does.

Sharpen your data validation skills with real MCQs at devwithdata.in.