[Excel/Power Query] Error: Expression.Error — We cannot convert the value (type mismatch)

Summary

Expression.Error: We cannot convert the value … indicates a data type mismatch in Power Query (M). A step attempted to coerce values into an incompatible type (e.g., text → number/date) or encountered localization issues (comma vs dot decimals). Fix it by locating the failing step, validating column contents, selecting the correct target type, and applying safe, locale-aware conversions (Number.From, Date.From) with try … otherwise to handle invalid rows. Applies to Excel 2016+, Microsoft 365, Windows/Mac.

Context

Power Query records transformations as M steps. When you change a column’s type (GUI or code), the engine converts each value. Mixed content, embedded symbols, or null in numeric/date columns cause conversion failures, surfacing as Expression.Error at the first offending row. M is strict and culture-aware: Number.From("1,23","de-DE") differs from Number.From("1.23","en-US"). After merges/expands, columns often contain unexpected text or null. Robust pipelines validate, clean, and then convert with locale specified.

Probable Cause

Incompatible values for the target type. Text like "abc", blanks, or symbols in a column converted to number or date.
Locale mismatch. Decimal/thousand separators not matching the current culture (e.g., "1.234,56" vs "1,234.56").
Implicit conversions in Table.TransformColumnTypes. Automatic “Changed Type” steps presume uniform data.
Merges/expands introduced mixed formats. Joined columns bring non-numeric text or null into typed fields.

Quick Fix

Locate the failing step. In Power Query Editor, click through Applied Steps until the error appears. Toggle View → Column Quality/Distribution/Profile to inspect invalid values.

# Peek at types/invalid counts for the current step
Table.Profile(#"Previous Step")

Choose the correct target type and culture. Reapply type with locale (GUI: Transform → Data Type → Using Locale).

# Locale-aware conversion to number (US)
= Table.TransformColumnTypes(Source, {{"Amount", type number}}, "en-US")
# Locale-aware date (Germany)
= Table.TransformColumnTypes(Source, {{"InvoiceDate", type date}}, "de-DE")

Sanitize inputs before conversion. Trim, remove non-digits, or map empty strings to null.

# Clean "AmountText": keep digits, signs, separators; blank → null
Clean = Table.TransformColumns(Source,
  {{"AmountText", each let t = Text.Trim(_) in if t = "" then null else Text.Replace(t, " ", "")}} )

Convert safely with try/otherwise. Prevent refresh crashes and keep diagnostics.

# Safe numeric parse with fallback null
SafeNum = Table.TransformColumns(Clean,
  {{"AmountText", each try Number.From(_, "en-US") otherwise null, type number}})

# Safe date parse with fallback null
SafeDate = Table.TransformColumns(SafeNum,
  {{"InvoiceDate", each try Date.From(_) otherwise null, type date}})

Handle special strings. Replace symbols like “—”, “N/A”, “(blank)” before typing.

# Replace non-numeric placeholders
Repl = Table.ReplaceValue(SafeDate, each _, each if List.Contains({"N/A","-","—","(blank)",""}, _) then null else _, Replacer.ReplaceValue, {"AmountText"})

Full Example

Scenario 1: “We cannot convert the value 'abc' to type Number.” after auto “Changed Type”.

# Failing step (auto-generated)
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Amount", type number}})

# Fix: clean + safe parse + locale
Clean = Table.TransformColumns(Source, {{"Amount", each Text.Trim(Text.Replace(_, " ", ""))}}),
Typed = Table.TransformColumns(Clean, {{"Amount", each try Number.From(_, "en-US") otherwise null, type number}})

Scenario 2: “We cannot convert the value null to type Date.” after merge/expand.

# Fix: map null/empty to null explicitly, then convert
Norm = Table.TransformColumns(Prev, {{"OrderDate", each if _ = null or _ = "" then null else _, type any}}),
Dates = Table.TransformColumns(Norm, {{"OrderDate", each try Date.From(_) otherwise null, type date}})

Scenario 3: Locale issues on decimals ("1.234,56").

# Use proper culture for parse
TypedEU = Table.TransformColumnTypes(Source, {{"Gross", type number}}, "de-DE")

Scenario 4: Mixed numeric strings with currency symbols.

# Strip currency, keep digits and separators, then parse
Strip = Table.TransformColumns(Source, {{"Price", each Text.Select(_, {"0".."9",".",",","-"}), type text}}),
PriceNum = Table.TransformColumns(Strip, {{"Price", each try Number.From(_, "en-US") otherwise null, type number}})

Pitfalls & Debug

Symptom → Type step fails intermittently after refresh. Fix → Profile early; convert using try … otherwise null and log Errors with a separate query for review.
Symptom → Dates parse wrongly (month/day swapped). Fix → Apply Using Locale or pass culture to Date.From/Table.TransformColumnTypes.
Symptom → Merge introduces unexpected text. Fix → Validate types on both sides before merge; re-type after expand.
Symptom → Thousand separators break parse. Fix → Remove spaces/thin spaces and normalize separators before Number.From.
Symptom → Column mixes text and numbers. Fix → Split into two: a raw text column and a typed numeric column for downstream math.

Validation & Next Steps

Confirm conversions with profiling and sample checks:

# Column diagnostics
View → Column Quality / Column Profile

# Spot-check transformations
Table.RowCount(Table.SelectRows(Typed, each [Amount] = null))   // count unparsed rows

Keep “clean → parse → type” as a pattern. Always specify culture when data is regional. Wrap risky conversions in try … otherwise and surface invalids for remediation instead of breaking refresh pipelines.