Our comprehensive guide to Power BI null values will teach you what they are, how to identify them, and how to replace them to avoid calculation errors. If you’re making the transition from Excel, handling nulls in Power BI can be a challenge.
Having an understanding of null values at a conceptual level will help you as technology in the workplace continues to evolve. Null values are a common occurrence in SQL and the database world, and are increasingly common within many different types of business reporting and data visualization software.
To get started, let’s look at what Null values are and what the mean!
Table of Contents
What is a Null Value?
A null value represents missing or undefined data in a dataset. They differ from other data values in that it’s really meant to represent the lack of a value. For example, if a person skips a question while filling out a survey, the lack of information for that question could be represented as a null.
Another example is the case of working in Microsoft Excel and importing a spreadsheet into another system. If there are active cells at the bottom of the Excel spreadsheet, below where actual numbers and text are written the system might read these active cells as null. Notating that they were active in the workbook but contain no data.
Why do Null Values Matter?
On a functional level, the presence of null values in a dataset will often cause errors during calculations or cause data transformations to fail. In the data science world, null values in a dataset can throw off statistical analysis. They’re often imputed or replaced with a placeholder, so they don’t throw off the results of statistical analysis.
Difference Between Null vs Blank Values
Nulls differ from blank values. A blank value may be recorded if a field is intentionally left blank. A null value might be recorded if there is no information available to populate a value.
In either case, it could cause potential miscalculations in Power BI depending on the calculation being applied.
We’ll explain how to identify null values and how to handle them.
How to Identify Null Values in Power BI
The easiest way to identify null values in a Power BI dataset is to launch the Power Query editor. Power Query is the side of Power BI that lets you explore and transform incoming data. To launch Power Query, right click on one of the columns in the Data panel on the right side of the Power BI report view and select Edit Query.
After the Power Query Editor is open, navigate to the View section of the Power Query ribbon, and select Column Quality. The Column Quality view will show you any values in a column that is an error, null, or blank. It displays these values as a percentage of the total values in a column under the Empty section.
Actual null values are displayed as an italic lowercase null in a column.
Blank values are expressed like an empty cell in Excel, such as in the example’s Cookie Type column.
There are additional valuable tools that you can turn on in the View tab, such as the column profile and column distribution that will give you a quick idea of what the data looks like in a dataset.
Try turning them on when you’re working on importing new data to have a better idea of what it looks like without trying to scroll through thousands of rows manually.
How to Convert Null to 0 in Power BI
Converting null values to a value can be done with the Replace Values function in the Power Query Editor. Right click on the column title of a column, it brings up a contextual menu, and select Replace Values.
The following screen will present you with the option to find and replace.
To find a null value, type the word null.
Then type the value you wish to replace it with. A common replacement is to change a null value to a zero in a numeric column, though it may not always be appropriate for all datasets.
Once you click OK all of the null values will be replaced in the selected column.
Replacing Null Values in Multiple Columns at the Same Time
If you have a lot of columns with null values that you want to replace, you can hold down SHIFT and click on multiple columns to select a range of them. Alternatively you can hold down CTRL and click on individual columns with your mouse to multi-select them. Then right click a single column and Replace Values.
The replace values screen will look the same as updating a single column, but when you press OK the values in all selected columns will be updated in a single step.
How to Replace Blank Values in Power BI
To reference a blank value in Power BI, you have to leave the Value to Find field empty. This differs from some systems where you have to type something like blank() or empty(). The example below shows a blank value being replaced with a word in the Cookie Type text column.
After pressing OK, all of the blank values in a column will be replaced.
Converting Values to Null in Power BI
Values in Power BI can be replaced will null values, using the replace values screen. Right click on a column in the Power Query editor, and select replace values. Type the value you want to find and replace it with the word null. Power BI gives the word null special meaning, and will update to a null value instead of referencing it as text.
You can also replace values with a blank by leaving the replace with empty.
Handling Null and Blank Values in Power BI Conditional Columns
The following section explains how to work with null and blank values when creating a conditional column. In our example, we are going to create a new data quality column that checks if a column contains a null or a blank value.
At times we use this scenario to detect incomplete data coming into Power BI. You can tag rows that have bad or missing data with a 1, then add a hidden data quality tab to the dashboard and report side. It makes it very easy for a developer to open the Power BI file and immediately identify data quality issues without even launching Power Query.
The following example data has a blank value, and null values across multiple columns.
To start, we’ll create a conditional column under Add Column, Conditional Column in the Power Query Editor.
Next, on the Add Conditional Column screen, we’ll setup an IF statement with the Null. Our logic says that IF a column is NullTHEN output a 1.
We’ll also check if the second column is null, and if the first column is blank.
Nulls are identified by the word null, and blanks are identified by leaving the text entry box blank.
The result in this example is a 1 in the new conditional data quality column, and a null value when the dataset is complete without errors.
This becomes an easy field to add to a table or matrix visual in Power BI. Filter on the Data Quality is equal to 1 and it will present all of the rows with missing data that need to be addressed.
This method can also be a way to identify bad data loads coming in from other systems.
Conclusion
Null values can be a nuisance to deal with in Power BI. They can cause errors, or cause calculations to misrepresent data. Luckily, they are easy to correct. Launch the Power Query editor and replace a null with a value or replace a blank with a value.
You can select multiple columns prior to launching the replace value tool to update multiple columns at a time. Or you can even create a conditional column to identify which rows have blanks or nulls to give yourself a value to filter on and identify all of the offending rows with minimal effort.