Implementing Custom Table Diffing and Schema Comparison Pipelines in Power Query M: Detecting Added, Removed, and Changed Columns Across Refresh Cycles

Introduction

Picture this: your ETL pipeline refreshes overnight, and the next morning your finance team's report is broken. A column called Revenue_USD silently became Revenue_Dollars in the source system. No error was thrown. Power Query just dropped the column and carried on. By the time anyone noticed, three dashboards were showing blank values and someone was already drafting an angry email to IT.

This is one of the most insidious failure modes in data pipelines — schema drift. Unlike data errors, schema changes don't always cause loud failures. They cause silent ones. A column disappears from a join, a type quietly coerces to text, a new column you needed for a calculation never makes it into your model. The solution isn't better monitoring after the fact; it's building schema awareness directly into your Power Query pipeline so that every refresh cycle produces a structured diff you can act on.

By the end of this lesson, you'll have built a complete, production-ready schema comparison pipeline in M. It will detect added columns, removed columns, renamed columns (inferred from type matching), and data type changes across refresh cycles — all expressed as a structured comparison table you can route to alerts, logs, or automated quality checks.

What you'll learn:

• How to capture and persist schema snapshots using M's type introspection capabilities
• How to build a symmetric diff function that compares two schema tables
• How to detect column additions, removals, and type changes across refresh cycles
• How to surface change categories with severity levels for operational alerting
• How to integrate schema diffing into a multi-stage pipeline without breaking existing transformations

---

Prerequisites

You should be comfortable writing parameterized M functions and understand how records and lists work as first-class data structures. If you need a refresher, the lessons on list and record operations in M and writing custom M functions from scratch will get you up to speed. You should also understand how Table.Schema works at a basic level — we'll go deep on it here, but familiarity helps.

---

Understanding What Table.Schema Actually Gives You

Before building a diff engine, you need to understand the raw material. Table.Schema(table) returns a table with one row per column, and it's richer than most people realize.

let
    SampleData = Table.FromRecords({
        [OrderID = 1, CustomerName = "Acme Corp", Revenue = 15420.50, OrderDate = #date(2024, 3, 15)],
        [OrderID = 2, CustomerName = "Globex Ltd", Revenue = 8730.00, OrderDate = #date(2024, 3, 16)]
    }),
    SchemaInfo = Table.Schema(SampleData)
in
    SchemaInfo

Run that and you'll get a table with columns including Name, Position, TypeName, Kind, IsNullable, NumericPrecisionBase, NumericPrecision, NumericScale, DateTimePrecision, MaxLength, IsVariableLength, NativeTypeName, NativeDefaultExpression, and NativeExpression. For our purposes, the critical columns are Name, Position, TypeName, and Kind.

The TypeName column is what we'll use to detect type changes. For a date column it returns "date", for a decimal it returns "number", for text it returns "text", and so on. Kind goes deeper — it will tell you "nullable" for nullable types, which matters when a source system changes a NOT NULL column to nullable.

Now here's the key insight for building a diff pipeline: you need to compare two schema snapshots — one from the previous refresh cycle and one from the current one. This means you need a way to persist the previous schema somewhere accessible at refresh time.

---

The Persistence Problem: Where to Store Your Schema Snapshot

This is the architectural decision that shapes everything else. You have a few options:

Option 1: A named table in an Excel workbook or SharePoint list. You write the current schema to a table at the end of each refresh, and the next refresh reads it back as the "previous" schema. This requires either Power Automate, a macro, or a manual step — it breaks the pure Power Query model.

Option 2: A separate query acting as a "frozen" baseline. You duplicate the schema capture query, disable refresh on it, and use it as a static reference. Simple but requires manual updates to the baseline.

Option 3: A dedicated schema snapshot file (CSV or JSON on SharePoint/OneDrive). Your pipeline writes schema snapshots to a known file location and reads the previous one at refresh time. This is the most robust approach for automated pipelines.

Option 4: A parameter table with your expected schema explicitly defined. You define what columns should exist, and the diff engine compares reality against your specification. This is the most practical approach for production Power Query models without write-back capabilities.

For this lesson, we'll implement Option 4 (specification-based diffing) as our primary pattern — it's immediately usable in any Power Query environment. We'll also build the comparison infrastructure that supports Option 2 and 3 when you have write-back.

---

Building the Schema Snapshot Function

First, let's build a reusable function that captures a normalized schema snapshot from any table. We want to return just the columns that matter for diffing, with consistent naming.

Create a new blank query named fnCaptureSchema with this code:

(sourceTable as table, optional tableName as text) as table =>
let
    // Capture raw schema information
    RawSchema = Table.Schema(sourceTable),
    
    // Select and normalize the columns we care about for diffing
    NormalizedSchema = Table.SelectColumns(
        RawSchema,
        {"Name", "Position", "TypeName", "Kind", "IsNullable", "MaxLength"}
    ),
    
    // Rename for clarity in diff output
    RenamedSchema = Table.RenameColumns(NormalizedSchema, {
        {"Name", "ColumnName"},
        {"Position", "ColumnPosition"},
        {"TypeName", "DataType"},
        {"Kind", "TypeKind"},
        {"IsNullable", "Nullable"},
        {"MaxLength", "MaxLength"}
    }),
    
    // Add source table identifier if provided
    WithTableName = if tableName <> null 
        then Table.AddColumn(RenamedSchema, "TableSource", each tableName, type text)
        else RenamedSchema,
    
    // Add capture timestamp for audit trail
    WithTimestamp = Table.AddColumn(
        WithTableName, 
        "CapturedAt", 
        each DateTime.LocalNow(), 
        type datetime
    )
in
    WithTimestamp

This function is your schema camera — point it at any table and it returns a standardized snapshot. The tableName parameter is optional but useful when you're diffing multiple tables in a single pipeline.

Now let's build a complementary function that creates a schema specification from scratch — this is how you define your "expected" schema for the specification-based approach.

Create a query named fnBuildSchemaSpec:

(columnSpecs as list) as table =>
// columnSpecs is a list of records with fields:
// ColumnName (required), DataType (required), Nullable (optional), MaxLength (optional)
let
    // Normalize each spec record to ensure all fields exist
    NormalizedSpecs = List.Transform(columnSpecs, each Record.Combine({
        [ColumnName = null, DataType = "any", Nullable = true, MaxLength = null, 
         ColumnPosition = null, TypeKind = null, TableSource = "Specification",
         CapturedAt = DateTime.LocalNow()],
        _  // User-provided values override defaults
    })),
    
    SpecTable = Table.FromRecords(NormalizedSpecs),
    
    // Ensure correct types on the spec table
    TypedSpec = Table.TransformColumnTypes(SpecTable, {
        {"ColumnName", type text},
        {"DataType", type text},
        {"Nullable", type logical},
        {"CapturedAt", type datetime}
    })
in
    TypedSpec

---

The Core Diff Engine

Now for the heart of the system. The diff engine takes two schema tables — a "before" and an "after" — and produces a structured comparison table where every row represents a change event.

Create a query named fnDiffSchemas:

(beforeSchema as table, afterSchema as table, optional options as record) as table =>
let
    // Merge options with defaults
    DefaultOptions = [
        IncludeUnchanged = false,
        PositionChangeIsSignificant = false,
        NullabilityChangeIsSignificant = true
    ],
    EffectiveOptions = Record.Combine({DefaultOptions, if options = null then [] else options}),
    
    // Extract column name lists from each schema
    BeforeColumns = List.Transform(Table.ToRecords(beforeSchema), each _[ColumnName]),
    AfterColumns  = List.Transform(Table.ToRecords(afterSchema),  each _[ColumnName]),
    
    // --- CATEGORY 1: REMOVED COLUMNS ---
    // Columns that exist in before but not in after
    RemovedColumnNames = List.Difference(BeforeColumns, AfterColumns),
    RemovedRows = List.Transform(RemovedColumnNames, (colName) => 
        let
            BeforeRow = List.First(List.Select(Table.ToRecords(beforeSchema), each _[ColumnName] = colName))
        in
            [
                ColumnName     = colName,
                ChangeType     = "REMOVED",
                Severity       = "HIGH",
                BeforeDataType = BeforeRow[DataType],
                AfterDataType  = null,
                BeforeNullable = BeforeRow[Nullable],
                AfterNullable  = null,
                BeforePosition = BeforeRow[ColumnPosition],
                AfterPosition  = null,
                ChangeDetail   = "Column removed from source"
            ]
    ),
    
    // --- CATEGORY 2: ADDED COLUMNS ---
    // Columns that exist in after but not in before
    AddedColumnNames = List.Difference(AfterColumns, BeforeColumns),
    AddedRows = List.Transform(AddedColumnNames, (colName) =>
        let
            AfterRow = List.First(List.Select(Table.ToRecords(afterSchema), each _[ColumnName] = colName))
        in
            [
                ColumnName     = colName,
                ChangeType     = "ADDED",
                Severity       = "MEDIUM",
                BeforeDataType = null,
                AfterDataType  = AfterRow[DataType],
                BeforeNullable = null,
                AfterNullable  = AfterRow[Nullable],
                BeforePosition = null,
                AfterPosition  = AfterRow[ColumnPosition],
                ChangeDetail   = "New column detected in source"
            ]
    ),
    
    // --- CATEGORY 3: COLUMNS PRESENT IN BOTH — check for changes ---
    SharedColumnNames = List.Intersect({BeforeColumns, AfterColumns}),
    
    SharedComparisons = List.Transform(SharedColumnNames, (colName) =>
        let
            BeforeRow = List.First(List.Select(Table.ToRecords(beforeSchema), each _[ColumnName] = colName)),
            AfterRow  = List.First(List.Select(Table.ToRecords(afterSchema),  each _[ColumnName] = colName)),
            
            TypeChanged      = BeforeRow[DataType]    <> AfterRow[DataType],
            NullableChanged  = BeforeRow[Nullable]    <> AfterRow[Nullable],
            PositionChanged  = BeforeRow[ColumnPosition] <> AfterRow[ColumnPosition],
            
            IsSignificantChange = TypeChanged 
                or (NullableChanged and EffectiveOptions[NullabilityChangeIsSignificant])
                or (PositionChanged and EffectiveOptions[PositionChangeIsSignificant]),
            
            // Build a human-readable change detail string
            ChangeDetails = Text.Combine(
                List.Select({
                    if TypeChanged then "Type: " & (BeforeRow[DataType] ?? "null") & " → " & (AfterRow[DataType] ?? "null") else null,
                    if NullableChanged then "Nullable: " & Text.From(BeforeRow[Nullable]) & " → " & Text.From(AfterRow[Nullable]) else null,
                    if PositionChanged then "Position: " & Text.From(BeforeRow[ColumnPosition]) & " → " & Text.From(AfterRow[ColumnPosition]) else null
                }, each _ <> null),
                "; "
            ),
            
            ChangeType = if IsSignificantChange then "MODIFIED"
                         else if PositionChanged then "REORDERED"
                         else "UNCHANGED",
            
            Severity = if TypeChanged then "HIGH"
                       else if NullableChanged then "MEDIUM"
                       else "LOW"
        in
            if ChangeType = "UNCHANGED" and not EffectiveOptions[IncludeUnchanged] then null
            else [
                ColumnName     = colName,
                ChangeType     = ChangeType,
                Severity       = Severity,
                BeforeDataType = BeforeRow[DataType],
                AfterDataType  = AfterRow[DataType],
                BeforeNullable = BeforeRow[Nullable],
                AfterNullable  = AfterRow[Nullable],
                BeforePosition = BeforeRow[ColumnPosition],
                AfterPosition  = AfterRow[ColumnPosition],
                ChangeDetail   = if ChangeDetails = "" then "No significant changes" else ChangeDetails
            ]
    ),
    
    // Filter out null entries (UNCHANGED columns when IncludeUnchanged = false)
    FilteredShared = List.Select(SharedComparisons, each _ <> null),
    
    // --- COMBINE ALL CHANGE CATEGORIES ---
    AllChangeRecords = RemovedRows & AddedRows & FilteredShared,
    
    // Build the result table
    ResultTable = if List.IsEmpty(AllChangeRecords)
        then Table.FromRecords(
            {[ColumnName = null, ChangeType = "NO_CHANGES", Severity = "NONE", 
              BeforeDataType = null, AfterDataType = null, BeforeNullable = null,
              AfterNullable = null, BeforePosition = null, AfterPosition = null,
              ChangeDetail = "Schemas are identical"]},
            type table [ColumnName = text, ChangeType = text, Severity = text,
                        BeforeDataType = text, AfterDataType = text, 
                        BeforeNullable = logical, AfterNullable = logical,
                        BeforePosition = Int64.Type, AfterPosition = Int64.Type,
                        ChangeDetail = text]
        )
        else Table.FromRecords(AllChangeRecords),
    
    // Sort by severity for readability: HIGH first
    SeverityOrder = {"HIGH", "MEDIUM", "LOW", "NONE"},
    SortedResult = Table.Sort(ResultTable, {
        {"Severity", (a, b) => 
            Value.Compare(
                List.PositionOf(SeverityOrder, a), 
                List.PositionOf(SeverityOrder, b)
            )
        },
        {"ChangeType", Order.Ascending}
    })
in
    SortedResult

There's a lot happening here — let's walk through the design decisions.

The engine splits the problem into three independent comparisons: pure removals, pure additions, and the more nuanced "shared columns" analysis. This structure makes the logic easy to reason about and extend. Each branch produces a list of records with the same shape, which we concatenate before converting to a table.

For shared columns, the severity logic is deliberately opinionated: type changes are always HIGH severity because they can silently break calculations, while nullability changes default to MEDIUM. Position changes are LOW by default and not considered "significant" (they won't trigger MODIFIED status) unless you explicitly set PositionChangeIsSignificant = true in the options record.

---

Detecting Probable Column Renames

Pure column name matching misses a common scenario: a column gets renamed but keeps the same data type and position. CustomerName becomes ClientName. The diff engine above will flag this as one REMOVED and one ADDED column — technically correct, but often misleading.

We can add a heuristic rename detector as a post-processing step:

(diffResult as table, optional maxCandidates as number) as table =>
let
    MaxCandidatePairs = if maxCandidates = null then 3 else maxCandidates,
    
    // Extract removed and added columns from the diff result
    RemovedCols = Table.SelectRows(diffResult, each [ChangeType] = "REMOVED"),
    AddedCols   = Table.SelectRows(diffResult, each [ChangeType] = "ADDED"),
    
    // For each removed column, find added columns with the same data type
    // These are rename candidates
    RenameCandidates = Table.AddColumn(RemovedCols, "RenameCandidates", (removedRow) =>
        let
            SameTypeCandidates = Table.SelectRows(AddedCols, each 
                [BeforeDataType] = null and  // AddedCols have null BeforeDataType
                [AfterDataType] = removedRow[BeforeDataType]
            ),
            CandidateNames = Table.Column(SameTypeCandidates, "ColumnName")
        in
            if List.Count(CandidateNames) > 0 and List.Count(CandidateNames) <= MaxCandidatePairs
            then Text.Combine(CandidateNames, ", ")
            else null
    ),
    
    // Flag rows with at least one rename candidate
    WithRenameFlag = Table.AddColumn(RenameCandidates, "PossibleRename", 
        each [RenameCandidates] <> null, type logical),
    
    // Build an annotation to add back to the main diff table
    AnnotatedRemoved = Table.TransformColumns(
        Table.SelectRows(WithRenameFlag, each [PossibleRename] = true),
        {{"ChangeDetail", (detail) => detail & " ⚠ Possible rename — candidates: "}}
    ),
    
    // Merge the rename candidate info back into ChangeDetail
    FinalAnnotated = Table.AddColumn(AnnotatedRemoved, "FinalDetail", each 
        [ChangeDetail] & [RenameCandidates]
    ),
    
    // Replace ChangeDetail in original diff for matching rows
    UpdatedDiff = Table.TransformRows(diffResult, (row) =>
        let
            MatchingAnnotation = Table.SelectRows(FinalAnnotated, each [ColumnName] = row[ColumnName]),
            HasAnnotation = Table.RowCount(MatchingAnnotation) > 0
        in
            if HasAnnotation 
            then Record.TransformFields(row, {
                "ChangeDetail", each Table.First(MatchingAnnotation)[FinalDetail],
                "ChangeType", each "PROBABLE_RENAME"
            })
            else row
    )
in
    Table.FromRecords(UpdatedDiff)

This is a heuristic, not a proof. It works well when renames happen in isolation and the data type is preserved. It gets noisy when you have many columns of the same type. The maxCandidates guard prevents it from flagging ambiguous cases where five text columns were removed and six were added simultaneously — in that situation, you want the original REMOVED/ADDED categorization, not a speculative rename list.

---

Putting It All Together: A Real Pipeline

Let's build a complete working example. Imagine you're tracking the schema of a Sales Orders table from a SQL source that's managed by another team. You've defined a schema contract and want to verify it on every refresh.

// Query: OrdersSchemaContract
// This is your specification — what you EXPECT the schema to look like
let
    ExpectedSchema = fnBuildSchemaSpec({
        [ColumnName = "OrderID",        DataType = "Int64.Type",  Nullable = false],
        [ColumnName = "CustomerName",   DataType = "text",        Nullable = false],
        [ColumnName = "Revenue_USD",    DataType = "number",      Nullable = true],
        [ColumnName = "OrderDate",      DataType = "date",        Nullable = false],
        [ColumnName = "SalesRepID",     DataType = "Int64.Type",  Nullable = true],
        [ColumnName = "ProductCategory",DataType = "text",        Nullable = true],
        [ColumnName = "ShippingRegion", DataType = "text",        Nullable = true]
    })
in
    ExpectedSchema

// Query: OrdersCurrentSchema
// This captures the actual schema from your live source
let
    LiveOrders = YourSQLSourceQuery,  // replace with your actual source query reference
    CurrentSchema = fnCaptureSchema(LiveOrders, "SalesOrders")
in
    CurrentSchema

// Query: OrdersSchemaDiff
// This is your diff report — load this to a table in your data model
let
    Expected = OrdersSchemaContract,
    Actual   = OrdersCurrentSchema,
    
    // Run the core diff
    RawDiff = fnDiffSchemas(
        Expected, 
        Actual,
        [IncludeUnchanged = false, NullabilityChangeIsSignificant = true]
    ),
    
    // Apply rename detection
    AnnotatedDiff = fnAnnotateRenames(RawDiff),
    
    // Add a refresh timestamp for historical tracking
    WithRefreshTime = Table.AddColumn(
        AnnotatedDiff, 
        "RefreshTimestamp", 
        each DateTime.LocalNow(), 
        type datetime
    ),
    
    // Add a summary flag: does this refresh have any HIGH severity issues?
    HasCriticalIssues = List.AnyTrue(
        List.Transform(Table.ToRecords(AnnotatedDiff), each _[Severity] = "HIGH")
    ),
    
    WithCriticalFlag = Table.AddColumn(
        WithRefreshTime,
        "RefreshHasCriticalIssues",
        each HasCriticalIssues,
        type logical
    )
in
    WithCriticalFlag

This produces a clean diff table you can load directly into your Power BI model. Create a card visual showing the count of HIGH severity rows — if it's nonzero, you have a problem that needs attention before this report is shared with stakeholders.

---

Handling Dynamic Column Detection

Sometimes you're not working with a known schema — you're ingesting files from external partners where the schema itself is the unknown. In those cases, you want to compare the current schema against the schema from the previous file batch.

This is where dynamic queries in M become important. The pattern requires two file references: one to the "current" file and one to the "previous" file (or a snapshot saved from the previous run).

// Query: PartnerFileSchemaComparison
// Compares this week's partner file against last week's
let
    // Load current week's file
    CurrentFile = Csv.Document(
        File.Contents("C:\DataDrops\PartnerFeed_Current.csv"),
        [Delimiter = ",", Columns = null, Encoding = 1252, QuoteStyle = QuoteStyle.None]
    ),
    CurrentPromoted = Table.PromoteHeaders(CurrentFile, [PromoteAllScalars = true]),
    
    // Load previous week's file (saved from last run)
    PreviousFile = Csv.Document(
        File.Contents("C:\DataDrops\PartnerFeed_Previous.csv"),
        [Delimiter = ",", Columns = null, Encoding = 1252, QuoteStyle = QuoteStyle.None]
    ),
    PreviousPromoted = Table.PromoteHeaders(PreviousFile, [PromoteAllScalars = true]),
    
    // Capture schemas from both
    CurrentSchema  = fnCaptureSchema(CurrentPromoted,  "PartnerFeed_Current"),
    PreviousSchema = fnCaptureSchema(PreviousPromoted, "PartnerFeed_Previous"),
    
    // Run diff: previous is "before", current is "after"
    SchemaDiff = fnDiffSchemas(PreviousSchema, CurrentSchema),
    
    // Count changes by category
    AddedCount   = Table.RowCount(Table.SelectRows(SchemaDiff, each [ChangeType] = "ADDED")),
    RemovedCount = Table.RowCount(Table.SelectRows(SchemaDiff, each [ChangeType] = "REMOVED")),
    ModifiedCount = Table.RowCount(Table.SelectRows(SchemaDiff, each [ChangeType] = "MODIFIED")),
    
    // Add summary metadata as a custom column for easy dashboard display
    WithSummary = Table.AddColumn(SchemaDiff, "DiffSummary", 
        each Text.Format(
            "#{0} added, #{1} removed, #{2} modified",
            {AddedCount, RemovedCount, ModifiedCount}
        ),
        type text
    )
in
    WithSummary

---

Performance Considerations

The diff engine as written uses List.Select inside List.Transform loops, which means for every column in the "shared" set, it scans the full schema table to find the matching row. For a typical table with 20–100 columns, this is completely negligible. Schema tables are tiny.

However, if you're running this across hundreds of tables in a single refresh — say, you've built an enterprise schema monitoring system — the nested scans can add up. The fix is to convert the schema tables to indexed records before the comparison:

// Convert a schema table to a record indexed by column name
// This gives O(1) lookups instead of O(n) scans
(schemaTable as table) as record =>
let
    Rows = Table.ToRecords(schemaTable),
    IndexedRecord = Record.FromList(
        Rows,
        List.Transform(Rows, each _[ColumnName])
    )
in
    IndexedRecord

With this, instead of List.Select(..., each _[ColumnName] = colName) you'd write IndexedRecord[colName] — a direct record field access. The performance patterns guide covers this pattern extensively in the context of lookup optimization.

Also worth noting: since schema capture queries are reference-type operations (they don't load data, they just inspect structure), they're naturally lightweight and typically don't require Table.Buffer unless you're referencing the same source table in both the schema capture and the main data load query.

---

Hands-On Exercise

Build a complete schema monitoring pipeline for a realistic scenario. Here's your setup:

Scenario: You manage a Power BI report that connects to a weekly Excel export from your company's CRM system. The CRM team occasionally adds, renames, or drops columns without notifying anyone. You've been burned before.

Your task:

1. Create sample "before" and "after" tables that simulate two weeks of CRM exports. The "after" table should have: one renamed column (simulate by changing the name), one new column (LastContactMethod), one removed column (LeadScore), and one type change (AnnualRevenue changes from number to text because someone formatted it with a currency symbol).

// Week 1 CRM Export (your "before")
let
    CRMWeek1 = Table.FromRecords({
        [ContactID = 1, CompanyName = "Apex Solutions", AnnualRevenue = 2500000.00, 
         LeadScore = 87, AccountOwner = "Sarah Chen", CreatedDate = #date(2024,1,15),
         ContactEmail = "info@apex.com", IndustryCode = "TECH"]
    }),
    Week1Schema = fnCaptureSchema(
        Table.TransformColumnTypes(CRMWeek1, {
            {"ContactID", Int64.Type}, {"AnnualRevenue", type number},
            {"LeadScore", Int64.Type}, {"CreatedDate", type date}
        }), 
        "CRM_Export"
    )
in
    Week1Schema

// Week 2 CRM Export (your "after") — schema has drifted
let
    CRMWeek2 = Table.FromRecords({
        [ContactID = 1, CompanyName = "Apex Solutions", AnnualRevenue = "$2,500,000",
         AccountManager = "Sarah Chen", CreatedDate = #date(2024,1,22),
         ContactEmail = "info@apex.com", IndustryCode = "TECH",
         LastContactMethod = "Email"]
    }),
    Week2Schema = fnCaptureSchema(
        Table.TransformColumnTypes(CRMWeek2, {
            {"ContactID", Int64.Type}, {"AnnualRevenue", type text},
            {"CreatedDate", type date}
        }),
        "CRM_Export"
    )
in
    Week2Schema

2. Run fnDiffSchemas(Week1Schema, Week2Schema) and verify the output shows:

   • LeadScore as REMOVED (HIGH severity)
   • LastContactMethod as ADDED (MEDIUM severity)
   • AnnualRevenue as MODIFIED (HIGH severity — type changed from number to text)
   • AccountOwner as REMOVED and AccountManager as ADDED (and if you apply rename detection, AccountOwner → AccountManager should be flagged as PROBABLE_RENAME)

3. Add a summary row to the diff output that concatenates the change counts into a single text value like: "Schema drift detected: 2 removed, 1 added, 1 modified".

4. Extension challenge: Modify fnDiffSchemas to accept a third parameter, ignoredColumns as list, that lets you pass column names to exclude from the diff. This is useful for columns you know will change every week (like ExportGeneratedAt).

---

Common Mistakes & Troubleshooting

"My diff shows everything as REMOVED and ADDED, even when columns match"

The most common cause is column name case sensitivity. M string comparison is case-sensitive. If your before schema has "customerid" and your after schema has "CustomerID", they won't match. Fix this by normalizing column names during schema capture:

// In fnCaptureSchema, add this step after NormalizedSchema:
NormalizedCaseSchema = Table.TransformColumns(
    RenamedSchema, 
    {{"ColumnName", Text.Lower, type text}}
)

Be careful though — if your source genuinely has CustomerID and customerid as distinct columns (rare but possible in some databases), case normalization will cause false UNCHANGED results.

"Table.Schema returns TypeName = null for some columns"

This happens with columns that have complex or custom M types. The DataType field in your schema snapshot will be null, and null <> null evaluates to false in M (nulls aren't equal to each other), so these columns will always appear as MODIFIED even when unchanged. Add a null-safe comparison in the type check:

// Replace:
TypeChanged = BeforeRow[DataType] <> AfterRow[DataType],

// With:
TypeChanged = (BeforeRow[DataType] ?? "") <> (AfterRow[DataType] ?? ""),

"The rename detection is producing too many false positives"

Reduce maxCandidates to 1. If there's more than one possible rename target for a given removed column, it's ambiguous — better to report REMOVED/ADDED than a potentially wrong PROBABLE_RENAME. You can also add a secondary heuristic: only flag probable renames when the ColumnPosition is the same, since a rename typically doesn't move a column.

"My schema spec uses type names that don't match what Table.Schema returns"

This is a documentation gap in Power Query. Table.Schema returns M type names in lowercase ("number", "text", "date"), but when writing specs you might instinctively write "Number" or "Decimal.Type". Standardize your spec to use the exact strings that Table.Schema returns. Print out a Table.Schema on a sample table to verify the exact strings for your source types before writing the spec.

"My diff query is slow when the source table has millions of rows"

Remember: Table.Schema doesn't read all the data — it reads the metadata. But your query still references the source table, which might trigger a full load depending on query folding. If performance is an issue, ensure your source query folds correctly and that the schema capture is done on a reference to the already-loaded query rather than a fresh connection. Using Table.Buffer on the source before passing it to fnCaptureSchema ensures it's only loaded once across schema capture and data transformation.

---

Summary & Next Steps

You've built a complete schema diffing and comparison pipeline from scratch. The system includes:

• fnCaptureSchema — a normalized schema snapshot function that works on any table
• fnBuildSchemaSpec — a specification builder for contract-based schema validation
• fnDiffSchemas — a flexible diff engine with configurable severity and options
• Rename heuristics — probabilistic detection of column renames via type and position matching
• Performance-conscious patterns — indexed record lookups and buffer-aware architecture

The real power of this approach is that it turns schema drift from an invisible problem into a visible, queryable event. Every refresh produces structured evidence of what changed, with severity levels that let you build automated alerts or conditional processing gates.

From here, consider these natural extensions:

• Persist diff history by appending OrdersSchemaDiff outputs to a SharePoint list or Azure Table Storage via Power Automate — now you have a schema changelog with timestamps.
• Add column count trending to your data model so you can visualize schema growth or contraction over time in a line chart.
• Extend the spec approach using the metadata and type annotation patterns to embed schema expectations directly in your M types, making the spec self-documenting.
• Integrate with slowly changing dimensions — if your source schema changes affect your SCD logic, the SCD implementation patterns lesson shows how to adapt your history tracking when the underlying structure shifts.

Schema awareness isn't a luxury in production pipelines — it's the difference between catching a breaking change before stakeholders do and spending your morning explaining why the revenue numbers are wrong.