In this article, we show you how you can check and test for null values in Power Query.
This can be useful if you want to create a custom column and return a certain value if a cell is null, rather than replacing null values.
We cover how you can easily mimic IsBlank and IsNull functions in Power Query to let you check for null values.
This also directly impacts the imported data within various applications, including Power BI, and facilitates the ability to efficiently check for blanks and nulls in Power BI.
IsBlank in Power Query
The function IsBlank does not exist in Power Query M code and you can’t use them in a custom column.
Furthermore, for anything other than text, there should be no blank cells in your Power Query data.
Any cells with no data (or blanks) should show as ‘null’.
You can then write an IF statement that will test for null values, mimicking IsBlank in Power Query.
IsNull in Power Query
Similarly, there is no IsNull function available in Power Query.
Instead, the way you can mimic IsNull and test for null values in your data is to use an IF statement.
Here are two examples of how you can test for null values in Power Query:
Below is a column of dates in Power Query with some null values in.
Here is the custom column formula we would use if we wanted to mimic IsNull:
if [Date] is null then "B" else "A"Here is the result:
Below is a second example using numeric values and some nulls.
We would keep the formula the same to mimic IsNull and test to see which rows have null values in.
It works the same and here is the result:
If your Power Query column is in text format, null values are not inserted for any blank values in your data.
Below is an example of a text column where there are some blank cells.
If we use the same if formula to mimic IsNull, this will not allow us to see which rows contain blanks.
Since we cannot use an IsBlank function in Power Query, we have to first change blank values to null.
We can then test for these null values as shown in the previous examples.
In this example, we would use the ‘Replace Values’ step as below:
This will then replace the blank values in our column with null, and we can then use the same ‘if [column] is null’ formula to check for null values.
Here is the result:
Another way you can check for null values in Power Query is by using the Text.Length function, however we found that using an if statement is easier.
Issues with checking for nulls in Power Query
A common issue people have with checking for blank or null values in Power Query is when blank spaces are entered into their source data.
When this happens, Power Query does not convert the cell with a blank space into a null, even though there is no data there.
To fix this issue, you can use the Text.Clean and Text.Trim functions (Clean and Trim) in Power Query to remove unwanted blank spaces.
This will let Power Query convert cells into nulls and your IsNull step should then work as shown above.