How to handle “null” values that break mathematical transformations.

In Power Query, null represents the complete absence of a value. When performing mathematical transformations, such as addition, subtraction, division, or percentage calculations, Power Query follows strict three-valued logic. Any basic mathematical operation involving a null operand automatically returns null (for example, 5 + null = null). This “null propagation” frequently causes calculated columns, KPIs, and aggregations to output blanks instead of valid numerical results.

Quick Answer

To prevent null from wiping out mathematical results, replace nulls with zero before calculating. Select your numeric columns, go to the Transform tab, click Replace Values, type null into Value to Find, and enter 0 in Replace With. Alternatively, use the M coalescing operator ?? in custom formulas: ([ColA] ?? 0) + ([ColB] ?? 0).

---

Why “null” Breaks Calculations in Power Query

Spreadsheet users often expect blank cells to evaluate to zero automatically during arithmetic. Excel worksheet formulas do this under certain conditions, but Power Query’s M engine does not. M is a strongly typed functional language that treats null as an unknown state:

• 100 * null results in null.
• null / 2 results in null.
• [Bonus] + [Base Salary] results in null if either column contains a null.

Beyond simple propagation, passing null into specific functions that expect non-nullable parameters triggers a hard error:
DataFormat.Error: The value is not a valid Number or Expression.Error: We cannot convert the value "null" to type Number.

Understanding the difference between an empty string "", a blank cell, and an actual null value is essential. For more on this distinction, see #N/A vs. Empty Cells: Understanding the logical difference.

---

Methods for Handling “null” in Mathematical Operations

Method 1: The Null-Coalescing Operator (??)

The cleanest way to handle null inside a Custom Column formula without altering your raw data is the ?? operator. It evaluates the expression on the left; if that expression is null, it returns the value on the right.

In the Add Column tab, select Custom Column, and write your arithmetic like this:

([Sales] ?? 0) + ([Shipping] ?? 0) - ([Discount] ?? 0)

If [Discount] is null, Power Query substitutes 0 on the fly. If [Sales] contains 250, the calculation proceeds as 250 + 0 - 0 = 250.

Method 2: Global Column Value Replacement

If the column should never have missing numbers for reporting purposes, convert all null values to zeroes across the entire dataset:

1. Select one or more numeric columns (hold Ctrl to select multiple).
2. On the Transform tab, click Replace Values.
3. In Value to Find, enter null (lowercase, without quotation marks).
4. In Replace With, enter 0.
5. Click OK.

Power Query generates the following M code:

= Table.ReplaceValue(#"Previous Step", null, 0, Replacer.ReplaceValue, {"Sales", "Shipping", "Discount"})

Method 3: Safe Division to Prevent #DIV/0!

When division is involved, blindly replacing null with 0 introduces a new problem: dividing by zero. In Power Query, dividing a positive number by zero yields [Infinity], but dividing 0 by 0 yields DataFormat.Error or NaN (Not a Number).

To handle both null and 0 divisors safely, write an if...then...else condition in your custom column:

if [UnitsSold] = null or [UnitsSold] = 0 then 0 else [TotalRevenue] / [UnitsSold]

This prevents downstream division errors that crash your data model. For related division logic failures in formulas, see #DIV/0! in Weighted Averages: Handling zero-sum weights.

---

Diagnostic Checklist: When 0 Is Not the Correct Replacement

Replacing null with 0 is not always mathematically sound:

• Statistical Averages: If you replace null with 0 before calculating an average, the zeroes are counted in the denominator, artificially dragging down the average. Keep the values as null when calculating averages, because aggregation functions like List.Average ignore null entries while factoring 0 into the count.
• Missing vs. Zero Values: In financial metrics, a null revenue figure may mean “not reported yet,” whereas 0 means “zero sales.” If data fidelity matters, calculate metrics using conditional columns that keep the result as null when underlying inputs are missing.

If mathematical operations on your transformed tables still fail with type conversion warnings, verify that your columns are explicitly cast as Decimal Number or Whole Number rather than Any or Text. For cleaning dirty mixed types, refer to Expression.Error: We cannot convert Text to Number (Handling “Dirty” data).