Implementing Table Relationship Validation and Referential Integrity Checks in Power Query M: Detecting Orphaned Keys and Broken Joins Before Load

Introduction

Picture this: you've built an elegant star schema model in Power BI. Sales facts join cleanly to a date dimension, a product dimension, and a customer dimension. Your dashboard looks stunning in the demo. Then the business unit uploads their latest extract, and suddenly your total revenue is off by $2.3 million. After two hours of detective work, you discover the cause: a batch of new sales records arrived with product codes that don't exist in your product dimension. The merge silently dropped those rows — Power Query's default Left Outer join quietly discarded them — and no alarm went off.

This is the referential integrity problem. In a relational database, a foreign key constraint would have thrown an error the moment that data tried to land. In Power Query, which has no native enforcement of relationships between tables, broken joins are a silent data quality hazard. Orphaned keys — fact records whose foreign key values have no matching primary key in the dimension — simply vanish, or worse, inflate your row counts when you're using the wrong join type. You need to build the enforcement yourself.

By the end of this lesson, you'll know exactly how to do that. We'll build a comprehensive referential integrity validation framework in M that detects orphaned keys, surfaces broken joins as structured diagnostic tables, conditionally halts pipeline execution on violations, and generates human-readable audit reports — all before a single row loads into your data model.

What you'll learn:

• How orphaned keys are created and why Power Query silently tolerates them
• How to use Table.NestedJoin, List.Difference, and anti-join patterns to detect foreign key violations
• How to build parameterized validation functions that test multiple relationships in a single pass
• How to surface diagnostics as structured error tables with violation counts, sample bad keys, and severity levels
• How to architect a "validation gate" that conditionally aborts the load when integrity checks fail
• How to balance validation thoroughness against query performance and folding behavior

---

Prerequisites

This lesson assumes you're comfortable writing M code by hand, not just clicking through the Power Query UI. Specifically, you should have solid footing with:

• M's functional evaluation model and how let…in expressions compose — if you need a refresher, Understanding the M Formula Language: Syntax, Data Types, and Expression Basics covers the fundamentals
• Merge queries and join types — Combining Data from Multiple Sources with Append and Merge Queries in Power Query will get you to speed if joins feel fuzzy
• Error handling with try...otherwise — a critical skill when building validation gates; Debugging and Error Handling in M: Building Robust try-otherwise Logic and Diagnostic Workflows in Power Query is the companion lesson
• Group By aggregations — Grouping and Aggregating Data in Power Query: Summarize, Count, and Calculate with Group By is helpful context for the aggregation steps in our diagnostic reports

---

Understanding the Orphaned Key Problem in Power Query

Before we write a single line of validation code, we need to internalize exactly what goes wrong and why Power Query's default behavior masks it.

The Silent Drop Problem

Imagine you have a FactSales table with 500,000 rows. Each row carries a ProductID foreign key. Your DimProduct dimension table has 2,000 distinct ProductID values. At some point, a source system migration renames a product line, and 3,200 sales records now reference ProductID values that no longer appear in DimProduct.

When you perform a standard merge:

let
    Source = FactSales,
    MergedWithProduct = Table.NestedJoin(
        Source,
        {"ProductID"},
        DimProduct,
        {"ProductID"},
        "ProductDetail",
        JoinKind.LeftOuter
    ),
    Expanded = Table.ExpandTableColumn(
        MergedWithProduct,
        "ProductDetail",
        {"ProductName", "Category", "SubCategory"}
    )
in
    Expanded

What happens to those 3,200 orphaned rows? They survive the join — a Left Outer join keeps all left-side rows — but their ProductName, Category, and SubCategory columns expand to null. If your downstream measures and calculations treat null category as "unknown" or simply filter it out, the revenue associated with those rows disappears from your reports with no warning.

Alternatively, if someone accidentally configured an Inner join (it's a single dropdown change in the UI), those 3,200 rows vanish entirely, and your total count is wrong.

The Silent Inflate Problem

The orphaned key problem has a less-discussed mirror image: when your dimension table has duplicate primary keys, an inner or left join produces more rows than you started with — a fan-out. If DimProduct somehow has two records for ProductID = 'P-4417' (perhaps from an UPSERT that failed halfway), every fact row referencing that product gets duplicated. Your sales totals double for that product segment, and the error is nearly impossible to catch visually.

What "Referential Integrity" Means in an M Context

In a relational database, referential integrity means: every foreign key value in a child table must have a corresponding primary key value in the parent table, and every primary key must be unique. In Power Query, we translate this into three checkable conditions:

1. Uniqueness of the primary key: No duplicates in the dimension's key column
2. No orphaned foreign keys: Every FK value in the fact table exists in the dimension's PK column
3. No null keys: Neither table has null values in the join columns (null-to-null joins produce incorrect matches in some evaluation contexts)

We'll build validation functions that check all three, then compose them into a complete pipeline gate.

---

Building the Core Orphan Detection Function

Let's start with the most critical check: finding fact rows whose foreign key has no match in the dimension.

The Anti-Join Pattern

An anti-join returns rows from the left table that have no match in the right table. Power Query supports this natively through JoinKind.LeftAnti:

let
    OrphanedSales = Table.NestedJoin(
        FactSales,
        {"ProductID"},
        DimProduct,
        {"ProductID"},
        "Unmatched",
        JoinKind.LeftAnti
    )
in
    OrphanedSales

This returns every row from FactSales where ProductID does not appear in DimProduct. The nested "Unmatched" column will be an empty table for every row (since there's nothing to expand — that's precisely the point of the anti-join). What you have is the exact population of orphaned records.

But we don't just want to see these rows — we want a structured diagnostic that tells us how many violations there are, what the bad keys are, and whether the situation is severe enough to halt the pipeline.

A Parameterized Validation Function

Let's build a reusable function. Following the pattern from Building Reusable Power Query Function Libraries: Parameters, Recursion, and Modular M Code Patterns, we'll define this as a standalone query that returns a function:

// Query name: fn_CheckReferentialIntegrity
(
    FactTable as table,
    FactKeyColumn as text,
    DimensionTable as table,
    DimensionKeyColumn as text,
    RelationshipName as text,
    optional SeverityThreshold as number  // Max allowed orphan count before FAIL
) as record =>

let
    // --- Step 1: Detect orphaned foreign keys ---
    OrphanedRows = Table.NestedJoin(
        FactTable,
        {FactKeyColumn},
        DimensionTable,
        {DimensionKeyColumn},
        "_NoMatch",
        JoinKind.LeftAnti
    ),
    OrphanCount = Table.RowCount(OrphanedRows),

    // --- Step 2: Extract sample of bad keys for diagnostics ---
    BadKeyList = List.Distinct(
        Table.Column(OrphanedRows, FactKeyColumn)
    ),
    SampleBadKeys = List.FirstN(BadKeyList, 20),

    // --- Step 3: Check dimension key uniqueness ---
    DimKeyList = Table.Column(DimensionTable, DimensionKeyColumn),
    DimKeyCount = List.Count(DimKeyList),
    DimDistinctCount = List.Count(List.Distinct(DimKeyList)),
    DuplicateDimKeys = DimKeyCount - DimDistinctCount,

    // --- Step 4: Check for null keys in fact ---
    FactNullCount = List.Count(
        List.Select(
            Table.Column(FactTable, FactKeyColumn),
            each _ = null
        )
    ),

    // --- Step 5: Determine pass/fail ---
    EffectiveThreshold = if SeverityThreshold = null then 0 else SeverityThreshold,
    PassesOrphanCheck = OrphanCount <= EffectiveThreshold,
    PassesDuplicateCheck = DuplicateDimKeys = 0,
    PassesNullCheck = FactNullCount = 0,
    OverallPass = PassesOrphanCheck and PassesDuplicateCheck and PassesNullCheck,

    // --- Step 6: Compose result record ---
    Result = [
        RelationshipName = RelationshipName,
        Status = if OverallPass then "PASS" else "FAIL",
        OrphanedRowCount = OrphanCount,
        OrphanedSampleKeys = SampleBadKeys,
        DimensionDuplicateKeyCount = DuplicateDimKeys,
        FactNullKeyCount = FactNullCount,
        TotalFactRows = Table.RowCount(FactTable),
        OrphanPercent = if Table.RowCount(FactTable) = 0 
                        then 0 
                        else Number.Round(OrphanCount / Table.RowCount(FactTable) * 100, 2),
        CheckTimestamp = DateTime.LocalNow()
    ]
in
    Result

This function returns a record — a structured M data type — containing everything you need to understand the integrity state of one relationship. Let's unpack the key design decisions:

Why return a record instead of a table? A record lets us compose multiple check results into a list and then convert that list to a table in a single subsequent step. It also makes it easier to interrogate individual fields in conditional logic downstream.

Why capture sample bad keys? When this runs in production and a FAIL occurs, you want the analyst debugging the issue to immediately see what the bad keys are — not have to re-run a separate query to find them.

Why check dimension key uniqueness here? Because a fan-out from duplicate dimension keys is the mirror image of the orphan problem. If the dimension has duplicates, your fact join will inflate row counts, which is just as damaging.

---

Running Multiple Relationship Checks in One Pass

A real data model has multiple relationships. Rather than calling our function separately for each one and managing individual result queries, let's build a validation orchestrator that checks all relationships in a single structured operation.

Defining the Relationship Manifest

First, we define what relationships need to be validated. We encode this as a table — which makes it easy to maintain and even load from an external configuration source:

// Query name: RelationshipManifest
let
    Relationships = #table(
        type table [
            RelationshipName = text,
            FactQuery = text,
            FactKey = text,
            DimensionQuery = text,
            DimensionKey = text,
            MaxAllowedOrphans = number
        ],
        {
            {"Sales → Product",    "FactSales",     "ProductID",    "DimProduct",   "ProductID",    0},
            {"Sales → Customer",   "FactSales",     "CustomerID",   "DimCustomer",  "CustomerID",   0},
            {"Sales → Date",       "FactSales",     "OrderDateKey", "DimDate",      "DateKey",      0},
            {"Sales → Territory",  "FactSales",     "TerritoryID",  "DimTerritory", "TerritoryID",  10},
            {"Orders → Product",   "FactOrders",    "ProductID",    "DimProduct",   "ProductID",    0}
        }
    )
in
    Relationships

The Orchestration Layer

Now we need to resolve the FactQuery and DimensionQuery string names to actual table references. M doesn't have a built-in namespace lookup, so we build a simple lookup record that maps names to queries:

// Query name: ValidationOrchestrator
let
    // Map string names to actual table references
    TableRegistry = [
        FactSales    = FactSales,
        FactOrders   = FactOrders,
        DimProduct   = DimProduct,
        DimCustomer  = DimCustomer,
        DimDate      = DimDate,
        DimTerritory = DimTerritory
    ],

    // Run all checks
    ManifestRows = Table.ToRecords(RelationshipManifest),
    
    CheckResults = List.Transform(
        ManifestRows,
        (row) =>
            fn_CheckReferentialIntegrity(
                Record.Field(TableRegistry, row[FactQuery]),
                row[FactKey],
                Record.Field(TableRegistry, row[DimensionQuery]),
                row[DimensionKey],
                row[RelationshipName],
                row[MaxAllowedOrphans]
            )
    ),

    // Convert list of result records to a table
    ResultTable = Table.FromRecords(CheckResults),
    
    // Add a column that renders sample bad keys as a delimited string (for readability)
    WithFormattedKeys = Table.AddColumn(
        ResultTable,
        "SampleOrphanedKeys",
        each Text.Combine(
            List.Transform([OrphanedSampleKeys], each Text.From(_)),
            ", "
        ),
        type text
    ),

    // Drop the raw list column (not display-friendly in a table)
    FinalResult = Table.RemoveColumns(WithFormattedKeys, {"OrphanedSampleKeys" & ""})
    // Note: Keep OrphanedSampleKeys, remove the raw list version
in
    ResultTable

Let me clean that up with a cleaner final form — the key pattern to understand is using Table.FromRecords to flatten a list of result records:

// Query name: ValidationOrchestrator
let
    TableRegistry = [
        FactSales    = FactSales,
        FactOrders   = FactOrders,
        DimProduct   = DimProduct,
        DimCustomer  = DimCustomer,
        DimDate      = DimDate,
        DimTerritory = DimTerritory
    ],

    ManifestRows = Table.ToRecords(RelationshipManifest),

    CheckResults = List.Transform(
        ManifestRows,
        (row) =>
            fn_CheckReferentialIntegrity(
                Record.Field(TableRegistry, row[FactQuery]),
                row[FactKey],
                Record.Field(TableRegistry, row[DimensionQuery]),
                row[DimensionKey],
                row[RelationshipName],
                row[MaxAllowedOrphans]
            )
    ),

    ResultTable = Table.FromRecords(CheckResults),

    // Format the sample keys list as readable text
    WithFormattedKeys = Table.TransformColumns(
        ResultTable,
        {
            {
                "OrphanedSampleKeys",
                each Text.Combine(
                    List.Transform(_ ?? {}, each Text.From(_ ?? "null")),
                    " | "
                ),
                type text
            }
        }
    )
in
    WithFormattedKeys

When this query evaluates, you get a clean diagnostic table with one row per relationship, showing pass/fail status, counts, percentages, and a pipe-delimited sample of the offending keys.

---

Building the Validation Gate

Having a diagnostic table is valuable, but the real power comes from using it as a gate that conditionally blocks downstream queries from running when integrity fails. This is what transforms passive reporting into active enforcement.

The Gate Pattern

The gate is a query that either returns true (all checks passed) or raises a structured error (at least one check failed). Downstream queries reference this gate query, creating a dependency that forces evaluation order.

// Query name: ValidationGate
let
    // Reference the orchestrator results
    AllResults = ValidationOrchestrator,
    
    // Find all failed checks
    FailedChecks = Table.SelectRows(
        AllResults,
        each [Status] = "FAIL"
    ),
    
    FailCount = Table.RowCount(FailedChecks),
    
    // Build a human-readable error message
    ErrorDetails = Text.Combine(
        List.Transform(
            Table.ToRecords(FailedChecks),
            (r) => Text.Combine({
                r[RelationshipName],
                ": ",
                Text.From(r[OrphanedRowCount]),
                " orphaned rows (",
                Text.From(r[OrphanPercent]),
                "%), ",
                Text.From(r[DimensionDuplicateKeyCount]),
                " dim key duplicates. Sample bad keys: ",
                r[SampleOrphanedKeys]
            })
        ),
        "#(lf)"  // newline separator
    ),
    
    // Gate logic: error on failure, true on pass
    GateResult = if FailCount = 0 
                 then true
                 else error Error.Record(
                     "ReferentialIntegrityViolation",
                     "Data load aborted: " & Text.From(FailCount) & " relationship(s) failed integrity checks.",
                     ErrorDetails
                 )
in
    GateResult

Wiring the Gate Into Downstream Queries

Now in your production fact table query, you reference the gate as a dependency before doing anything else:

// Query name: FactSales_Validated
let
    // This line forces the gate to evaluate first.
    // If the gate errors, this query fails before touching any data.
    _Gate = if ValidationGate = true then true else error "Gate check failed",

    // Proceed with normal fact table transformation only if gate passed
    Source = FactSales,
    
    MergedProduct = Table.NestedJoin(
        Source,
        {"ProductID"},
        DimProduct,
        {"ProductID"},
        "ProductDetail",
        JoinKind.LeftOuter
    ),
    
    // ... rest of your transformations
    
    FinalTable = MergedProduct
in
    FinalTable

---

Advanced Pattern: Composite Key Validation

Many real-world schemas use composite foreign keys — relationships where two or more columns together form the join condition. Our current function handles single-column keys. Let's extend it.

The Composite Anti-Join

Power Query's Table.NestedJoin naturally supports multi-column joins by passing lists to both key arguments:

// Composite key orphan detection
let
    CompositeOrphans = Table.NestedJoin(
        FactInventory,
        {"WarehouseID", "ProductID", "DateKey"},
        DimInventorySlot,
        {"WarehouseID", "ProductID", "DateKey"},
        "_NoMatch",
        JoinKind.LeftAnti
    ),
    OrphanCount = Table.RowCount(CompositeOrphans)
in
    OrphanCount

The challenge with composite keys is surfacing meaningful diagnostic information. When a row is orphaned because of a composite key mismatch, you need to show the combination of values that failed, not just individual column values. Here's how to capture that:

// Extract composite key combinations as a single diagnostic string
let
    CompositeOrphans = Table.NestedJoin(
        FactInventory,
        {"WarehouseID", "ProductID", "DateKey"},
        DimInventorySlot,
        {"WarehouseID", "ProductID", "DateKey"},
        "_NoMatch",
        JoinKind.LeftAnti
    ),

    // Create a synthetic composite key column for diagnostics
    WithCompositeKey = Table.AddColumn(
        CompositeOrphans,
        "_CompositeKey",
        each Text.Combine(
            {
                Text.From([WarehouseID]),
                Text.From([ProductID]),
                Text.From([DateKey])
            },
            "::"
        ),
        type text
    ),

    BadCombinations = List.Distinct(
        Table.Column(WithCompositeKey, "_CompositeKey")
    ),

    SampleBad = List.FirstN(BadCombinations, 20)
in
    SampleBad

---

Detecting Fan-Out: When Joins Inflate Your Data

We've focused on the orphan (drop) problem. Now let's tackle the fan-out (inflate) problem — when dimension key duplicates cause your fact join to produce more rows than you started with.

Measuring Fan-Out

// Query name: fn_DetectJoinFanOut
(
    FactTable as table,
    FactKeyColumn as text,
    DimensionTable as table,
    DimensionKeyColumn as text,
    RelationshipName as text
) as record =>

let
    OriginalFactCount = Table.RowCount(FactTable),

    // Perform the actual inner join (worst case for fan-out)
    Joined = Table.NestedJoin(
        FactTable,
        {FactKeyColumn},
        DimensionTable,
        {DimensionKeyColumn},
        "_Dim",
        JoinKind.Inner
    ),
    JoinedCount = Table.RowCount(Joined),

    // Identify which dimension keys are duplicated
    DimKeys = Table.Column(DimensionTable, DimensionKeyColumn),
    DimKeyGroups = List.Buffer(DimKeys),
    DuplicatedKeys = List.Select(
        List.Distinct(DimKeyGroups),
        (k) => List.Count(List.Select(DimKeyGroups, each _ = k)) > 1
    ),

    FanOutOccurred = JoinedCount > OriginalFactCount,
    FanOutRatio = if OriginalFactCount = 0 
                  then 1 
                  else Number.Round(JoinedCount / OriginalFactCount, 4),

    Result = [
        RelationshipName = RelationshipName,
        OriginalFactRows = OriginalFactCount,
        PostJoinRows = JoinedCount,
        FanOutRatio = FanOutRatio,
        FanOutDetected = FanOutOccurred,
        DuplicateDimensionKeys = List.FirstN(DuplicatedKeys, 20),
        DuplicateKeyCount = List.Count(DuplicatedKeys),
        Status = if FanOutOccurred then "FAIL" else "PASS"
    ]
in
    Result

---

Performance Considerations: Making Validation Fast Enough to Run

The validation framework we've built is thorough, but it has a real cost: it evaluates your source tables potentially multiple times. Let's think through the performance implications and apply appropriate mitigations.

The Double-Evaluation Problem

Our fn_CheckReferentialIntegrity function references FactTable in multiple steps: once for the anti-join, once for Table.RowCount, and once for the null key count. If FactTable is a live database query, each reference may trigger a separate round-trip to the source.

The solution is Table.Buffer, which materializes the table in memory and prevents re-evaluation:

// Buffered version of the fact table reference
(
    FactTable as table,
    FactKeyColumn as text,
    DimensionTable as table,
    DimensionKeyColumn as text,
    RelationshipName as text,
    optional SeverityThreshold as number
) as record =>

let
    // Buffer both tables once to prevent repeated source queries
    BufferedFact = Table.Buffer(FactTable),
    BufferedDim = Table.Buffer(DimensionTable),

    OrphanedRows = Table.NestedJoin(
        BufferedFact,
        {FactKeyColumn},
        BufferedDim,
        {DimensionKeyColumn},
        "_NoMatch",
        JoinKind.LeftAnti
    ),
    // ... rest of logic using BufferedFact and BufferedDim
in
    //...

Column Projection: Only Load What You Need

Instead of passing the full fact table to the validation function, project down to just the key column:

// In ValidationOrchestrator, project before passing to validation function
fn_CheckReferentialIntegrity(
    Table.SelectColumns(FactSales, {"ProductID"}),  // Only the key column
    "ProductID",
    Table.SelectColumns(DimProduct, {"ProductID"}), // Only the key column
    "ProductID",
    "Sales → Product",
    0
)

This can reduce memory consumption by 90%+ on wide tables. The validation is about key relationships, not column values — there's no reason to load every fact column.

Query Folding Considerations

If your source is a SQL Server database, Power Query can fold Table.NestedJoin with JoinKind.LeftAnti into a NOT EXISTS or LEFT JOIN ... WHERE right.key IS NULL SQL pattern, pushing the computation to the server. This is dramatically more efficient than pulling all rows to M and joining in-process.

Check whether folding is occurring by right-clicking the orphan detection step and looking for "View Native Query." If it's greyed out, the step isn't folding. Common reasons folding breaks include prior steps that aren't foldable (like Table.Buffer — buffering deliberately breaks folding). Balance between folding and multi-evaluation prevention carefully based on your source system.

For more detail on this trade-off, see Power Query Performance: Master Folding, Buffering & Optimization Techniques.

---

Building the Diagnostic Audit Report

The validation gate tells you pass/fail. But when something fails, your data team needs a rich diagnostic report to investigate and fix the problem quickly. Let's build one.

The Full Audit Report Query

// Query name: IntegrityAuditReport
let
    // Pull full orphaned row details for any failed relationship
    // Using FactSales → DimProduct as the example
    OrphanedRows = Table.NestedJoin(
        FactSales,
        {"ProductID"},
        DimProduct,
        {"ProductID"},
        "_NoMatch",
        JoinKind.LeftAnti
    ),

    // Keep only diagnostic columns plus the orphaned key
    AuditColumns = Table.SelectColumns(
        OrphanedRows,
        {
            "SalesOrderID",
            "ProductID",
            "CustomerID",
            "OrderDate",
            "SalesAmount"
        }
    ),

    // Add metadata columns
    WithMeta = Table.AddColumn(
        AuditColumns,
        "ValidationRun",
        each DateTime.LocalNow(),
        type datetime
    ),

    WithRelName = Table.AddColumn(
        WithMeta,
        "FailedRelationship",
        each "Sales → Product",
        type text
    ),

    WithSeverity = Table.AddColumn(
        WithRelName,
        "Severity",
        each if [SalesAmount] > 10000 then "HIGH" 
             else if [SalesAmount] > 1000 then "MEDIUM"
             else "LOW",
        type text
    ),

    // Sort by most impactful orphans first
    Sorted = Table.Sort(
        WithSeverity,
        {{"SalesAmount", Order.Descending}}
    )
in
    Sorted

This report does something important that the summary table doesn't: it shows you the full context of each orphaned row — including financial impact. A $47,000 sale that gets dropped is categorically different from a $12 one. By adding Severity based on the business value column, you help the data team triage their fix effort.

---

Integrating with a Multi-Stage Pipeline Architecture

In a well-designed ETL pipeline, validation belongs in a specific architectural layer. If you're working with a staging architecture — raw, cleansed, and conformed layers — integrity checks naturally fit at the conformed layer boundary, after cleaning but before modeling.

This aligns with the multi-stage staging pattern described in Building Multi-Stage Staging Architectures in Power Query: Separating Raw, Cleansed, and Conformed Layers for Scalable ETL Pipelines. In that framework, your validation queries sit at the transition point:

Raw Layer → Cleansed Layer → [VALIDATION GATE] → Conformed Layer → Model

The validation gate serves as the quality control checkpoint between cleansed data (which has had null handling, type coercion, and de-duplication applied) and conformed data (which must satisfy relational integrity for the model to function correctly).

This placement is deliberate: running validation on raw data catches too many noise violations from legitimate data quality issues that cleansing would fix. Running it after conformation is too late — the bad data is already in the model. The conformation boundary is the right moment.

---

Hands-On Exercise

This exercise builds a complete referential integrity validation system for a retail dataset. Work through it in sequence — each step builds on the previous one.

Setup

Create the following tables as Power Query queries (you can use #table to create them inline, or import from actual CSV files):

FactSales (500 rows, with some intentionally bad keys):

// Query: FactSales_Raw
let
    Source = #table(
        type table [SalesID = Int64.Type, ProductID = text, CustomerID = Int64.Type, 
                    RegionID = text, SalesAmount = number],
        {
            {1, "P-001", 101, "R-EAST", 2500.00},
            {2, "P-002", 102, "R-WEST", 1200.00},
            {3, "P-999", 103, "R-EAST", 8900.00},  // Bad ProductID
            {4, "P-001", 104, "R-NORTH", 450.00},
            {5, "P-003", 999, "R-SOUTH", 3300.00},  // Bad CustomerID
            {6, "P-002", 102, "R-GHOST", 780.00},   // Bad RegionID
            {7, "P-001", 101, "R-WEST", 12000.00},
            {8, "P-888", 103, "R-EAST", 5600.00}   // Bad ProductID
        }
    )
in
    Source

DimProduct, DimCustomer, and DimRegion with valid keys only (create these similarly).

Exercise Steps

Step 1: Create the fn_CheckReferentialIntegrity function from the lesson. Call it against the Sales → Product relationship and confirm it returns a record with Status = "FAIL" and OrphanedRowCount = 2.

Step 2: Extend your call to check all three relationships (Sales → Product, Sales → Customer, Sales → Region). Build the RelationshipManifest table and the ValidationOrchestrator query. Confirm you get three rows in the result, with at least two showing "FAIL."

Step 3: Build the ValidationGate query. Verify that when you force evaluation (by previewing it), it raises a ReferentialIntegrityViolation error with a readable message listing the failed relationships.

Step 4: Modify the FactSales_Validated query to reference ValidationGate. Confirm that the query preview fails with the gate error rather than returning data.

Step 5 (Challenge): Add a fn_DetectJoinFanOut check by introducing a deliberate duplicate key in DimProduct (add a second row with ProductID = "P-001"). Verify the fan-out function detects this and returns FanOutDetected = true.

Step 6 (Challenge): Build the IntegrityAuditReport query for the Sales → Product relationship. Add a RevenueAtRisk column that totals the SalesAmount for orphaned rows. Confirm it shows $14,500 (the sum of rows 3 and 8 in our sample data).

---

Common Mistakes & Troubleshooting

Mistake 1: Forgetting That Null ≠ Null in Anti-Joins

In M, null = null evaluates to null (not true), which means a fact row with a null foreign key will match a dimension row with a null primary key in some join configurations and fail to match in others. This is non-deterministic and dangerous. Your null key check should be a separate, explicit validation step — never assume your anti-join catches null-keyed rows reliably.

Mistake 2: Validating Raw Data Instead of Cleansed Data

If you run the validation gate against un-cleansed source data, you'll get hundreds of spurious failures from whitespace in keys, case mismatches, and type inconsistencies. Always run validation after your cleansing steps. A "P-001" and "p-001 " will fail an equality join even though they represent the same product. Clean your data with trimming, case normalization, and type coercion before running integrity checks — Cleaning Messy Data with Power Query: Nulls, Errors, and Type Conversions covers the essential cleansing patterns.

Mistake 3: The Gate Query Getting Cached

Power Query caches query results during a session. If you've previously run ValidationGate and it returned true, and then your source data changes to introduce violations, a cached result might still show true. During development, always use Refresh Preview (not the cached result) to force re-evaluation. In production (Power BI Service scheduled refreshes), caching doesn't persist across refresh cycles, so this is less of an issue there.

Mistake 4: Not Buffering the Dimension in the Fan-Out Check

The fan-out detection function uses List.Select inside a List.Select — an O(n²) operation over the dimension key list. For large dimensions, this is brutal. Use List.Buffer on the key list before that operation, and for very large dimensions, consider using Table.Group as a more efficient approach to find duplicates:

// Efficient duplicate key detection using Table.Group
DimKeyDuplicates = Table.SelectRows(
    Table.Group(
        DimensionTable,
        {DimensionKeyColumn},
        {{"Count", Table.RowCount, Int64.Type}}
    ),
    each [Count] > 1
)

This folds to an efficient GROUP BY + HAVING on SQL sources and runs in linear time even in-process.

Mistake 5: Making Validation Too Strict Too Early

A validation gate with zero tolerance is ideal in steady-state production. But when you first bring in a new data source, you'll often find that 15-20% of records have integrity issues — not because the data is catastrophically broken, but because the dimensions haven't been populated with all historical values yet. Don't let a perfectly reasonable data onboarding situation trigger constant gate failures. Use the SeverityThreshold parameter to start with a permissive threshold, generate the audit report, and tighten the threshold as the data quality improves.

Mistake 6: Circular Reference Through the Gate

If your ValidationGate query references ValidationOrchestrator, which references FactSales and DimProduct, and FactSales_Validated references ValidationGate, and ValidationGate also references FactSales_Validated — you've created a circular dependency. Power Query will error with a circular reference message. Keep the dependency chain strictly one-directional: source tables → validation function → orchestrator → gate → validated output tables.

---

Summary & Next Steps

You've built a production-grade referential integrity validation framework from first principles. Let's consolidate what you've accomplished:

Core concepts mastered:

• The orphaned key problem and why Power Query's default join behavior silently masks data loss
• Using JoinKind.LeftAnti to implement the anti-join pattern for orphan detection
• The fan-out problem and how dimension key duplicates inflate your row counts
• Building parameterized, reusable validation functions that return structured diagnostic records
• Composing multiple relationship checks through an orchestration layer driven by a manifest table
• Implementing a validation gate that conditionally aborts pipeline execution on integrity violations
• Performance optimization: column projection, buffering, and query folding trade-offs

Architectural principles to carry forward:

• Validation belongs at the conformed layer boundary — after cleansing, before modeling
• Diagnostic reports should surface business impact (revenue at risk), not just row counts
• Separation of configuration (the manifest) from logic (the validation function) is essential for maintainability
• Zero-tolerance is the right default for steady-state production; tiered thresholds are appropriate during data onboarding

What to explore next:

The validation framework you've built here naturally extends to handle slowly changing dimensions, where referential integrity is time-bounded rather than absolute — a product that was valid in 2022 might be discontinued in 2024, and a sale from 2022 referencing it shouldn't be flagged as orphaned. Merging Slowly Changing Dimensions in Power Query: Tracking Historical Changes with Type 1 and Type 2 SCD Patterns and Implementing Slowly Changing Lookup Tables in Power Query: Point-in-Time Merges with Effective Date Ranges show you how to add temporal validity to your integrity checks.

If your model involves many-to-many relationships — which have their own unique integrity concerns around bridge tables and double-counting — Managing Many-to-Many Relationships in Power Query: Deduplication, Bridge Tables, and Safe Merge Strategies is the logical next destination.

Finally, if you want to connect this validation framework to a system that automatically alerts on failures and manages retry logic, Scheduling and Managing Power Query Refresh Failures in Power BI Service: Alerts, Diagnostics, and Recovery Workflows shows how to surface those gate errors into operational monitoring workflows.

Your data model is only as trustworthy as the relationships it's built on. With this framework in place, you'll know about broken joins the moment the data arrives — not the moment a stakeholder spots a discrepancy in a board-level report.