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
TypedResult: 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
CleanedResult
| order id | order date | amount |
|---|---|---|
| A1 | 2026-10-01 | 120 |
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
RenamedResult
| CustomerID | OrderDate | Amount |
|---|---|---|
| C1 | 2026-10-01 | 120 |
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
netandnet 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
CheckedResult: 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
ResultResult
| Product | Sales Oct 2026 | Sales Nov 2026 |
|---|---|---|
| Kite | 10 | 12 |
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
PickedResult
| CustomerID | Order Date | Amount |
|---|---|---|
| C1 | 2026-10-01 | null |
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
LongResult
| Product | Month | Sales |
|---|---|---|
| Kite | Sales Oct 2026 | 10 |
| Kite | Sales Nov 2026 | 12 |
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:
- Delete the automatic step and write one type step after your renames, naming only the columns you depend on.
- Pass a record as the third argument to
Table.TransformColumnTypes. Microsoft's reference says the record can containCultureandMissingField, and that withoutMissingFielda missing column raises an error (Table.TransformColumnTypes, Microsoft Learn). The page's own example usesMissingField.UseNull. - 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
SchemaResult
| Name | TypeName |
|---|---|
| Product | Any.Type |
| Sales Oct 2026 | Number.Type |
| Sales Nov 2026 | Number.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
RenamedResult
| OrderID | Amount | Column3 |
|---|---|---|
| A1 | 120 | x |
| A2 | 80 | y |
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
| Order | Step | Defends against |
|---|---|---|
| 1 | Source, then Promote Headers | Starting point |
| 2 | Clean names (Table.TransformColumnNames) | Spacing and capitalisation changes |
| 3 | Rename from mapping (MissingField.Ignore) | Known renames |
| 4 | Schema check | Required columns that are really gone |
| 5 | Select or remove columns by rule | Added and optional columns |
| 6 | Unpivot period columns | A new column every month |
| 7 | Types, 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
- Dealing with errors, Microsoft Learn (learn.microsoft.com)
- Table.TransformColumnNames, Microsoft Learn (learn.microsoft.com)
- Table.RenameColumns, Microsoft Learn (learn.microsoft.com)
- Table.SelectColumns, Microsoft Learn (learn.microsoft.com)
- Table.TransformColumnTypes, Microsoft Learn (learn.microsoft.com)
- Data types in Power Query, Microsoft Learn (learn.microsoft.com)
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
- How to refresh Power Query automatically in ExcelRefresh Power Query when an Excel workbook opens, every few minutes or on a timer with VBA, and make the queries safe to refresh when nobody is watching.
- Power BI Desktop or dataflows: where should Power Query steps live?Decide which Power Query steps belong in a Power BI dataflow and which stay in the Desktop file, with a comparison table, tested M and a checklist for moving.
- Power Query in Excel for Mac: what works and what doesn'tWhat Power Query in Excel for Mac can do, per Microsoft's documentation and Excel team posts: the editor, data sources, file paths, sharing with Windows.
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.