ln this article we will show you how to check for blank values and nulls in Power BI.
We cover how to use the ISBLANK DAX function, but also provide a second solution if you need it.
IsNull in Power BI
The IsNull function does not exist in Power BI DAX. Therefore it can’t be used in any measures of custom columns in Power BI.
Instead, you can use the ISBLANK DAX function which will return True or False depending on whether the specified cell is blank or null.
Here’s how to use ISBLANK in Power BI.
IsBlank in Power BI
You can use the DAX formula ISBLANK to check for blank values in Power BI.
However, there is a caveat.
For text columns, in your Power Query editor, blank values must be changed to ‘null’ for the ISBLANK DAX function to work correctly.
Blank values in other column types in Power Query (such as dates and numbers) are automatically changed to null.
We go over this in more detail in our previous article about checking for nulls in Power Query.
Once your blank values are changed to null in Power Query, you can use the ISBLANK function in Power BI.
Here’s an example to show you how it works:
The below Power Query table has two text columns. The first column has some null values and the second one has blank cells.
In Power BI, we can create a custom column in our table and use the following formula to check for null values in the first column:
Column = ISBLANK(Example[Text])This produces the below result which tells us which rows contain the null values.
However, if we use the same DAX formula but on the ‘Text 2’ column with blank values instead of nulls, we get the below result.
This is not working as intended and therefore we must change the blank values to nulls in Power Query.
You can use the ISBLANK function combined with ‘if’ to return values you want if there are nulls.
Check for blanks and nulls using LEN
Rather than use the ISBLANK function in Power BI, you can use the LEN function too for text values.
It allows you to check for both nulls and blanks, so you don’t need to change values to null in Power Query.
LEN is a function that returns the length of text in a cell. Therefore we can use an IF statement to return a value when a cell has a text length less than 1, i.e. it is blank or null.
If we use the same table from our previous example, the below custom column formula will check for blank and null values.
Column = IF(LEN(Example[Text 2]) < 1,"B","A")This formula produces the below custom column if you want to check for blanks and nulls in both the ‘Text’ and ‘Text 2’ columns.
