[Excel/Power Query] Error: #FIELD! (field not found) — How to Fix After Renames or Source Changes

Summary

The #FIELD! error in Power Query means a step is referencing a column that no longer exists at that point in your query. Typical triggers are source-file schema changes (renamed/deleted headers), case differences (Amount vs amount), or hard-coded column lists in steps like Table.SelectColumns or Table.ExpandTableColumn. The fix is to update the step to the current name, normalize headers early, or guard against optional columns using dynamic checks.

Context

Power Query (M) is case-sensitive for field names. When you import data, steps capture the exact header strings present at that time. If later refreshes rename or drop a column, any downstream step that expects the old name fails with Expression.Error: The field 'X' of the record wasn't found. The safest pattern is: (1) standardize/rename headers immediately after source, (2) avoid brittle hard-coded lists when possible, and (3) add fallbacks for optional columns.

Probable Cause

Column renamed or deleted at the source. CSV/Excel/DB schema changed after the query was authored.
Header normalization changed casing/spaces. Upstream Promote Headers or cleanup altered exact field text.
Hard-coded selectors. Table.SelectColumns / Table.ExpandTableColumn referencing names that no longer exist.
Dynamic/dated headers. Columns like Sales_2025-10 that vary between refreshes.

Quick Fix

Locate the failing step. In Power Query Editor, click each item in Applied Steps until the error appears; note the exact column name mentioned in the formula bar/message.
Update the reference to the new name. Edit the step to match current headers, or add a robust rename step just after the source.

# Normalize/rename early; ignore if missing
= Table.RenameColumns(Source, {{"Amt","Amount"}}, MissingField.Ignore)

Guard optional columns with dynamic checks. Use Table.ColumnNames, Table.HasColumns, or try … otherwise.

# Select only columns that exist
let
  cols = {"Customer","Amount","Date"},
  existing = List.Intersect({cols, Table.ColumnNames(Source)}),
  safe = Table.SelectColumns(Source, existing, MissingField.Ignore)
in
  safe

Fix expansions after schema changes. Recreate the Expand step or compute the list of fields dynamically.

# Expand only fields that are present today
available = List.Intersect({{"Amount","Tax","Net"}, Table.ColumnNames(NestedTable{0}[Data])}),
Expanded = Table.ExpandTableColumn(Source, "Data", available, available)

Full Example

Scenario: A step fails with Expression.Error: The field 'Amount' of the record wasn't found. The source renamed Amount → TotalAmount.

# Failing step (before)
#"Changed Type" = Table.TransformColumnTypes(Prev, {{"Amount", Int64.Type}})

# Fix 1: Centralized, tolerant rename near the Source
HeadersFixed = Table.RenameColumns(Prev, {{"TotalAmount","Amount"}}, MissingField.Ignore),
#"Changed Type" = Table.TransformColumnTypes(HeadersFixed, {{"Amount", Int64.Type}})

# Fix 2: Dynamic presence check to avoid hard failure
HasAmount = Table.HasColumns(Prev, "Amount"),
SafeTyped = if HasAmount then
    Table.TransformColumnTypes(Prev, {{"Amount", Int64.Type}})
  else
    Prev

Optional column fallback: If a column can be absent, add a default safely.

# Add "Amount" if missing, defaulting to 0
SafeAmount = if Table.HasColumns(Prev, "Amount")
  then Prev
  else Table.AddColumn(Prev, "Amount", each 0, Int64.Type)

Case normalization: Standardize headers once to prevent case-related breaks.

# Lowercase all headers then rename canonical names once
Lower = Table.TransformColumnNames(Source, Text.Lower),
Canon = Table.RenameColumns(Lower, {{"totalamount","amount"}, {"customername","customer"}} , MissingField.Ignore)

Pitfalls & Debug

Symptom → Table.SelectColumns breaks after a refresh. Fix → Build existing = List.Intersect(...) and select only present columns; pass MissingField.Ignore.
Symptom → Expand step fails post-schema update. Fix → Delete and re-create the Expand (it records current fields), or compute the expand list with Table.ColumnNames.
Symptom → Random capitalization changes break steps. Fix → Normalize headers (Table.TransformColumnNames) immediately after source.
Symptom → Dynamic headers by date/version. Fix → Map patterns to stable names (e.g., use Text.StartsWith to choose the latest then rename).
Symptom → Record.Field reference fails. Fix → Use Record.HasFields(row, "Amount") before accessing; supply a default if missing.

Validation & Next Steps

Confirm that downstream steps no longer reference missing names and that data types are applied successfully.

# Inspect available headers at the failing point
Table.ColumnNames(#"Previous Step")

# Quick schema profile
Table.Profile(#"Previous Step")

Use View → Query Dependencies to see where a rename/drop occurred. For long-term resilience, centralize schema normalization (trim, case, canonical renames) in one early step and reference that canonical layer everywhere else.