Expression.Error: We cannot convert a value of type Table to type Text
By Tahir Nas · Examples run in our simulator · Updated
Expression.Error: We cannot convert a value of type Table to type Text.
A text function received a whole table. Usually a step returns a table where you expected one cell, for example after a filter that should have picked a single value.
Common causes
- Passing a query or step (a table) to Text.Upper, Text.Combine or &.
- A parameter-like query returns a one-cell table instead of a value.
- A merge produced a nested table column and a later step treats it as text.
How to fix it
- Drill down to the value: Table{0}[Column] or Table[Column]{0}.
- For a single-value query, right-click the cell and Drill Down so the query returns the value.
- Expand or aggregate nested table columns before using them as text.
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.Upper(#table({"Region"}, {{"north"}}))Fixed:
Text.Upper(#table({"Region"}, {{"north"}}){0}[Region])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
Build a one-row summary table in M (kite shop)
Write a let expression that computes order count, total quantity and the largest unit price, returned as a one-row table.
- intermediate~9 minPro
Build a calendar from the first to the last order date (kite shop)
Create a table with one row for every day from the earliest to the latest order date, including days with no orders.
- beginner~6 minPro
Turn a settings object into a Name/Value table (report config)
A JSON settings file is a single object. List its settings as rows with Name and Value columns.
- intermediate~7 minPro
Find customers who ordered in January but not in February (kite shop)
Compare the customer lists of two months and return the customers who didn't come back.
Related lessons
- M basics: let, records and lists
- Parameters: one value, many queries
- Expand or aggregate a merge result