Power QueryExercises

Handle changing column headers from a source system in Power Query

Troubleshooting · By Tahir Nas · Examples run in our simulator · Updated

10 min read

When a source system renames a column, every step that names it fails with "The column ... of the table wasn't found", because Power Query writes column names into the steps as fixed text. The fix is to stop depending on exact names: clean the headers first, rename from a mapping table that lists old and new spellings, check for the columns you need straight after those two steps so the error is clear, and choose the rest by rule.

Why a renamed header breaks the query

Every step generated by the editor that touches a column stores its name as text. A source that sends Order ID instead of OrderID makes the first step that mentions the old name fail:

let
    Source = #table({"Order ID", "Order Date", "Amount"}, {{"A1", #date(2026, 10, 1), 120}}),
    Typed = Table.TransformColumnTypes(Source, {{"OrderDate", type date}})
in
    Typed

Result: an error

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

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The error is "The column 'OrderDate' of the table wasn't found." Microsoft's guide to errors uses this exact situation as its example of a step-level error: a step refers to a column name that no longer exists after the source changed (Dealing with errors, Microsoft Learn). The site's page on the column of the table wasn't found walks through reading the error.

The renamed header is rarely the only change. Source systems also add columns, drop optional ones, change capitalisation or spacing, and add a new period column every month. Each needs a slightly different defence, and the glossary calls the whole problem schema drift. The post on why Excel reports break every month puts these changes alongside the other monthly failures.

Step 1: clean every header before anything else

Many "renames" are really noise: a leading space, different capitals, a trailing tab. Table.TransformColumnNames applies a function to every column name (Table.TransformColumnNames, Microsoft Learn), so it also covers columns that arrive in later refreshes:

let
    Source = #table({" Order ID", "ORDER DATE ", "amount"}, {{"A1", #date(2026, 10, 1), 120}}),
    Cleaned = Table.TransformColumnNames(Source, each Text.Lower(Text.Trim(_)))
in
    Cleaned

Result

order idorder dateamount
A12026-10-01120

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The headers become order id, order date and amount. Lower case is a deliberate choice: it gives every spelling one predictable form, which the mapping in Step 2 then turns into the names you want. Text.Proper looks tidier but rewrites interior capitals, so a correct OrderID would become Orderid. Put this step directly after the source (or after Promote Headers). Column names in M are case-sensitive, so without it Amount and amount are different columns as far as a later step is concerned. The exercise clean every column name, including future ones practises the pattern.

Step 2: rename from a mapping table

When a column really is renamed, keep a small table of old and new names and let Table.RenameColumns apply it to the cleaned headers. By default a rename of a missing column raises an error; the optional missingField argument changes that, and MissingField.Ignore skips renames whose source column isn't there (Table.RenameColumns, Microsoft Learn). That lets one mapping list every spelling a column has ever had:

let
    Source = #table({"Cust No ", "Order Date", "NET"}, {{"C1", #date(2026, 10, 1), 120}}),
    Cleaned = Table.TransformColumnNames(Source, each Text.Lower(Text.Trim(_))),
    Map = #table({"From", "To"}, {{"cust no", "CustomerID"}, {"customer number", "CustomerID"}, {"order date", "OrderDate"}, {"net", "Amount"}, {"net amount", "Amount"}}),
    Renamed = Table.RenameColumns(Cleaned, Table.ToRows(Map), MissingField.Ignore)
in
    Renamed

Result

CustomerIDOrderDateAmount
C12026-10-01120

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The result has CustomerID, OrderDate and Amount. Next month the source can send Customer Number, net amount or NET and the query still produces the same columns, because the mapping's From values are written in their cleaned, lower-case form. Two cautions:

  • If a file ever contains both spellings at once (say net and net amount), both renames apply and the step fails because two columns would share a name. That's a real problem worth an error.
  • In a workbook, the mapping can live in an Excel table that a colleague maintains, so a new spelling is a row added to a sheet rather than an edit to the query. The post on combining exports from several ad platforms uses one mapping table for several sources.

The exercises accept both old and new names for a column and rename columns from a mapping table cover each half of this.

Step 3: check the schema before using the columns

Some changes should stop the refresh: a required column that's gone, not renamed. Check for it straight after the clean and rename steps, so the error names the problem instead of surfacing three steps later, and only fires for columns those steps couldn't recover:

let
    Expected = {"CustomerID", "OrderDate", "Amount"},
    Source = #table({"CustomerID", "OrderDate"}, {{"C1", #date(2026, 10, 1)}}),
    Missing = List.Difference(Expected, Table.ColumnNames(Source)),
    Checked = if List.IsEmpty(Missing) then Source else error Error.Record("SchemaCheck", "Expected columns are missing", Missing)
in
    Checked

Result: an error

SchemaCheck: Expected columns are missing

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The refresh fails with "Expected columns are missing" and the details list Amount, which tells whoever opens the file exactly what changed. The lesson Validate data and fail loudly covers other checks of this kind.

Step 4: choose columns by rule, not by list

Remove Columns and Choose Columns store the names they were given. If the source adds a helper column, Choose Columns ignores it (good), but if a chosen column disappears, the step fails. For a CSV source, also check the Source step's Columns option, which drops columns beyond a fixed count without an error. Two patterns help.

Build the list from the actual names when you want "everything except":

let
    Source = #table({"Product", "Sales Oct 2026", "tmp_check", "Sales Nov 2026"}, {{"Kite", 10, "x", 12}}),
    Keep = List.Select(Table.ColumnNames(Source), each not Text.StartsWith(_, "tmp_")),
    Result = Table.SelectColumns(Source, Keep)
in
    Result

Result

ProductSales Oct 2026Sales Nov 2026
Kite1012

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The tmp_check column is gone and every other column is kept, including any that appear later. The exercise remove every helper column whose name starts with tmp_ is this pattern.

When a column is optional, say so in the step. Table.SelectColumns accepts MissingField.UseNull, which creates the column filled with null when it's missing (Table.SelectColumns, Microsoft Learn):

let
    Source = #table({"CustomerID", "Order Date"}, {{"C1", #date(2026, 10, 1)}}),
    Picked = Table.SelectColumns(Source, {"CustomerID", "Order Date", "Amount"}, MissingField.UseNull)
in
    Picked

Result

CustomerIDOrder DateAmount
C12026-10-01null

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Amount appears with null instead of the step failing. The lesson When a column goes missing: MissingField options compares the options, and Choose Columns vs Remove Columns explains which of the two is safer when new columns arrive.

Step 5: unpivot columns whose names are data

A source that adds a column every month (Sales Oct 2026, Sales Nov 2026, and so on) isn't renaming columns; it's storing data in the headers. A step that names those columns either misses each new month or breaks when an old month drops off the export. Unpivot every column except the ones that identify the row:

let
    Source = #table({"Product", "Sales Oct 2026", "Sales Nov 2026"}, {{"Kite", 10, 12}}),
    Long = Table.UnpivotOtherColumns(Source, {"Product"}, "Month", "Sales")
in
    Long

Result

ProductMonthSales
KiteSales Oct 202610
KiteSales Nov 202612

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The result has one row per product and month: Kite, Sales Oct 2026, 10 and Kite, Sales Nov 2026, 12. Because the step names only Product, a December column is picked up automatically. The guide Unpivot columns in Power Query explains why "Unpivot Other Columns" is the refresh-proof choice, and the lesson Unpivot, then turn header text into real dates finishes the job.

Step 6: make the type step tolerant, or generate it

The Changed Type step that Power Query adds automatically lists every column by name, so it's usually the first step to break. You have three options, from simplest to most flexible:

  1. Delete the automatic step and write one type step after your renames, naming only the columns you depend on.
  2. Pass a record as the third argument to Table.TransformColumnTypes. Microsoft's reference says the record can contain Culture and MissingField, and that without MissingField a missing column raises an error (Table.TransformColumnTypes, Microsoft Learn). The page's own example uses MissingField.UseNull.
  3. Generate the list of type pairs from the column names, for example every column whose name starts with "Sales" becomes a number:
let
    Source = #table({"Product", "Sales Oct 2026", "Sales Nov 2026"}, {{"Kite", "10", "12"}}),
    SalesColumns = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "Sales")),
    Typed = Table.TransformColumnTypes(Source, List.Transform(SalesColumns, each {_, type number})),
    Schema = Table.SelectColumns(Table.Schema(Typed), {"Name", "TypeName"})
in
    Schema

Result

NameTypeName
ProductAny.Type
Sales Oct 2026Number.Type
Sales Nov 2026Number.Type

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Both Sales columns come out as Number.Type, and a December column would too, without editing the step. (Product stays Any.Type here because the example only types the Sales columns.)

The exercise set types without failing when an optional column is missing practises the second option, and the lesson Dynamic steps that survive new columns covers the third.

What about renaming by position?

Some exports have headers you can't predict at all, such as a first column called Report run 05/11/2026. Renaming by position handles that:

let
    Source = #table({"Report run 05/11/2026", "Column2", "Column3"}, {{"A1", 120, "x"}, {"A2", 80, "y"}}),
    Names = Table.ColumnNames(Source),
    Renamed = Table.RenameColumns(Source, List.Zip({List.FirstN(Names, 2), {"OrderID", "Amount"}}))
in
    Renamed

Result

OrderIDAmountColumn3
A1120x
A280y

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The first two columns become OrderID and Amount, and Column3 is left alone; List.FirstN keeps the two lists the same length when the export has more columns than you rename. Steps that use names don't care about column order, so a source that only reorders its columns needs no fix at all. Use position only when the column order is the one thing the source guarantees. If the order can change, position-based renames give columns the wrong meaning without any error, which is worse than a failed refresh. The exercise fix a query that names columns by position shows how that goes wrong.

A refresh-proof order of steps

OrderStepDefends against
1Source, then Promote HeadersStarting point
2Clean names (Table.TransformColumnNames)Spacing and capitalisation changes
3Rename from mapping (MissingField.Ignore)Known renames
4Schema checkRequired columns that are really gone
5Select or remove columns by ruleAdded and optional columns
6Unpivot period columnsA new column every month
7Types, naming only the columns you rely on (including the unpivoted value column)Type step breaking on new names

The refresh-proof checklist has more checks in the same spirit.

Frequently asked questions

Should I turn off automatic type detection?

It's an option rather than a requirement. Automatic detection adds a Changed Type step for unstructured sources, and it can be switched off in the query options (Data types in Power Query, Microsoft Learn). If you keep it, review the step it creates and trim it to the columns you depend on, or replace it with your own type step after the renames.

How do I find out which columns changed?

Compare the current names with the names you expect. List.Difference(Expected, Table.ColumnNames(Source)) lists the missing ones, and swapping the arguments lists the new ones. Keep both lists in a scratch step while you investigate.

Can a mapping table handle columns that are split or merged?

No. A mapping only renames. If one source column becomes two, or two become one, the query needs a real transformation step, and the schema check is what tells you it's needed.

To practise all of this on files that change shape, the Power Query Exercises project refresh-resilient analyst case gives you depot files with different column names, orders and extra columns, plus a column map to drive the renames.

Sources

About the author

Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.

Keep reading

Practise Power Query in the browser with graded exercises checked against fresh data.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.