Implementing Data Validation and Quality Checks in Power Query: Building Automated Assertion Pipelines to Catch Nulls, Duplicates, and Referential Integrity Failures Before Load

Introduction

Picture this: your sales report refreshes on Monday morning, and the regional manager notices that three territories are showing zero revenue. Not bad sales — zero. You dig in and discover that the ETL pipeline happily loaded 14,000 rows where RegionID is null, silently breaking the join to your dimension table. The numbers aren't wrong because the math failed; they're wrong because bad data passed through unchallenged. By the time anyone noticed, the report had been sitting in twelve inboxes for two hours.

This is the class of problem that data validation is designed to prevent. Most Power Query pipelines are built to transform data that behaves. Real-world source data doesn't behave. It shows up with nulls in primary key columns, duplicate transaction IDs from a midnight batch job that ran twice, and foreign keys referencing dimension records that were deleted six months ago. Without deliberate quality gates built into your pipeline, these issues flow downstream and become someone else's problem — usually yours, later, under pressure.

By the end of this lesson, you'll know how to build assertion-style validation queries directly in Power Query that catch data quality failures before a single row lands in your model. You'll have a reusable pattern for surfacing null violations, duplicate key detection, and referential integrity checks — all structured as a quality gate that can halt the load or produce a diagnostic report, depending on what the situation requires.

What you'll learn:

• How to structure assertion queries that test specific data quality rules and return pass/fail results
• Techniques for detecting nulls and blanks across critical columns programmatically
• How to identify duplicate keys using Group By aggregation and expose the offending rows
• Referential integrity validation by anti-joining fact tables against dimension tables
• How to build a consolidated Quality Dashboard query that surfaces all failures in a single view

---

Prerequisites

This lesson assumes you're comfortable working in Power Query's Advanced Editor and have written custom M code before. You should understand how merges and appends work, and have a working familiarity with conditional logic and custom columns. If you've built basic transformations but haven't written M from scratch yet, visit Understanding the M Formula Language: Syntax, Data Types, and Expression Basics first.

---

The Architecture: Validation as a Parallel Pipeline

The biggest conceptual shift here is treating validation as a parallel concern, not an inline step. Most people try to add validation by inserting a filter step and hoping the data doesn't have problems. That approach hides failures — if you filter out null rows, those rows vanish without anyone knowing they existed.

The architecture we're building works like this: your main transformation query runs as normal, producing clean output. Alongside it, a set of assertion queries reference the same source (or an early intermediate step) and each tests one specific rule. A Quality Dashboard query appends all assertion results into a single table. You load the dashboard into your model, and you can build a simple report or even configure a conditional refresh alert against it.

Think of it like a test suite running against your data pipeline. Each assertion is an independent test with a clear pass/fail result, a description, and — crucially — the count of failing rows and optionally the rows themselves.

Key insight: Reference an early, lightly-transformed staging step in your assertions rather than the raw source connector. This avoids re-querying the source multiple times (which can be expensive or hit API rate limits) and keeps your assertions consistent with what's actually flowing into your transformations. You can learn more about structuring these layers in the lesson on Building Multi-Stage Staging Architectures in Power Query.

Here's what the query dependency graph looks like in practice:

Source_SalesOrders (raw connector)
    └── Staging_SalesOrders (light type coercion, rename columns)
            ├── Transform_SalesOrders (main pipeline → loads to model)
            ├── Assert_NoNullOrderIDs (validation query)
            ├── Assert_NoDuplicateOrderIDs (validation query)
            └── Assert_ValidCustomerIDs (referential integrity)

Source_Customers (raw connector)
    └── Staging_Customers
            ├── Transform_Customers (main pipeline)
            └── (also used by Assert_ValidCustomerIDs)

QualityDashboard (appends all Assert_ queries → loads to model)

This structure ensures your assertion queries never add latency to your main transformation pipeline. They run as separate queries, evaluated independently.

---

Setting Up Your Staging Reference

Let's work with a realistic scenario: a sales order system that exports to CSV daily. The file contains orders with a OrderID, CustomerID, ProductID, OrderDate, Quantity, and UnitPrice.

Your staging query does minimal work — just enough to get clean column names and appropriate types:

let
    Source = Csv.Document(
        File.Contents("C:\DataPipeline\sales_orders.csv"),
        [Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    TypedColumns = Table.TransformColumnTypes(
        PromotedHeaders,
        {
            {"OrderID", type text},
            {"CustomerID", type text},
            {"ProductID", type text},
            {"OrderDate", type date},
            {"Quantity", Int64.Type},
            {"UnitPrice", type number}
        }
    )
in
    TypedColumns

Save this as Staging_SalesOrders. Disable load on it — it's an intermediate reference, not a destination. Every assertion query will reference Staging_SalesOrders by name, which means Power Query evaluates the staging step once and branches from there.

Tip: Right-click any query in the Queries pane and select "Enable Load" to toggle it. Queries with load disabled appear in italics and don't produce output tables, but they're fully usable as references by other queries.

---

Assertion Pattern 1: Null and Blank Detection

The most common data quality failure is unexpected nulls in fields that should always have a value. OrderID being null means you can't track the order. CustomerID being null means revenue goes unattributed. Let's build an assertion that catches this.

Here's the pattern for a null check assertion:

let
    Source = Staging_SalesOrders,

    // Define which columns must never be null
    RequiredColumns = {"OrderID", "CustomerID", "ProductID", "OrderDate"},

    // Filter rows where any required column is null or blank
    NullRows = Table.SelectRows(
        Source,
        each List.AnyTrue(
            List.Transform(
                RequiredColumns,
                (col) =>
                    Record.Field(_, col) = null or
                    (type text = Value.Type(Record.Field(_, col)) and
                     Text.Trim(Text.From(Record.Field(_, col))) = "")
            )
        )
    ),

    FailCount = Table.RowCount(NullRows),

    // Build the assertion result record
    Result = Table.FromRecords({[
        AssertionName = "NoNullsInRequiredColumns",
        Status        = if FailCount = 0 then "PASS" else "FAIL",
        FailingRows   = FailCount,
        Description   = "Checks OrderID, CustomerID, ProductID, OrderDate for nulls or blanks",
        TestedAt      = DateTime.LocalNow()
    ]})
in
    Result

A few things worth explaining here. List.AnyTrue combined with List.Transform lets you check multiple columns dynamically without hardcoding a chain of or conditions. When you add a new required column, you just update the RequiredColumns list — the logic adapts automatically. This is significantly more maintainable than writing each [OrderID] = null or [CustomerID] = null or ....

The blank string check (Text.Trim(Text.From(...)) = "") is important because source systems regularly export empty strings rather than nulls. Power Query imports these as "" after type conversion, and they'll pass a simple = null check while still breaking your downstream logic.

Warning: Be careful applying the blank check to non-text columns. Calling Text.From(null) returns null, not an empty string, so the type text guard in the expression above prevents false positives on numeric or date columns that are legitimately null. If you skip that guard and your source has a null Quantity, you'll get an error instead of a clean FAIL result.

The Result table has a consistent schema across all your assertions: AssertionName, Status, FailingRows, Description, and TestedAt. This consistency is what makes the Quality Dashboard work — you can append all assertion results into a single table because they share the same shape.

---

Assertion Pattern 2: Duplicate Key Detection

Duplicate primary keys are insidious. They don't cause errors — they cause silent overcounting. If OrderID = "ORD-8821" appears twice in your fact table, every measure that aggregates by order will double-count it. The source system might have a bug, or maybe a file got included twice when combining files from a folder. Either way, you want to know before it reaches your model.

let
    Source = Staging_SalesOrders,

    // Group by the key column and count occurrences
    Grouped = Table.Group(
        Source,
        {"OrderID"},
        {{"RowCount", each Table.RowCount(_), Int64.Type}}
    ),

    // Keep only keys that appear more than once
    Duplicates = Table.SelectRows(Grouped, each [RowCount] > 1),

    DuplicateKeyCount = Table.RowCount(Duplicates),
    TotalDuplicateRows = 
        if DuplicateKeyCount = 0 
        then 0 
        else List.Sum(Table.Column(Duplicates, "RowCount")) - DuplicateKeyCount,

    Result = Table.FromRecords({[
        AssertionName = "NoDuplicateOrderIDs",
        Status        = if DuplicateKeyCount = 0 then "PASS" else "FAIL",
        FailingRows   = TotalDuplicateRows,
        Description   = "OrderID should be unique per row. " & 
                        Text.From(DuplicateKeyCount) & " key(s) appear more than once.",
        TestedAt      = DateTime.LocalNow()
    ]})
in
    Result

The TotalDuplicateRows calculation deserves a quick explanation. If ORD-8821 appears 3 times, the Group By step shows it with a RowCount of 3. But the "extra" rows — the ones that shouldn't exist — number 2 (3 minus the one legitimate occurrence). So List.Sum(RowCounts) - DuplicateKeyCount gives you the count of genuinely redundant rows, which is more useful than the count of affected keys when you're reporting to a stakeholder.

Key insight: If you need to see which rows are duplicates for diagnostic purposes, create a second query that references Staging_SalesOrders, performs the same Group By, filters to RowCount > 1, and then merges back to the original table to surface the full duplicate rows. Don't put this diagnostic detail inside the assertion itself — keep assertions lean so they evaluate quickly.

---

Assertion Pattern 3: Referential Integrity

Referential integrity failures are where things get genuinely dangerous for your data model. A CustomerID in your fact table that doesn't exist in your customer dimension means that order will either disappear (if you use an inner join) or create a blank row in reports (if you use a left join). Neither outcome is acceptable in production.

The technique here is an anti-join: you merge the fact table against the dimension on the foreign key, using a Left Anti join to return only rows where no match was found in the dimension.

let
    FactSource = Staging_SalesOrders,
    DimSource  = Staging_Customers,  // assumes this staging query exists

    // Keep only the key column from the dimension for the join
    DimKeys = Table.SelectColumns(DimSource, {"CustomerID"}),

    // Left Anti join: rows in fact that have NO match in dimension
    OrphanedRows = Table.NestedJoin(
        FactSource,
        {"CustomerID"},
        DimKeys,
        {"CustomerID"},
        "DimMatch",
        JoinKind.LeftAnti
    ),

    // Remove the joined column — we don't need it
    CleanedOrphans = Table.RemoveColumns(OrphanedRows, {"DimMatch"}),

    OrphanCount = Table.RowCount(CleanedOrphans),

    Result = Table.FromRecords({[
        AssertionName = "ValidCustomerIDReferentialIntegrity",
        Status        = if OrphanCount = 0 then "PASS" else "FAIL",
        FailingRows   = OrphanCount,
        Description   = "Every CustomerID in SalesOrders must exist in the Customers dimension.",
        TestedAt      = DateTime.LocalNow()
    ]})
in
    Result

JoinKind.LeftAnti is one of the most underused join types in Power Query. It returns rows from the left table that have no matching row in the right table — exactly what you need for an orphan check. If you're not yet familiar with the full range of join kinds available, the article on combining data with appends and merges covers them in detail.

You can extend this pattern to check ProductID against a product dimension, RegionID against a region lookup — any foreign key relationship in your schema. Each check becomes its own assertion query with the same output shape.

---

Assertion Pattern 4: Business Rule Validation

Beyond structural checks, you often have business rules that valid data must satisfy. Unit price should never be negative. Quantity should always be a positive integer. Order dates shouldn't be in the future. These are domain constraints that the source system should enforce but frequently doesn't.

let
    Source = Staging_SalesOrders,

    // Define business rules as a list of {RuleName, FilterFunction, Description}
    // Each filter selects VIOLATING rows
    Rules = {
        {
            "PositiveQuantity",
            each [Quantity] <= 0 or [Quantity] = null,
            "Quantity must be a positive integer"
        },
        {
            "NonNegativeUnitPrice",
            each [UnitPrice] < 0 or [UnitPrice] = null,
            "UnitPrice must be >= 0"
        },
        {
            "OrderDateNotInFuture",
            each [OrderDate] > Date.From(DateTime.LocalNow()),
            "OrderDate cannot be in the future"
        }
    },

    // Apply each rule and build a result record
    RuleResults = List.Transform(
        Rules,
        (rule) =>
            let
                RuleName    = rule{0},
                FilterFn    = rule{1},
                RuleDesc    = rule{2},
                Violations  = Table.SelectRows(Source, FilterFn),
                ViolCount   = Table.RowCount(Violations)
            in
            [
                AssertionName = "BusinessRule_" & RuleName,
                Status        = if ViolCount = 0 then "PASS" else "FAIL",
                FailingRows   = ViolCount,
                Description   = RuleDesc,
                TestedAt      = DateTime.LocalNow()
            ]
    ),

    Result = Table.FromRecords(RuleResults)
in
    Result

This pattern is particularly powerful because you can define all your business rules as data — a list of tuples — and the evaluation loop applies them uniformly. Adding a new rule means adding one line to the Rules list. No new queries, no copy-pasting logic. This is the kind of approach that makes a pipeline genuinely maintainable over time, which aligns with the principles in Power Query Best Practices: Building Maintainable ETL Solutions That Last.

Tip: If your business rules list gets long, consider storing it in a separate reference table (an Excel named range or a small lookup file) so non-developers can maintain the rule descriptions without touching M code. You can load that table in Power Query and use it to drive the validation loop dynamically.

---

Building the Quality Dashboard Query

Now you have four assertion queries, each producing a table with the same five-column schema. Combining them is straightforward with Table.Combine:

let
    // Reference all assertion queries by name
    AllAssertions = Table.Combine({
        Assert_NoNullsInRequiredColumns,
        Assert_NoDuplicateOrderIDs,
        Assert_ValidCustomerIDReferentialIntegrity,
        Assert_BusinessRules
    }),

    // Add a helper column to make filtering easy in reports
    WithPassFail = Table.AddColumn(
        AllAssertions,
        "IsFailing",
        each [Status] = "FAIL",
        type logical
    ),

    // Sort failures to the top
    Sorted = Table.Sort(
        WithPassFail,
        {{"IsFailing", Order.Descending}, {"AssertionName", Order.Ascending}}
    ),

    // Summary counts for dashboard KPIs
    TotalChecks  = Table.RowCount(Sorted),
    PassCount    = List.Count(List.Select(Table.Column(Sorted, "Status"), each _ = "PASS")),
    FailCount    = TotalChecks - PassCount,

    // Optionally add summary as metadata — useful for alerting
    FinalTable   = Table.AddColumn(
        Sorted,
        "SummaryNote",
        each Text.From(PassCount) & "/" & Text.From(TotalChecks) & " checks passed",
        type text
    )
in
    FinalTable

Load this query to your data model. In Power BI, you can now build a simple card visual showing total failures, a table of failing assertions with their row counts, and a conditional format that turns the status column red when Status = "FAIL".

Warning: Do not disable load on QualityDashboard. This is the one query in the validation layer that should produce output — it's the table you'll use in reports or monitoring dashboards. All the individual Assert_* queries should have load disabled; they're intermediary steps consumed by the dashboard.

---

Making Validation Gates Block the Load

Sometimes you don't want a report — you want the entire refresh to fail loudly if validation doesn't pass. This is the right approach when your downstream consumers can't be exposed to bad data under any circumstances.

You can implement a hard gate using error in M:

let
    Dashboard  = QualityDashboard,

    FailingRows = Table.SelectRows(Dashboard, each [Status] = "FAIL"),
    FailCount   = Table.RowCount(FailingRows),

    // If any check fails, throw an error that halts the refresh
    Gate = if FailCount > 0
           then error Error.Record(
               "DataQualityFailure",
               Text.From(FailCount) & " validation check(s) failed. Refresh halted. " &
               "Review the QualityDashboard table for details.",
               FailingRows
           )
           else "All checks passed",

    // This step only evaluates if Gate doesn't error
    Source = Staging_SalesOrders,

    // ... rest of your transformation
    Output = Source
in
    Output

The trick is that Gate is evaluated before Output because M evaluates let bindings lazily but will still throw if an error expression is evaluated during the dependency chain. You can force this by referencing Gate in a downstream step:

// Force gate evaluation by referencing it in a step that's guaranteed to evaluate
GatedSource = if Gate = "All checks passed" then Staging_SalesOrders else Staging_SalesOrders

This pattern works, but it's worth understanding the trade-off: a hard gate that errors means your entire report stops refreshing. Your consumers see a refresh failure rather than stale or wrong data. Whether that's the right behavior depends entirely on your context — a financial close report probably warrants a hard gate; an operational dashboard might be better served by surfacing the failures without stopping the load.

For more on debugging and error handling in M, including how try...otherwise interacts with error, that lesson covers the mechanics in depth.

---

Hands-On Exercise

Here's a complete exercise that ties all the patterns together. You'll build a mini validation pipeline for a product sales dataset.

Setup: Create an Excel workbook with a sheet named Orders containing these columns and intentionally bad data:

OrderID	CustomerID	ProductID	OrderDate	Quantity	UnitPrice
ORD-001	CUST-10	PROD-A	2024-01-15	5	29.99
ORD-002	CUST-11	PROD-B	2024-01-16	-2	14.50
ORD-003		PROD-C	2024-01-17	3	8.00
ORD-001	CUST-12	PROD-A	2024-01-18	1	29.99
ORD-004	CUST-99	PROD-D	2099-12-31	4	12.00

Create a second sheet named Customers with CustomerID values: CUST-10, CUST-11, CUST-12 (note: CUST-99 is missing).

Your tasks:

1. Create Staging_Orders and Staging_Customers queries from the respective Excel sheets. Disable load on both.

2. Create Assert_NoNullsInRequiredColumns using the null detection pattern. Expected result: FAIL (CustomerID is missing for ORD-003).

3. Create Assert_NoDuplicateOrderIDs using the duplicate detection pattern. Expected result: FAIL (ORD-001 appears twice).

4. Create Assert_ValidCustomerIDReferentialIntegrity using the anti-join pattern against Staging_Customers. Expected result: FAIL (CUST-99 doesn't exist).

5. Create Assert_BusinessRules covering the three business rules from the pattern above. Expected results: FAIL for PositiveQuantity (ORD-002 has Quantity = -2) and OrderDateNotInFuture (ORD-004 has a future date).

6. Create QualityDashboard by combining all four assertion queries. Enable load. Verify you see 5 total assertions, with 5 failures.

7. Bonus: Add a sixth assertion checking that UnitPrice is never zero (products with a zero price might indicate a data error). Add it to your business rules list and confirm it appears in the dashboard.

---

Common Mistakes & Troubleshooting

Problem: Assertion queries are re-querying the source instead of using the staging reference

This usually happens when you name your staging query something generic that collides with a step name inside the assertion, or when you accidentally create the assertion by editing the staging query directly instead of creating a new one. Always create assertion queries fresh via Home → New Query → Blank Query, then write let Source = Staging_Orders as your first line. Check your query dependencies (View → Query Dependencies) to confirm the arrows point from assertions to the staging query, not directly to the source.

Problem: DateTime.LocalNow() causes every refresh to show all assertions as "changed"

If you're using incremental refresh or change-tracking in Power BI, DateTime.LocalNow() in the TestedAt column means the Quality Dashboard never has identical data between refreshes, which can cause downstream issues. Replace it with Date.From(DateTime.LocalNow()) if date precision is sufficient, or accept that the dashboard always fully refreshes.

Problem: The Left Anti join returns zero rows even when orphans exist

Nine times out of ten, this is a data type mismatch. If CustomerID in the fact table is type text and in the dimension it's type number (because the source system stores IDs as integers), the join silently finds no matches — including no anti-matches. Always verify that your staging queries cast both sides of a join to the same type before the merge. The lesson on cleaning nulls, errors, and type conversions covers type coercion thoroughly.

Problem: Performance is degrading after adding validation queries

Each assertion query that references Staging_Orders creates a dependency on it. If Staging_Orders itself references a slow connector, Power Query may evaluate it multiple times despite the reference structure. Use Table.Buffer() on your staging query to cache it in memory:

// In Staging_Orders, wrap the final step:
Buffered = Table.Buffer(TypedColumns)

This forces evaluation once and holds the result in memory for all downstream queries. Be mindful that this increases memory usage proportionally to your row count. The performance optimization lesson covers when buffering helps vs. hurts in detail.

Problem: The business rule loop fails with a type mismatch error when building records

If one of your rule filter functions references a column that was null in a row, M may propagate the null in unexpected ways through comparisons. Wrap comparisons defensively:

each ([Quantity] ?? 0) <= 0  // null coalescing to 0 before comparison

The ?? operator returns the right side if the left side is null, preventing null propagation through arithmetic comparisons.

Note: The ?? null-coalescing operator was introduced in a relatively recent version of the M engine. If you're working in an older Power BI Desktop build or in Excel's Power Query, you may need to use the explicit form: if [Quantity] = null then true else [Quantity] <= 0.

---

Summary & Next Steps

You've now built a complete assertion-based validation pipeline in Power Query. The key architectural decisions that make this work in production are: staging queries as shared, buffered references; assertions as independent parallel queries with a consistent output schema; and a dashboard query that consolidates all results into a single loadable table.

The patterns you've learned here — null detection with List.AnyTrue, duplicate detection with Table.Group, referential integrity with JoinKind.LeftAnti, and business rule loops over a parameterized rule list — are reusable across any source system and any domain. Once you've built this structure once, adapting it to a new pipeline is a matter of updating the staging references and the rule definitions, not rebuilding the logic.

Where you take this next depends on what your environment demands:

• Automated alerting: Integrate the QualityDashboard with Power BI data-driven alerts or Power Automate flows so failures generate Teams or email notifications without human monitoring.
• Historical tracking: Append each refresh's assertion results to a historical log table so you can trend data quality over time. This pairs naturally with the techniques in Automating Incremental Data Refreshes in Power Query.
• Reusable validation functions: Extract your assertion patterns into a reusable function library so the same fnAssertNoNulls function can be called from any pipeline in your workspace with just a table and column list as arguments.
• Schema validation: Extend the pipeline to check that the incoming table has the expected columns before applying any transformations — a critical safeguard covered in the lesson on Handling Dynamic Schema Changes in Power Query.

Data quality isn't a one-time cleanup project. It's an ongoing commitment encoded in your pipeline architecture. The assertion pattern you've built today is the infrastructure that makes that commitment sustainable.