“The field ‘X’ of the record wasn’t found” during Step Expansion

The error "Expression.Error: The field '[FieldName]' of the record wasn't found" occurs in Power Query when expanding a column of records or nested objects (such as JSON payloads, API responses, or related database tables). Power Query expects a specific field name that was hardcoded into the step when the query was originally created, but that field is absent from one or more incoming records in the current refresh.

Fast Fix

Open the Advanced Editor and find the failing step (usually Table.ExpandRecordColumn). Look for the missing field name in the column list argument and either delete it or correct its spelling to match the current schema. To prevent future errors from sparse data, wrap the step using Record.FieldOrDefault or build a dynamic expansion pattern using Table.ColumnNames.

---

Diagnostic Snapshot

Property	Details
Error Type	Expression.Error
Full Syntax	Expression.Error: The field '[X]' of the record wasn't found.
Common Trigger	Ingesting sparse JSON/REST APIs, renamed columns upstream, or changed database schemas
Core Vulnerability	Table.ExpandRecordColumn() explicitly hardcodes all expanded field names in the M-script
Case Sensitivity	Strict. A record containing {"id": 101} will fail if the code looks for "ID".

---

Why Record Expansion Breaks

When you ingest structured objects like JSON or OData, Power Query parses individual rows as M Records (key-value pairs). When you click the expand icon on a column header, Power Query automatically writes an explicit, static list of keys to pull from those records into the formula:

= Table.ExpandRecordColumn(Source, "CustomerRecord", {"ID", "Name", "Email"}, {"ID", "Name", "Email"})

This static declaration causes failures under two common scenarios:

Record Row 1:  [ID = 101, Name = "Alice", Email = "a@test.com"]  --> SUCCEEDS
Record Row 2:  [ID = 102, Name = "Bob"]                          --> CRASH: Field 'Email' not found!

1. Sparse Records (Missing Keys in APIs): Modern web APIs often omit keys entirely when a value is null to conserve bandwidth. If Record #1 has an "Email" key, but Record #2 completely omits the key instead of returning null, Power Query halts evaluation because it cannot find the requested field.
2. Schema Drift Upstream: If an upstream system renames a field (e.g., changing "PostalCode" to "ZipCode"), the static expansion list still attempts to find "PostalCode" in every row.

---

Step-by-Step Resolution

Step 1: Locate the Missing Field in M-Code

1. In Excel, go to Data > Queries & Connections and double-click the failing query.
2. Select the broken step in the Applied Steps pane (typically named Expanded [ColumnName]).
3. Look at the formula bar or click Home > Advanced Editor: #"Expanded UserInfo" = Table.ExpandRecordColumn( PreviousStep, "UserInfo", {"UserID", "Department", "Phone"}, {"UserID", "Department", "Phone"} )
4. Check which field was flagged in the error message (e.g., "Phone").
5. If the field was deleted upstream and is no longer needed, simply delete "Phone" from both lists inside the formula and press Enter.

---

Step 2: Safely Expand Sparse Records with Default Nulls

If your data source (like a REST API) returns sparse records where optional fields are omitted, use Record.FieldOrDefault inside a custom column transformation instead of the rigid Table.ExpandRecordColumn.

Instead of expanding directly, transform the column to inject missing keys with a default null value before expanding:

1. Open the Advanced Editor.
2. Add a step before expansion that standardizes the records:(Setting the 4th argument of Record.AddField to true tells Power Query to ignore the step if the field already exists in the record). // Ensure the "Phone" key exists in every record, defaulting to null if missing: #"Normalized Records" = Table.TransformColumns(PreviousStep, { {"UserInfo", each Record.AddField(_, "Phone", null, true), type record} }), #"Expanded UserInfo" = Table.ExpandRecordColumn( #"Normalized Records", "UserInfo", {"UserID", "Department", "Phone"}, {"UserID", "Department", "Phone"} )

---

Step 3: Dynamically Expand All Available Record Fields

To make your query completely resilient to schema changes and sparse records without hardcoding field names, dynamically collect all distinct keys across all rows before expanding:

1. In the Advanced Editor, replace your static expansion step with the dynamic pattern: let Source = Json.Document(Web.Contents("<https://api.example.com/users>")), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), // Dynamically extract all distinct field names from all records in the column: AllFieldNames = List.Distinct(List.Combine(List.Transform(#"Converted to Table"[Column1], Record.FieldNames))), // Expand using the dynamic list instead of hardcoded strings: #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", AllFieldNames, AllFieldNames) in #"Expanded Column1"

[Record 1: ID, Name]
[Record 2: ID, Email] ──► List.Distinct(List.Combine(...)) ──► Dynamic Keys: {"ID", "Name", "Email"}
                                                                         │
                                                                         ▼
                                                           Safely Expanded with Nulls

This pattern ensures that every unique key found across the entire dataset is discovered automatically. Any record missing a key will automatically receive a null value instead of crashing the query.

For table-level metadata mismatches, see Power Query “Table.ExpandTableColumn” Metadata Mismatch. If a standard table column header is missing rather than a record field, see Power Query “Expression.Error: The column of the table wasn’t found”. If broken step pointers are causing upstream reference issues, consult How to Fix “Step references an unknown variable” in M-Code. For handling null evaluation crashes in logical steps, see “Expression.Error: We cannot convert the value null to type Logical”.