[Excel/Power Query] Error: Expression.Error — We cannot convert the value null to type Number (handling nulls in numeric columns) — How to Fix It

Summary

Expression.Error: We cannot convert the value null to type Number occurs when Power Query tries to interpret a null or text entry as a number. This typically happens right after a “Changed Type” step or during arithmetic operations like addition or division. Power Query requires explicit numeric values for type conversion — null (absence of data) cannot be converted directly. The fix is to replace or handle nulls before conversion using try … otherwise, Replace Values, or conditional logic.

Context

When importing or transforming data, Power Query automatically infers column types. If it finds blank or non-numeric values in a numeric column, the automatic Changed Type step fails, producing this error. Common cases include Excel or CSV blanks, text placeholders like “N/A”, or arithmetic expressions like [A] + [B] where one operand is null. Power Query doesn’t implicitly treat null as zero — it treats it as “no value”, which cannot participate in numeric expressions unless handled explicitly.

Probable Cause

  • Null or blank entries: Empty cells imported as null break numeric conversions.
  • Mixed data types: Some cells contain text like “N/A” or “—”.
  • Auto type detection: The “Changed Type” step guessed type number but encountered text/nulls.
  • Arithmetic with nulls: Adding or multiplying null values without safety checks.
  • Manual conversion: Number.From() used directly on null or invalid text.

Quick Fix

  1. Locate the failing step. In Power Query Editor, identify the step showing Expression.Error — often “Changed Type” or a custom column.
  2. Inspect affected column. Hover over the column header to see its type and presence of errors or blanks.
  3. Replace invalid entries. Right-click the column → Replace Errors → enter a default (e.g., 0 or null).
# Replace invalid or missing values before conversion Cleaned = Table.ReplaceValue(Source, null, 0, Replacer.ReplaceValue, {"Amount"})
  1. Use safe conversion logic. Replace direct numeric conversion with try … otherwise to prevent failure:
# Safe number conversion SafeNumbers = Table.AddColumn(Source, "AmountFixed", each try Number.From([Amount]) otherwise null, type number)
  1. Remove non-numeric text. If values like “N/A” exist, clean them first:
# Replace non-numeric text Cleaned = Table.ReplaceValue(Source, "N/A", null, Replacer.ReplaceValue, {"Amount"}) Converted = Table.TransformColumnTypes(Cleaned, {{"Amount", type number}})
  1. Optionally coalesce nulls to zero. Use if … then … else for math-safe replacement:
# Replace null with zero before calculation Total = Table.AddColumn(Source, "Adjusted", each if [Amount] = null then 0 else [Amount])

Full Example

Scenario: A CSV column “Amount” contains numbers and blanks. The auto “Changed Type” step fails.

# Original data Source = Table.FromRecords({ [ID=1, Amount="100"], [ID=2, Amount="N/A"], [ID=3, Amount=null], [ID=4, Amount="250"] }); # Step 1 — Replace invalid text Cleaned = Table.ReplaceValue(Source, "N/A", null, Replacer.ReplaceValue, {"Amount"}); # Step 2 — Safe numeric conversion Converted = Table.AddColumn(Cleaned, "AmountNum", each try Number.From([Amount]) otherwise null, type number); # Step 3 — Optional: replace remaining nulls with 0 Filled = Table.ReplaceValue(Converted, null, 0, Replacer.ReplaceValue, {"AmountNum"});

Result: All rows convert cleanly. Non-numeric or missing entries become 0 or null depending on logic.

Pitfalls & Debug

  • Symptom → “Changed Type” step fails immediately. Fix → Delete it and reapply manually after cleaning data.
  • Symptom → Math operations return null. Fix → Wrap arithmetic with try … otherwise or default zero.
  • Symptom → Still errors on refresh. Fix → Check for new text placeholders in updated source.
  • Symptom → “null” displayed as text. Fix → Convert to real null via Replace Values or Value.ReplaceType.

Validation & Next Steps

Use built-in profiling tools to validate numeric columns:

# Column profiling Profile = Table.Profile(Source); Quality = Table.TransformColumnTypes(Source, {{"Amount", type number}});

Keep the Column Quality bar visible to monitor null or error percentages. Standardize your cleaning logic for recurring imports — especially when data originates from user-maintained Excel files or regional exports.

Sources

Number.From (Power Query M)

try … otherwise (Power Query M)

Table.ReplaceValue

Labels

Tool/Power Query, OS/Cross-platform, Topic/Null Handling & Numeric Conversion