In this article, we will show you how to fix the error “We cannot convert the value null to type Logical” in Power BI.
You can experience this error in Power Query, which can look like this in one of your applied steps:
Or, you may discover it after you have clicked ‘Close and Apply’ and it stops changes being applied in Power BI.
It may also say “OLE DB or ODBC error: [Expression.Error]” beforehand.
There are three ways you can solve this error in Power Query and Power BI, which we share below.
Why does the error “We cannot convert the value null to type Logical” occur?
The reason why you are experiencing the error “We cannot convert the value null to type Logical” in Power BI is because you have null values that are being used in a calculation or applied step which requires a value.
This may not be easy to spot, and it may suddenly become an issue even if you haven’t changed your Power BI report.
If you make a change to your data source or add new data, and don’t enter a value somewhere, it could enter your report and cause the error to occur.
Here is an example of the error in one of our reports.
This is our example table of data we started with:
We then added a custom column to create a concatenated reference if the cost of goods had a positive value in:
Here is the code for our custom column:
= Table.AddColumn(#"Changed Type", "ConCat Ref", each if [Cost of goods] > 0 then [Product]&[Category] else null)We then used this ConCat Ref to merge with another table and bring in the number of sales for each reference from the Product Sales table, as below:
Now, a new row of data has been added in our data source (row 6 in the image below).
However, the person who entered this data did not put a value for the Cost of goods. There is a null value.
Therefore, the ConCat Ref column could not be calculated and returns an error.
Since we are using this ConCat Ref column in our merge queries step, we get the following error:
“[Expression.Error] We cannot convert the value null to type Logical..”
There are also instances where a table will still load in Power Query and not display the error in the yellow box as above. Power Query will let you carry on applying new steps. This is generally the case when you have a large number of rows in your table.
In this case, it can be hard to spot the error and you will only discover it after applying changes or refreshing the report.
To pinpoint exactly where the error occurs, you must go through each of the applied steps in Power Query and look for times when a column that contains null values is used in future applied steps or calculations.
Once you load all of the values in a column, it will say if the error has occurred.
Solutions
Solution 1: Remove null and empty values
We know this error in Power BI is caused when null values appear in columns that they shouldn’t.
Therefore, you can add an applied step to filter out the nulls in the affected column.
Either use the Table.SelectRows function or click on the column and deselect null.
Here is our example table where we have removed the null value from our Cost of goods column which was causing the error:
Here is the formula for the applied step:
= Table.SelectRows(#"Added Custom", each ([Cost of goods] <> null))Here is the final table, which now loads without an error since we have filtered out the row with the null value.
Solution 2: Amend the column to use nulls
The second solution we suggest is to change your custom column to take into account null values.
In our example, we have amended our custom column, ConCat Ref, to produce a result even if the Cost of goods is null:
Here is the applied step, and in bold the change we have made to fix the error:
= Table.AddColumn(#"Changed Type", "ConCat Ref", each if [Cost of goods] > 0 or [Cost of goods] = null then [Product]&[Category] else null)Here is the final table, which now loads without an error:
Solution 3: Use validation in your data source
Our last solution is to use data validation in your original data source so that null values can’t be entered in the first place.
If your data source is Microsoft Excel, for example, you can add data validation easily to your table and ensure that cells can’t be left blank.
However, we would recommend you also apply either the first or second solution, in case your data validation is changed in the future.