Expression.Error: We cannot convert the value null to type Logical
By Tahir Nas · Examples run in our simulator · Updated
Expression.Error: We cannot convert the value null to type Logical.
if and filter conditions need true or false. When a condition evaluates to null, for example because the column is empty on some rows, if raises this error.
Common causes
- if [Flag] then … where Flag is null on some rows.
- A comparison chain with and/or where one side is null.
- A logical column imported from Excel with blank cells.
How to fix it
- Compare explicitly: if [Flag] = true then … treats null as not true.
- Supply a default with the coalesce operator: if [Flag] ?? false then ….
- Replace nulls in the column before the step: Table.ReplaceValue(…, null, false, Replacer.ReplaceValue, {"Flag"}).
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:
let Flag = null in if Flag then "Yes" else "No"Fixed:
let Flag = null in if Flag ?? false then "Yes" else "No"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
- intermediate~7 minFree
Treat empty text and null the same (hardware)
Some suppliers are null and some are empty text. Standardise both to "Unknown".
- intermediate~8 minFree
Predict which orders a filter will drop, nulls included (kite shop)
Before a Qty ≥ 3 filter goes live, list exactly the orders it will remove, including the ones with no quantity.
- beginner~7 minFree
Convert Yes/No answers to true, false or null (charity)
The marketing opt-in column mixes Yes, Y, no, N, blanks and nulls. Turn it into a proper true/false column.
- beginner~7 minPro
Label orders as Small, Medium or Bulk (kite shop)
Add a Tier column from Qty: 1–2 Small, 3–5 Medium, 6 or more Bulk.