Expression.Error: We cannot convert the value to type Date
By Tahir Nas · Examples run in our simulator · Updated
Expression.Error: We cannot convert the value "2026-01-31" to type Date.
A date function such as Date.AddDays or Date.Year received text, not a date. The column still holds text because its type was never changed, or it was changed after this step.
Common causes
- Date arithmetic before the Changed Type step.
- A column typed as any or text after a merge or append.
- Dates stored as text in the source.
How to fix it
- Convert first: Date.AddDays(Date.From([OrderDate]), 30).
- Move the Changed Type step above the step that uses the dates.
- Set types again after Table.Combine or an expand, which can reset them.
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:
Date.AddDays("2026-01-31", 30)Fixed:
Date.AddDays(Date.From("2026-01-31"), 30)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~7 minPro
Add year, month and quarter columns (kite shop)
Convert OrderDate to a date, then add Year, Month (name) and Quarter (number).
- intermediate~8 minPro
Calculate days to close each ticket (telecom)
Convert Opened and Closed to dates and compute whole days between them; open tickets stay null.
- advanced~10 minPro
Calculate age in completed years on a report date (rowing club)
Work out each member's age on 30 June 2026, counting only birthdays that have already happened.
- intermediate~8 minPro
Count orders per week, with weeks starting on Monday (kite shop)
Label each order with the Monday of its week and count orders per week.
Guides
- Power Query date functions cheat sheet (tested)
- Power Query data type pitfalls that quietly change your numbers
Related lessons
- Date parts and durations
- Inspect column types and catch conversion errors
- Mixed column types after an append