Expression.Error: We cannot convert the value to type Text
By Tahir Nas · Examples run in our simulator · Updated
Expression.Error: We cannot convert the value 1042 to type Text.
A text function such as Text.Start, Text.Length or Text.Combine received a number. M never converts numbers to text implicitly; you convert explicitly with Text.From.
Common causes
- A code column was typed as a number, then a text function was applied.
- Text.Combine over a list that contains numbers.
- Concatenating with & where one side is a number.
How to fix it
- Convert first: Text.Start(Text.From([Code]), 2).
- For Text.Combine, transform the list: Text.Combine(List.Transform(list, Text.From), ", ").
- Keep identifier columns (codes, ZIPs, IDs) as text from the start so leading zeros survive.
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.Start(1042, 2)Fixed:
Text.Start(Text.From(1042), 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 minFree
Keep order numbers' leading zeros (kite shop)
Type a CSV so order numbers stay text (0007, not 7) while quantities and prices become numbers.
- intermediate~7 minFree
Split a timestamp into its date and hour (door access)
Each event has a text timestamp like 2026-03-04T14:30:00. Add a real Date and the Hour of day.
- beginner~5 minFree
Make a mixed number/text code column all text (garden centre)
Product codes arrive as a mix of numbers (1001) and text (A-17, 0042). Make every code text.
- beginner~5 minFree
Build a date from year, month and day columns (deliveries)
A legacy export stores dates as three number columns. Combine them into one real date.
Related lessons
- Choose types deliberately: identifiers are text
- Combine text values and handle missing parts
- Split columns and extract text by delimiter