Expression.Error: We cannot convert a value of type Record to type Text
By Tahir Nas · Examples run in our simulator · Updated
Expression.Error: We cannot convert a value of type Record to type Text.
A text operation received a record, which is what a row or a nested JSON object is. Pick the field you need first.
Common causes
- Text.From or & applied to _ inside Table.AddColumn, where _ is the whole row.
- A JSON column of nested objects used as if it held text.
- A row lookup without the trailing [Column].
How to fix it
- Select the field: _[Name] or [Name].
- Expand the record column with Table.ExpandRecordColumn before using its fields.
- To show a whole record as text, use Text.FromBinary(Json.FromValue(record)).
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:
Text.From([Name = "Ana", City = "Porto"])Fixed:
Text.From([Name = "Ana", City = "Porto"][Name])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
- advanced~14 minPro
Flatten nested JSON orders into one row per line (webshop)
Parse the JSON, expand the customer object and the list of order lines into a flat table.
- intermediate~8 minPro
Convert JSON records that don't all have the same fields (crm)
Some contacts have no email field and some no phone field at all. Build a table with all four columns.
- intermediate~9 minPro
Flatten a JSON object keyed by product code (garden)
The price feed is one object whose keys are SKUs and whose values are {name, price} objects. Make it a table.
- intermediate~9 minPro
Summarise each order's lines without flattening them (webshop)
For every JSON order, count its lines and total its units directly from the nested list, keeping one row per order.