Handling Dynamic Schema Changes in Power Query: Strategies for Evolving Source Data Structures

Introduction

You've built a clean, elegant Power Query solution. It loads perfectly, transforms correctly, and your stakeholders are happy. Then Monday morning arrives, and so does an email: "Hey, we added a few new columns to the export — hope that doesn't break anything!" It does break things. Your query throws a cryptic error, your dashboard goes blank, and you spend the morning hunting down which step failed instead of doing actual analysis.

Schema drift — when source data structure changes over time — is one of the most common real-world Power Query problems, and it's almost never covered in beginner tutorials. New columns appear, old ones disappear, column names get renamed by a system upgrade, or the order of columns shifts after a database view is rebuilt. Any of these changes can silently corrupt your output or loudly break your query entirely. The challenge is that Power Query's default behavior is extremely brittle: it memorizes the exact schema it first encountered and treats any deviation as an error.

By the end of this lesson, you will know how to design Power Query solutions that survive schema changes gracefully. You'll move from reactive fire-fighting to proactive defensive design.

What you'll learn:

• Why Power Query's default schema-locking behavior creates fragile pipelines and how to recognize it
• How to use column selection, reordering, and conditional column existence checks to make transformations schema-tolerant
• How to write dynamic column detection logic that adapts to new or missing columns without manual intervention
• How to build a flexible column-mapping table that decouples your transformation logic from hard-coded column names
• How to handle the specific scenario where column order changes but names stay consistent, and vice versa

---

Prerequisites

You should be comfortable with:

• Writing and reading basic M code in the Advanced Editor
• Understanding Power Query's step-by-step evaluation model
• Familiarity with Table.SelectColumns, Table.RenameColumns, and Table.TransformColumnTypes
• Basic understanding of lists and records in M

If you haven't worked in the Advanced Editor before, spend 30 minutes there first — this lesson lives almost entirely in M code.

---

Why Power Query Is Brittle by Default

Before jumping to solutions, let's understand the root cause. When you use the Power Query GUI to apply a transformation — say, removing a column or changing a data type — Power Query records the operation against the exact column names present at that moment. The generated M code looks like this:

= Table.SelectColumns(Source, {"OrderID", "CustomerName", "Region", "SalesAmount", "OrderDate"})

This is a hard-coded list. If the source tomorrow delivers a file with "SalesRep" added and "Region" renamed to "Territory", this step will throw:

Expression.Error: The column 'Region' of the table wasn't found.

Or worse — if the source adds new columns but keeps all the old ones, the Table.SelectColumns step silently discards the new columns without any warning. You might not notice for weeks that you've been missing data.

The other major offender is Table.TransformColumnTypes, which Power Query generates obsessively. Every time you apply data types through the GUI, it records every column name explicitly:

= Table.TransformColumnTypes(
    PreviousStep,
    {
        {"OrderID", Int64.Type},
        {"CustomerName", type text},
        {"Region", type text},
        {"SalesAmount", Currency.Type},
        {"OrderDate", type date}
    }
)

If a column in that list doesn't exist, the entire step fails. If a new column appears that needs a type applied, this step ignores it.

Key insight: Power Query's GUI-generated code optimizes for reproducibility on a fixed schema. Your job as a practitioner is to identify which steps need to be made schema-aware, and rewrite them accordingly.

---

Strategy 1: Select Only What You Need, Defensively

The first and simplest strategy is to stop selecting columns that don't exist. Instead of hard-coding a column list, you compute the intersection of "columns I want" and "columns that actually exist."

Here's the pattern:

let
    Source = Excel.Workbook(File.Contents("C:\Data\SalesExport.xlsx"), null, true),
    SalesTable = Source{[Item="Sales",Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(SalesTable, [PromoteAllScalars=true]),

    // Define the columns we care about
    DesiredColumns = {"OrderID", "CustomerName", "Region", "SalesAmount", "OrderDate"},

    // Find the intersection with what actually exists
    AvailableColumns = Table.ColumnNames(PromotedHeaders),
    ColumnsToSelect = List.Intersect({DesiredColumns, AvailableColumns}),

    // Select only the safe intersection
    SelectedColumns = Table.SelectColumns(PromotedHeaders, ColumnsToSelect)
in
    SelectedColumns

List.Intersect takes a list of lists and returns only elements present in all of them. By wrapping both your desired columns and the available columns, you get a selection list that will never reference a non-existent column.

This pattern is appropriate when missing columns are acceptable — you're intentionally saying "give me whatever subset of these columns exists." But sometimes a missing column is a real problem. For example, if OrderID is missing, your entire data model breaks. For those cases, you want to raise a meaningful error rather than silently produce bad data:

let
    Source = Excel.Workbook(File.Contents("C:\Data\SalesExport.xlsx"), null, true),
    SalesTable = Source{[Item="Sales",Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(SalesTable, [PromoteAllScalars=true]),

    // Required columns — query should fail loudly if these are missing
    RequiredColumns = {"OrderID", "CustomerName", "SalesAmount"},

    // Optional columns — include if present, ignore if not
    OptionalColumns = {"Region", "SalesRep", "Discount"},

    AvailableColumns = Table.ColumnNames(PromotedHeaders),

    // Check required columns exist
    MissingRequired = List.Difference(RequiredColumns, AvailableColumns),
    RequiredCheck = if List.Count(MissingRequired) > 0
        then error "Required columns missing: " & Text.Combine(MissingRequired, ", ")
        else "OK",

    // Build optional selection safely
    OptionalToInclude = List.Intersect({OptionalColumns, AvailableColumns}),
    FinalColumnList = List.Combine({RequiredColumns, OptionalToInclude}),

    SelectedColumns = Table.SelectColumns(PromotedHeaders, FinalColumnList)
in
    SelectedColumns

This approach gives you fine-grained control: required columns fail loudly with a useful message, optional columns are included opportunistically.

Tip: The List.Difference(A, B) function returns elements in A that are not in B — perfect for finding missing columns. Get comfortable with List.Intersect, List.Difference, and List.Combine as they form the backbone of schema-adaptive logic.

---

Strategy 2: Adding Missing Columns with Default Values

The complement to "select only what exists" is "ensure all expected columns exist, even if you have to create them." This is particularly useful when downstream transformations — or your data model — expect a fixed schema.

Consider a scenario where you're consolidating sales data from multiple regional offices. Some regions track Discount as a column; others don't include it at all. You want a unified output where every row has a Discount column, defaulting to 0 when not provided.

let
    Source = Csv.Document(
        File.Contents("C:\Data\RegionSouth_Sales.csv"),
        [Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    // Ensure Discount column exists, default to 0 if missing
    AddDiscountIfMissing = if List.Contains(Table.ColumnNames(PromotedHeaders), "Discount")
        then PromotedHeaders
        else Table.AddColumn(PromotedHeaders, "Discount", each 0, type number),

    // Ensure SalesRep column exists, default to "Unassigned" if missing
    AddSalesRepIfMissing = if List.Contains(Table.ColumnNames(AddDiscountIfMissing), "SalesRep")
        then AddDiscountIfMissing
        else Table.AddColumn(AddDiscountIfMissing, "SalesRep", each "Unassigned", type text)

in
    AddSalesRepIfMissing

This works, but it gets verbose fast if you have many optional columns. Here's a more scalable version using a helper function pattern:

let
    Source = Csv.Document(
        File.Contents("C:\Data\RegionSouth_Sales.csv"),
        [Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    // Define expected optional columns and their defaults
    // Each record has: ColumnName, DefaultValue, ColumnType
    OptionalColumnDefaults = {
        [Name = "Discount",  Default = 0,             Type = type number],
        [Name = "SalesRep",  Default = "Unassigned",  Type = type text],
        [Name = "Notes",     Default = null,           Type = type text],
        [Name = "Approved",  Default = false,          Type = type logical]
    },

    // Function to add a column if it doesn't exist
    EnsureColumn = (tbl as table, colDef as record) as table =>
        if List.Contains(Table.ColumnNames(tbl), colDef[Name])
        then tbl
        else Table.AddColumn(tbl, colDef[Name], each colDef[Default], colDef[Type]),

    // Fold over the list, applying EnsureColumn for each optional column
    WithDefaults = List.Accumulate(
        OptionalColumnDefaults,
        PromotedHeaders,
        (state, current) => EnsureColumn(state, current)
    )

in
    WithDefaults

List.Accumulate is the key here — it works like a fold or reduce, threading the table through each iteration. Starting with PromotedHeaders as the seed, it applies EnsureColumn for every entry in OptionalColumnDefaults, passing the result of each step as input to the next. This means you can add 20 optional column definitions without writing 20 if blocks.

Warning: When using List.Accumulate for table transformations, keep in mind that each iteration creates a new table reference. On very large tables (millions of rows), this can become slow. For performance-sensitive scenarios, consider building the add-column operations as a batch, or handling defaults in your data model layer (DAX calculated columns) rather than in Power Query.

---

Strategy 3: Dynamic Type Assignment Without Breaking on New Columns

Table.TransformColumnTypes is the biggest source of schema fragility in GUI-generated queries. Let's fix it.

The goal is to apply data types to columns that exist, skip columns that don't, and leave unspecified columns in their current state. Here's the pattern:

let
    Source = Excel.Workbook(File.Contents("C:\Data\SalesExport.xlsx"), null, true),
    SalesTable = Source{[Item="Sales",Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(SalesTable, [PromoteAllScalars=true]),

    // Define intended types for columns you care about
    IntendedTypes = {
        {"OrderID",      Int64.Type},
        {"CustomerName", type text},
        {"Region",       type text},
        {"SalesAmount",  Currency.Type},
        {"OrderDate",    type date},
        {"Discount",     type number},
        {"SalesRep",     type text}
    },

    // Get currently available columns
    AvailableColumns = Table.ColumnNames(PromotedHeaders),

    // Filter type list to only include columns that exist
    SafeTypes = List.Select(
        IntendedTypes,
        (typeSpec) => List.Contains(AvailableColumns, typeSpec{0})
    ),

    // Apply only the safe subset of types
    TypedTable = Table.TransformColumnTypes(PromotedHeaders, SafeTypes)

in
    TypedTable

List.Select filters a list based on a condition. Here, it filters IntendedTypes to only include pairs where the column name (first element, accessed via {0}) exists in AvailableColumns. The result is a type specification list that's guaranteed to be valid for the current schema.

---

Strategy 4: Handling Column Renames — The Mapping Table Approach

Column renames are a particularly treacherous form of schema change because they don't add or remove information — they just change the label. A system upgrade changes "Cust_Name" to "CustomerName", and suddenly every downstream reference breaks.

The most robust solution is a mapping table that defines how to translate source column names into your standardized internal names. This completely decouples your transformation logic from whatever naming convention the source system uses today.

Create a dedicated query (or a separate sheet in your workbook) that acts as a column name dictionary:

// Query name: ColumnMappings
let
    MappingTable = Table.FromRows(
        {
            {"Cust_Name",    "CustomerName"},
            {"CustomerName", "CustomerName"},
            {"CUST_NM",      "CustomerName"},
            {"Ord_ID",       "OrderID"},
            {"OrderID",      "OrderID"},
            {"ORDER_ID",     "OrderID"},
            {"Sal_Amt",      "SalesAmount"},
            {"SalesAmount",  "SalesAmount"},
            {"SALES_AMT",    "SalesAmount"},
            {"Ord_Dt",       "OrderDate"},
            {"OrderDate",    "OrderDate"},
            {"ORDER_DATE",   "OrderDate"},
            {"Rgn",          "Region"},
            {"Region",       "Region"},
            {"Territory",    "Region"}
        },
        {"SourceName", "StandardName"}
    )
in
    MappingTable

Notice that each standard name maps to itself as well — this means the mapping works whether the source uses old names or new names. Now, in your main data query, you apply this mapping dynamically:

let
    Source = Excel.Workbook(File.Contents("C:\Data\SalesExport.xlsx"), null, true),
    SalesTable = Source{[Item="Sales",Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(SalesTable, [PromoteAllScalars=true]),

    // Get actual column names from the source
    SourceColumns = Table.ColumnNames(PromotedHeaders),

    // Get the mapping table
    Mappings = ColumnMappings,

    // Build a rename list: only for columns that exist in source AND have a mapping
    MappingRecord = Record.FromTable(
        Table.SelectColumns(Mappings, {"SourceName", "StandardName"})
    ),

    // Build rename pairs only for columns that exist and have a different target name
    RenamePairs = List.Select(
        List.Transform(
            SourceColumns,
            (col) =>
                let
                    mapped = Record.FieldOrDefault(MappingRecord, col, col)
                in
                    if mapped <> col then {col, mapped} else null
        ),
        (pair) => pair <> null
    ),

    // Apply renames (only if there are any renames to apply)
    RenamedTable = if List.Count(RenamePairs) > 0
        then Table.RenameColumns(PromotedHeaders, RenamePairs)
        else PromotedHeaders

in
    RenamedTable

Let's trace through what's happening. Record.FromTable converts the two-column mapping table into a record where each SourceName becomes a field. Record.FieldOrDefault(MappingRecord, col, col) looks up a column name and returns the mapped name — or the original name if no mapping exists (the third argument is the fallback). The List.Transform then builds rename pairs, filtering out nulls (columns that need no renaming) with List.Select.

Tip: Store your ColumnMappings table in a separate Excel sheet or a SharePoint list that non-technical stakeholders can edit. This way, when the source system renames a column, a business analyst can update the mapping table without touching M code. You've turned a code change into a data change.

---

Strategy 5: Schema-Agnostic Append and Merge Operations

When you're combining data from multiple sources — appending monthly files, merging regional exports — schema differences between files are especially common. Power Query's Table.Combine function is your friend here, but it needs the right configuration.

By default, Table.Combine (what the GUI uses for "Append Queries") handles mismatched schemas using a MissingField option. Let's look at a robust pattern for appending a folder of CSV files where different files may have different columns:

let
    // Load all CSV files from a folder
    Source = Folder.Files("C:\Data\MonthlySales\"),

    // Filter to CSV files only
    CSVFiles = Table.SelectRows(Source, each [Extension] = ".csv"),

    // Function to load and normalize a single file
    LoadAndNormalize = (filePath as text) as table =>
        let
            RawData = Csv.Document(
                File.Contents(filePath),
                [Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]
            ),
            WithHeaders = Table.PromoteHeaders(RawData, [PromoteAllScalars=true]),

            // Apply column mapping
            SourceCols = Table.ColumnNames(WithHeaders),
            RenamePairs = List.Select(
                List.Transform(
                    SourceCols,
                    (col) =>
                        let mapped = Record.FieldOrDefault(
                            Record.FromTable(Table.SelectColumns(ColumnMappings, {"SourceName", "StandardName"})),
                            col, col
                        )
                        in if mapped <> col then {col, mapped} else null
                ),
                (x) => x <> null
            ),
            Renamed = if List.Count(RenamePairs) > 0
                then Table.RenameColumns(WithHeaders, RenamePairs)
                else WithHeaders,

            // Add source file identifier
            WithSource = Table.AddColumn(Renamed, "SourceFile", each filePath, type text)
        in
            WithSource,

    // Apply the function to each file
    AllTables = Table.AddColumn(
        CSVFiles,
        "ParsedData",
        each LoadAndNormalize([Folder Path] & [Name]),
        type table
    ),

    // Combine all tables — MissingField.UseNull fills gaps with null
    Combined = Table.Combine(
        AllTables[ParsedData],
        [MissingField = MissingField.UseNull]
    )

in
    Combined

The MissingField.UseNull option tells Table.Combine to create null values for any column that exists in some tables but not others, rather than throwing an error. This is the correct behavior for schema-tolerant appends — you get all columns that appear in any file, with nulls where a file didn't have that column.

Warning: MissingField.UseNull means your combined table's schema is the union of all source schemas. If File A has 10 columns and File B has 12 different columns, you'll end up with up to 22 columns, most of which are null for half the rows. Build your column selection and type enforcement steps after the combine, once you know the full output schema.

---

Strategy 6: Using `Table.Schema` for Runtime Schema Inspection

Power Query exposes a powerful built-in function, Table.Schema, that returns a table describing every column — its name, position, type, and nullability. This is the diagnostic tool you reach for when you need to understand what you're working with at runtime.

let
    Source = Excel.Workbook(File.Contents("C:\Data\SalesExport.xlsx"), null, true),
    SalesTable = Source{[Item="Sales",Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(SalesTable, [PromoteAllScalars=true]),

    // Inspect the schema
    SchemaInfo = Table.Schema(PromotedHeaders)

in
    SchemaInfo

Run this in a separate diagnostic query and you'll see columns like Name, Position, TypeName, Kind, IsNullable, and more. This is invaluable for debugging schema issues.

More practically, you can use Table.Schema to build adaptive type enforcement. Here's a scenario: you receive files from a vendor who sometimes sends OrderDate as a text column (when the file has no data) and sometimes as a proper date. You want to handle both cases:

let
    Source = Csv.Document(
        File.Contents("C:\Data\SalesExport.csv"),
        [Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    // Check the actual type of OrderDate before transforming it
    SchemaInfo = Table.Schema(PromotedHeaders),
    OrderDateRow = Table.SelectRows(SchemaInfo, each [Name] = "OrderDate"),
    OrderDateTypeName = if Table.RowCount(OrderDateRow) > 0
        then OrderDateRow{0}[TypeName]
        else "Missing",

    // Apply the right transformation based on actual type
    ProcessedDates = if OrderDateTypeName = "Missing"
        then Table.AddColumn(PromotedHeaders, "OrderDate", each null, type date)
        else if OrderDateTypeName = "Date"
            then PromotedHeaders  // Already correct type, no action needed
            else Table.TransformColumns(
                PromotedHeaders,
                {"OrderDate", each if _ = null or _ = ""
                    then null
                    else Date.From(_),
                type date}
            )

in
    ProcessedDates

This pattern is admittedly verbose, but it's honest about what's happening: you're inspecting the actual state of the data before deciding how to transform it, rather than assuming a fixed format.

---

Hands-On Exercise: Building a Schema-Resilient Sales Pipeline

Let's put all of these strategies together in a realistic exercise. You'll build a Power Query solution that ingests monthly sales exports from a folder. The files have evolved over 18 months: early files used different column names, later files added new columns, and some regional files have missing optional columns.

Setup: Create three CSV files to simulate the scenario.

File 1: 2023_01_Sales.csv (older format, fewer columns)

Ord_ID,Cust_Name,Rgn,Sal_Amt,Ord_Dt
1001,Northwind Traders,West,4250.00,2023-01-15
1002,Contoso Ltd,East,1875.50,2023-01-18
1003,Fabrikam Inc,Central,9300.00,2023-01-22

File 2: 2023_09_Sales.csv (transitional format, mix of old and new names)

OrderID,CustomerName,Region,SalesAmount,OrderDate,SalesRep
2001,Northwind Traders,West,5100.00,2023-09-03,Maria Santos
2002,Tailspin Toys,East,2200.00,2023-09-07,James Okafor
2003,Contoso Ltd,Central,7650.00,2023-09-11,Maria Santos

File 3: 2024_03_Sales.csv (current format, full columns)

OrderID,CustomerName,Region,SalesAmount,OrderDate,SalesRep,Discount,Channel
3001,Northwind Traders,West,6000.00,2024-03-02,Maria Santos,0.05,Direct
3002,Fabrikam Inc,East,3400.00,2024-03-08,James Okafor,0.00,Partner
3003,Tailspin Toys,Central,8900.00,2024-03-15,Chen Wei,0.10,Direct

Step 1: Create your ColumnMappings query with the mapping table from Strategy 4 above.

Step 2: Create your main SalesPipeline query using the folder loading pattern from Strategy 5, adding the EnsureColumn logic from Strategy 2 to guarantee Discount and SalesRep exist in the output with appropriate defaults.

Step 3: After combining all files, apply safe type assignments using the pattern from Strategy 3.

Step 4: Add a final step that selects only your standardized output columns in a fixed order, using List.Intersect to ensure the selection is always valid:

// Final output schema
OutputColumns = {"OrderID", "CustomerName", "Region", "SalesAmount", "OrderDate",
                  "SalesRep", "Discount", "Channel", "SourceFile"},

AvailableFinal = Table.ColumnNames(Combined),
FinalSelection = List.Intersect({OutputColumns, AvailableFinal}),
FinalTable = Table.SelectColumns(Combined, FinalSelection)

When you run this against all three files, you should get a unified table with 9 rows. The 2023_01 file rows will have null for SalesRep and 0 for Discount (from your defaults), and null for Channel (which you didn't define a default for, since it wasn't in OptionalColumnDefaults). The column names will all be in your standardized format regardless of what the source file used.

This is a working, schema-resilient pipeline. Add a fourth CSV with a completely different column name for Region (say, "Territory"), add it to your mapping table, and the pipeline adapts with no other code changes.

---

Common Mistakes & Troubleshooting

Mistake 1: Building the mapping record inside a loop

In the folder-loading pattern, the inner function LoadAndNormalize calls Record.FromTable(Table.SelectColumns(ColumnMappings, ...)) on every file. This means the mapping table is re-evaluated for every file, which is inefficient. Extract the MappingRecord computation outside the function, assign it to a query-level variable, and reference that variable inside the function.

Mistake 2: Forgetting that List.Intersect requires a list of lists

A very common error:

// WRONG — this will error
List.Intersect(DesiredColumns, AvailableColumns)

// CORRECT — wrap both in an outer list
List.Intersect({DesiredColumns, AvailableColumns})

The function signature is List.Intersect(lists as list) — it takes a single list containing the lists to intersect.

Mistake 3: Applying Table.Schema to a large table for every row in a loop

Table.Schema is a metadata operation and is generally fast, but calling it inside List.Accumulate on a large table can still add up. Use it once at the beginning of your query to capture schema information, then reference that captured information in subsequent steps.

Mistake 4: Trusting that column order is stable

Some systems export columns in alphabetical order after a schema migration. Others sort columns by creation date. Never rely on positional column access (like Source{0}) for anything other than row-level record access. Always reference columns by name.

Mistake 5: Silent data loss from Table.SelectColumns on new columns

If a source adds a genuinely important new column and your List.Intersect pattern doesn't include it in DesiredColumns, it will be silently dropped. Build a monitoring step that logs or alerts when new unexpected columns appear:

// Log unexpected new columns for review
UnexpectedColumns = List.Difference(AvailableColumns, DesiredColumns),
LogUnexpected = if List.Count(UnexpectedColumns) > 0
    then Table.AddColumn(SelectedColumns, "__UnexpectedColumnsDetected",
         each Text.Combine(UnexpectedColumns, ", "), type text)
    else SelectedColumns

This adds a metadata column to your output when surprises appear, making them visible without breaking the pipeline.

Troubleshooting schema errors:

When you hit The column 'X' of the table wasn't found, the fastest diagnosis is:

1. Add a temporary step immediately before the failing step: = Table.ColumnNames(PreviousStep) — this shows you exactly what columns are actually present.
2. Check if the column was dropped in an earlier step, or was never in the source to begin with.
3. If it's a rename issue, use Table.Schema(PreviousStep) to see actual type names — sometimes a column exists but with a slightly different case ("orderid" vs "OrderID").

Tip: M is case-sensitive for column names. "SalesAmount" and "salesamount" are different columns. When building your ColumnMappings table, include common casing variants, or normalize all column names to a consistent case early in your pipeline using Table.TransformColumnNames(tbl, Text.Proper).

---

Summary & Next Steps

Schema drift is a fact of life in data engineering. Systems get upgraded, exports get modified, and data teams don't always communicate changes upstream. The difference between a fragile pipeline and a resilient one comes down to a handful of deliberate design choices:

• Use List.Intersect and List.Difference to make column selection and type assignment adaptive rather than fixed.
• Use List.Accumulate with an EnsureColumn helper to add missing optional columns with sensible defaults, keeping your output schema consistent.
• Maintain a mapping table that translates source column names to your standardized names, and make it editable by non-technical team members.
• Use MissingField.UseNull in Table.Combine for schema-tolerant appends.
• Use Table.Schema and Record.FieldOrDefault for runtime schema inspection and conditional logic.
• Separate required columns from optional ones — required columns should fail loudly and clearly, optional ones should degrade gracefully.

The architecture principle underlying all of these strategies is the same: make your transformations depend on data about the schema, not on the schema itself being fixed. When you do that, adding a new source column becomes a non-event rather than an incident.

Where to go next:

• Explore query parameters to make your column lists and mappings configurable from the workbook level, enabling end users to adjust behavior without opening the Advanced Editor.
• Study custom connector development if you own the source system — defining a fixed output schema at the connector level eliminates drift before it reaches Power Query.
• Investigate Power Query diagnostics (Diagnostics.ActivityId, query folding indicators) to understand the performance cost of the adaptive patterns you've built — some of these patterns prevent query folding and should be used knowingly.
• Consider moving mapping logic to a database view or a Power Automate flow that normalizes the schema before Power Query ever sees the data — sometimes the right answer is to fix schema drift at the source, not absorb it in the transformation layer.