Power QueryExercises

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

OptionEffectUse when
MissingField.Error (default)The step failsThe column is required
MissingField.IgnoreThe missing name is skippedThe column is optional and the output may omit it
MissingField.UseNullThe column is created, full of nullThe output must always have the column
Table.SelectColumns(
    #table({"OrderID", "Qty"}, {{"A-1", 2}}),
    {"OrderID", "Qty", "Discount"},
    MissingField.UseNull
)

Result

OrderIDQtyDiscount
A-12null

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

Older exports have no Discount column; newer ones do. UseNull gives every refresh the same three columns.

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
    Net

Result

OrderIDAmountNet
A-1100100
A-24040

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.
Functions in this lesson:Table.SelectColumnsTable.HasColumnsTable.ColumnNames

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