When a column goes missing: MissingField options
Handle optional and missing columns deliberately instead of hiding schema problems.
~9 min read · intermediate · Module 4: Working with columns
By Tahir Nas · Examples run in our simulator · Updated
Before this: Choose Columns vs Remove Columns
By default, a step that names a column the table doesn't have raises Expression.Error: The column 'X' of the table wasn't found. That error is useful: it tells you the source changed. Before silencing it, decide whether the missing column is expected.
Table.SelectColumns(
#table({"OrderID", "Qty"}, {{"A-1", 2}}),
{"OrderID", "Qty", "Discount"}
)Result: an error
Expression.Error: The column 'Discount' of the table wasn't found.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Three MissingField options
| Option | Effect | Use when |
|---|---|---|
MissingField.Error (default) | The step fails | The column is required |
MissingField.Ignore | The missing name is skipped | The column is optional and the output may omit it |
MissingField.UseNull | The column is created, full of null | The output must always have the column |
Table.SelectColumns(
#table({"OrderID", "Qty"}, {{"A-1", 2}}),
{"OrderID", "Qty", "Discount"},
MissingField.UseNull
)Result
| OrderID | Qty | Discount |
|---|---|---|
| A-1 | 2 | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The same third argument exists on Table.RemoveColumns, Table.RenameColumns and Table.ReorderColumns, so a rename of an optional column can be made tolerant too.
Test for a column explicitly
When the logic should differ, branch on Table.HasColumns. Here a discount is applied only when the source provides one:
let
Source = #table({"OrderID", "Amount"}, {{"A-1", 100}, {"A-2", 40}}),
Net = if Table.HasColumns(Source, "Discount")
then Table.AddColumn(Source, "Net", each [Amount] - [Discount], type number)
else Table.AddColumn(Source, "Net", each [Amount], type number)
in
NetResult
| OrderID | Amount | Net |
|---|---|---|
| A-1 | 100 | 100 |
| A-2 | 40 | 40 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
When not to relax the check
If a required column disappears because the source renamed it (Qty became Quantity), MissingField.UseNull turns a clear error into a column of nulls, and the report quietly shows zero sales. Keep the default for required columns, and fix renames with an explicit rename step. Add MissingField.Ignore or UseNull only for columns that are genuinely optional, and write a comment saying so.
Where to find this in Excel
There's no option for this in the dialogs. Add the MissingField argument to the step's formula in the formula bar or in the Advanced Editor (View > Advanced Editor).
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Use Power Query to transform data (the Power Query editor) (Microsoft Learn).
Common mistakes
- Adding MissingField.Ignore everywhere after one failed refresh, which hides the next real schema change.
- Using UseNull for a required measure column, so a renamed source column becomes a column of nulls.
- Testing for a column with a case-different name: Table.HasColumns is case-sensitive.
Excel, Power BI and the simulator
- The editor never adds MissingField options for you; you add them in the formula bar or Advanced Editor in both Excel and Power BI Desktop.
- In Power BI, a column that disappears from a loaded query also breaks visuals and measures that use it, so a loud error at refresh is usually preferable.
- The simulator supports MissingField.Error, Ignore and UseNull on the column functions that accept them.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Working with columns quiz