Expression.Error: We cannot convert a value of type Record to type Table
By Tahir Nas · Examples run in our simulator · Updated
Expression.Error: We cannot convert a value of type Record to type Table.
A table function such as Table.Combine received a record (one row) where it needs a table. Lists of records from JSON are the usual source.
Common causes
- Appending a single record to a list of tables.
- Treating a JSON object as if it were an array of rows.
- A function that returns a row being used where a table is expected.
How to fix it
- Wrap rows: Table.FromRecords({record}) turns one or more records into a table.
- For JSON arrays of objects, use Table.FromRecords(json) or Table.FromList then expand.
- Check each item's type with Value.Is(item, type table) before combining.
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.Combine({#table({"A"}, {{1}}), [A = 2]})Fixed:
Table.Combine({#table({"A"}, {{1}}), Table.FromRecords({[A = 2]})})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
- beginner~6 minPro
Stack two months of orders (kite shop)
Append February's orders below January's to get one table.
- intermediate~9 minPro
Append store and web sales with different column names (bookshop)
Align WebSales column names to StoreSales before appending, and see where nulls come from.
- beginner~7 minPro
Append three store tables and label each row's store (kite shop)
Each store sends its own orders table. Stack all three and add a Store column saying where each row came from.
- intermediate~8 minPro
Append tables whose columns differ in order and set (kite shop)
Web orders have a Discount column, phone orders a Channel column, and their columns are in different orders. Append by name.
Related lessons
- Append tables and align their columns
- Flatten nested JSON
- Records in depth: fields, defaults and combining