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
- A custom column that returns a table on some rows and a number on others.
- A merged column replaced by a value in a later step.
- Expanding the wrong column.
How to fix it
- Make every row return a table: if … then table else #table(columns, {}).
- Filter out non-table rows before expanding: each Value.Is([Data], type table).
- Check the column's values in the preview before expanding.
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:
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
- intermediate~9 minPro
Look up each order's customer city (kite shop)
Merge Orders with Customers on Customer and expand City, keeping every order.
- intermediate~8 minPro
Find orders from unknown customers (kite shop)
Use a left anti join to list orders whose customer isn't in the customer master.
- beginner~6 minPro
Keep only enrolments for courses that are still running (college)
Enrolments refer to course codes; the Courses table lists only courses still running. Keep matching enrolments with their title.
- advanced~14 minPro
Reconcile bank and ledger with a full outer join (charity)
Match bank lines to ledger entries by Ref and flag what's only in the bank, only in the ledger, or in both.