Power QueryExercises

Expression.Error: We cannot convert the value to type Table

By Tahir Nas · Examples run in our simulator · Updated

Expression.Error: We cannot convert the value 1 to type Table.

Expand Table Column (or another table function) met a cell that isn't a table. Mixed nested columns, where some rows hold a table and others a number, text or null-like placeholder, cause it.

Common causes

How to fix it

  1. Make every row return a table: if … then table else #table(columns, {}).
  2. Filter out non-table rows before expanding: each Value.Is([Data], type table).
  3. Check the column's values in the preview before expanding.

Find the step that fails

Minimal example

Raises the error:

Table.ExpandTableColumn(#table({"Key", "Data"}, {{"a", 1}}), "Data", {"x"})

Fixed:

Table.ExpandTableColumn(#table({"Key", "Data"}, {{"a", #table({"x"}, {{1}})}}), "Data", {"x"})

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

Related lessons

Similar errors