[Excel/Power Query] Error: Expression.Error — We cannot apply field access to the type Text (record/table vs text)

Summary

Expression.Error: We cannot apply field access to the type Text appears when you reference a field like [Amount] but the underlying value is plain Text, not a Record (row) or a Table/Record value. Typical causes include earlier steps that coerced structured data to text (e.g., Text.From), mixed-type columns where some rows are Records and others are Text, or attempting to expand fields manually instead of using the expand button. Fix by ensuring the column is a Record or Table before field access, parsing text-encoded structures (e.g., JSON), and adding defensive typing with Value.Is.

Context

Power Query columns can hold primitive types (Text, Number, Date) or structured types (Record, Table, List). Field access with bracket syntax—[FieldName] or functions like Record.Field—only works on Record values. If a step transforms a structured column into Text (via Text.From, concatenation, or wrong Expand), later field access fails. Likewise, when importing JSON/CSV APIs, a column may display as Text (e.g., a JSON string), which must be parsed with Json.Document before expansion. Mixed-type columns are common after Merges/Expands or custom logic; these require normalization or conditional handling.

Probable Cause

Field access on Text. Using each [Field] while the column is primitive Text.
Earlier coercion to Text. A prior step applied Text.From or concatenation to a structured column.
Mixed types in the same column. Some rows are Records, others Text or null.
Manual expansion instead of UI expand. Typing [Field] against a column that actually contains Text.
Unparsed serialized structures. JSON/XML stored as Text not converted to Record/Table before access.

Quick Fix

Inspect the column type. In the preview grid, check the column icon: “ABC” = Text, record icon = Record, table icon = Table. Turn on View → Column Quality/Distribution/Profile to detect mixed types.
Expand with the UI when the column is structured. If the column has a Record/Table icon, click the expand (↔) button to select fields. Avoid manual [Field] until the column is confirmed as structured.
Parse text-encoded structures. If the column is JSON as Text, convert it first:

# Parse JSON text in column "Payload" Parsed = Table.TransformColumns(Source, {{"Payload", each try Json.Document(_) otherwise null}})
Then expand the resulting records

Expanded = Table.ExpandRecordColumn(Parsed, "Payload", {"id","amount"}, {"id","amount"})

Add defensive access for mixed columns. When not all rows are Records, guard with Value.Is:

# Safely read "amount" if the cell is a Record, else null WithAmount = Table.AddColumn(Source, "amount", each if Value.Is([Payload], type record) then Record.Field([Payload], "amount") else null, type number)

Undo text coercion. If a previous step applied Text.From or similar to a structured column, branch before that step or remove the coercion on the column you need to expand.
Normalize types up front. Use Table.TransformColumnTypes only for primitive conversions; avoid converting structured columns to Text unless that’s intentional.

Full Example

Scenario 1: JSON payload stored as Text

# A column "Payload" contains JSON text like: {"id":123,"amount":45.6} Source = Excel.CurrentWorkbook(){[Name="Raw"]}[Content];
Identify Text vs Record

Profile = Table.Profile(Source);

Parse Text → Record

Parsed = Table.TransformColumns(Source, {{"Payload", each try Json.Document(_) otherwise null}});

Expand fields

Expanded = Table.ExpandRecordColumn(Parsed, "Payload", {"id","amount"}, {"id","amount"});

Result: columns id (number), amount (number)

Scenario 2: Mixed-type column after merge/expand

# "Details" column has Records for most rows, but Text/null for others WithValue = Table.AddColumn(Source, "AmountSafe", each if Value.Is([Details], type record) then Record.Field([Details], "Amount") else null, type number);

Scenario 3: Earlier step converted a Record to Text

# Failing pattern: field access after Text.From Bad = Table.TransformColumns(Source, {{"Meta", each Text.From(_), type text}}); # Later... Fail = Table.AddColumn(Bad, "Author", each [Meta][Author]) # ← error: Meta is Text
Fix: branch before coercion or avoid coercion on "Meta"

Good = Table.AddColumn(Source, "Author",
each if Value.Is([Meta], type record) then [Meta][Author] else null, type text)

Scenario 4: Delimited Text that must become a Table/Record

# e.g., "A|B|C" → columns Split = Table.TransformColumns(Source, {{"Blob", each Text.Split(_, "|")}}); AsTable = Table.TransformColumns(Split, {{"Blob", each Table.FromColumns({ _ }, {"Col"})}});
If a single row record is desired:

AsRecord = Table.TransformColumns(Split, {{"Blob", each [A=[0], B=[1], C=_[2]]}})

Pitfalls & Debug

Symptom → Custom column each [Field] errors sporadically. Fix → Column is mixed-type. Add Value.Is checks or normalize with a parsing step so all rows share the same type.
Symptom → Expand button missing. Fix → Column is Text. Parse it (e.g., Json.Document) before expansion, or adjust prior steps to preserve structure.
Symptom → Worked before, fails after refresh. Fix → Upstream data changed type (API now returns string). Add a try … otherwise pattern to handle variations.
Symptom → Using Table.AddColumn with each [Field] on a Text column. Fix → Convert the source to a Record or use functions (Record.Field) after verifying type.
Symptom → Columns expanded then coerced to Text for display. Fix → Keep a structured branch for logic; only format to Text at the very end.

Validation & Next Steps

Confirm runtime types before field access and detect anomalies early:

# Inspect types row-by-row Types = Table.AddColumn(Source, "TypeOfCol", each Value.Type([Payload]));

// Profile the column for mixed types/nulls
View → Column Quality / Column Profile

// Defensive parse with logging
Safe = Table.AddColumn(Source, "Parsed",
each let t = Value.Type([Payload]) in
if t = type text then try Json.Document([Payload]) otherwise null
else if Value.Is([Payload], type record) then [Payload]
else null, type any)

Prefer the expand UI to generate correct ExpandRecordColumn/ExpandTableColumn steps, then refine. For volatile sources, combine try … otherwise with Value.Is to keep pipelines resilient. Finally, keep transformations modular—parse/normalize first, expand/access second, formatting last.