Expression.Error: The field of the record wasn't found
By Tahir Nas · Examples run in our simulator · Updated
Expression.Error: The field 'Amount' of the record wasn't found.
Code reads a field from a record that doesn't have it. In a custom column, [Amount] reads a field of the current row, so the error means the row has no column called Amount at that point.
Common causes
- A custom column refers to a column that was renamed or removed earlier.
- Expanding a record column where some records lack the field (JSON is the usual source).
- A lookup record built by hand has a different field name.
How to fix it
- Check the exact column names in the step before the custom column.
- Use the optional field access [Amount]? to get null instead of an error.
- Use Record.FieldOrDefault(_, "Amount", 0) when the field is genuinely optional.
Find the step that fails
- Open the query in the Power Query editor (Excel: Data → Queries & Connections → Edit; Power BI Desktop: Transform data).
- Click the steps in Applied Steps from the top. The first step that shows the error is where it starts; later steps only repeat it.
- Read that step's formula in the formula bar (View → Formula Bar if it's hidden) and compare it with the columns and values the previous step produced.
Minimal example
Raises the error:
[Region = "North", Qty = 2][Amount]Fixed:
[Region = "North", Qty = 2][Amount]?Both snippets are run by the Power Query Exercises simulator on every build: the first must raise Expression.Error, the second must run.
Practise avoiding it
- advanced~14 minPro
Flatten nested JSON orders into one row per line (webshop)
Parse the JSON, expand the customer object and the list of order lines into a flat table.
- intermediate~8 minPro
Convert JSON records that don't all have the same fields (crm)
Some contacts have no email field and some no phone field at all. Build a table with all four columns.
- intermediate~9 minPro
Flatten a JSON object keyed by product code (garden)
The price feed is one object whose keys are SKUs and whose values are {name, price} objects. Make it a table.
- intermediate~9 minPro
Summarise each order's lines without flattening them (webshop)
For every JSON order, count its lines and total its units directly from the nested list, keeping one row per order.
Related lessons
- Records in depth: fields, defaults and combining
- Expand record columns safely when fields vary
- Flatten nested JSON