[Excel/Power Query] Error: Expression.Error — We cannot convert a value of type List to type Table (conversion error) — How to Fix It

Summary

Expression.Error: We cannot convert a value of type List to type Table occurs when Power Query expects a Table but receives a List instead. Lists are one-dimensional collections (e.g., {1,2,3}), while Tables are two-dimensional with columns and rows. This mismatch often arises during expansions, merges, or Table.Combine operations where a List is mistakenly passed as a Table. Fix it by converting the List to a Table using Table.FromList() or by restructuring earlier steps to return proper Table objects.

Context

Power Query’s data model distinguishes between Lists (single-column collections) and Tables (structured datasets). A List may appear visually similar to a single-column table but behaves differently. When functions like Table.Combine, Table.ExpandTableColumn, or Table.NestedJoin are applied to Lists, Power Query throws this error. Lists often emerge from operations like Table.Column(), List.Transform(), or certain APIs returning JSON arrays. Correct handling depends on your intent — whether you want to transform the List into a Table, aggregate it, or extract individual items.

Probable Cause

  • List in a column expected to contain a Table. Expanding or combining such a column fails.
  • Previous step output a List instead of a Table. For instance, Table.Column() returns a List, not a Table.
  • Incorrect input type to Table.Combine or Expand. Passing a list of values instead of list of tables.
  • Merging queries or expanding nested data incorrectly. A JSON array or column expansion produces lists rather than tables.
  • Unintended flattening of tables into lists. Using List.Transform or List.FirstN on a Table variable.

Quick Fix

  1. Identify the failing step. Open Power Query Editor and click through Applied Steps to locate the one returning the error.
  2. Inspect the column icon. Hover over problematic columns. A list icon (≡) indicates a List value, while a table icon (▦) indicates a Table.
  3. Convert List → Table. Wrap the list in Table.FromList():
# Convert list to table with one column Converted = Table.FromList([ListColumn], Splitter.SplitByNothing(), {"Value"})
  1. Handle dynamic types. If the column can contain both lists and tables:
# Safely convert lists, pass through tables Fixed = Table.AddColumn(Source, "AsTable", each if Value.Is([Data], type list) then Table.FromList([Data], Splitter.SplitByNothing(), {"Item"}) else [Data])
  1. Extract list elements instead of converting. If only one item is needed, use list accessors:
# Get first element of list FirstItem = Table.AddColumn(Source, "FirstValue", each List.First([ListColumn]), type any)
  1. For JSON arrays returned as lists: parse and convert properly before expansion.
# Parse JSON text and convert inner lists to tables Parsed = Table.TransformColumns(Source, {{"Payload", each Json.Document(_)}})
Convert inner list to table

Fixed = Table.AddColumn(Parsed, "PayloadTable",
each if Value.Is([Payload], type list)
then Table.FromList([Payload], Splitter.SplitByNothing(), {"Value"})
else [Payload])

Full Example

Scenario: A column “Items” holds a list for each row, like {1,2,3}, but the next step uses Table.ExpandTableColumn, which expects a Table. Power Query raises:

Expression.Error: We cannot convert a value of type List to type Table.

Fix:

# Sample source Source = Table.FromRecords({ [ID=1, Items={1,2,3}], [ID=2, Items={4,5}], [ID=3, Items=null] });
Convert lists to tables safely

Normalized = Table.AddColumn(Source, "ItemsTable",
each if Value.Is([Items], type list)
then Table.FromList([Items], Splitter.SplitByNothing(), {"Item"})
else null, type table);

Expand normalized tables

Expanded = Table.ExpandTableColumn(Normalized, "ItemsTable", {"Item"}, {"Item"});

Result: Each list element becomes a separate row under the “Item” column, with ID repeated for each list entry.

Example: combining list of tables

# Wrong: Table.Combine expects list of tables, not list of values BadCombine = Table.Combine({1,2,3})
Correct:

T1 = Table.FromList({1,2}, Splitter.SplitByNothing(), {"Value"})
T2 = Table.FromList({3,4}, Splitter.SplitByNothing(), {"Value"})
GoodCombine = Table.Combine({T1, T2})

Pitfalls & Debug

  • Symptom → Column expand fails. Fix → The column contains Lists; convert them with Table.FromList() before expansion.
  • Symptom → Table.Combine crashes. Fix → Ensure the list elements are Tables (Value.Is(_, type table)).
  • Symptom → Mixed list/table data types. Fix → Add conditional conversion (if Value.Is(...)) or unify upstream steps.
  • Symptom → Refresh errors after merge. Fix → The merge returned a list of matches instead of a nested table; use Table.ExpandListColumn() or Table.FromList().
  • Symptom → JSON array parsed but not expanded. Fix → Apply Table.FromList() to convert arrays to tables before further transformations.

Validation & Next Steps

Before applying table functions, confirm the column types explicitly:

# Type-check before operations Checked = Table.AddColumn(Source, "TypeCheck", each Value.Type([Items]))
Conditional fix

Clean = Table.AddColumn(Checked, "AsTable", each
if Value.Is([Items], type list)
then Table.FromList([Items], Splitter.SplitByNothing(), {"Value"})
else [Items])

Use Column Quality profiling and cell previews to detect where lists occur. Keep transformations modular: lists → tables → expansions. Avoid flattening tables into lists unless you truly intend a one-dimensional collection.

Sources

Power Query M: Table.FromList

Value.Is (type testing)

Table.Combine

Labels

Tool/Power Query, OS/Cross-platform, Topic/List vs Table Conversion