[Excel/Power Query] Error: Expression.Error — Cannot convert a value of type Record to type Table (expansion or structure mismatch) — How to Fix It

Summary

Expression.Error: Cannot convert a value of type Record to type Table appears when a Power Query step tries to treat a Record (single row structure) as a Table (collection of rows). This often happens during Expand, Merge, or Combine operations when the previous step outputs a Record instead of a Table. To fix it, convert the Record into a Table using Table.FromRecords() or modify the previous step so it returns a Table type.

Context

Power Query distinguishes between Records and Tables. A Record contains key-value pairs (like one JSON object), while a Table is a structured list of Records. Expansion and combination operations require Table input. This error often originates from steps like Table.First(), Record.Field(), or Removed Other Columns that unintentionally reduce a Table to a Record. Once a value becomes a Record, operations expecting a Table — such as Table.Combine or Expand — fail with this message.

Probable Cause

  • Expanding a Record instead of a Table: The column icon shows a Record (curly braces replaced by square brackets).
  • Using Table.Combine or ExpandTableColumn on a Record: The operation expects a Table input type.
  • Table reduced to Record: An earlier step used Table.First() or Record.Field().
  • Mixed return types: Query outputs differ — one step returns a Record, another returns a Table.
  • Copied M code mismatch: A referenced query returns a different structure than expected.

Quick Fix

  1. Identify the failing step. Open Power Query Editor → select the step that triggers the error (often “Expanded Column” or “Removed Other Columns”).
  2. Inspect the column icon. If it shows a single-sheet symbol, it’s a Record. Expansion requires a Table.
  3. Wrap the Record in Table.FromRecords().
# Convert Record to one-row Table let Source = Table.FromRecords({[ColumnName=[Field1="A", Field2=100]]}) in Source
  1. Correct previous reduction. If the step used Table.First() or Record.Field(), revert it or reintroduce Table.FromList() to restore structure.
  2. Access individual fields directly. Use [Column][FieldName] instead of trying to expand as a table when only one record exists.
# Access field from Record safely Fixed = Table.AddColumn(Source, "Amount", each [Details][Value])
  1. Check data types. Use Value.Type() or Table.Schema() to verify structures at each step.

Full Example

Scenario: A query retrieves a single record instead of a table. Attempting to expand it throws the error.

# Failing example Source = Record.Field([Details], "Transactions"); Expanded = Table.ExpandTableColumn(Source, "Transactions", {"ID", "Amount"}); # Error: Cannot convert a value of type Record to type Table

Fix 1 — Convert the Record into a Table

# Convert Record to Table Source = Record.Field([Details], "Transactions"); Converted = Table.FromRecords({Source});

Fix 2 — Adjust access pattern

# Access directly instead of expanding AmountValue = [Details][Amount]

Fix 3 — Restore table structure before merge

# If merging a record instead of table Buffered = Table.FromRecords({QueryReturningRecord}); Merged = Table.NestedJoin(BaseTable, {"ID"}, Buffered, {"ID"}, "Data", JoinKind.LeftOuter);

Pitfalls & Debug

  • Symptom → Record icon instead of table grid before expand. Fix → Wrap with Table.FromRecords() before expansion.
  • Symptom → Table.Combine or Merge fails. Fix → Ensure all sources return Table type.
  • Symptom → Expansion works for some rows only. Fix → Use conditional logic: if Value.Is([Column], type record) then Table.FromRecords({[Column]}) else [Column].
  • Symptom → Code copied from API example breaks. Fix → Verify whether the API returns Record or List — adjust accordingly.

Verification & Next Steps

# Check structure types TypeCheck = Table.AddColumn(Source, "Type", each Value.Type([Column]));
Example output:
[type record] → needs Table.FromRecords
[type table] → ready for expansion

Ensure each column has consistent structure. If columns vary (Record vs Table), normalize them before merging or expanding.

Sources

Table.FromRecords (Power Query M)

Value.Type (Power Query M)

Table.ExpandTableColumn (Power Query M)

Labels

Tool/Power Query, OS/Cross-platform, Topic/Record & Table Conversion