Null is always a common issue that we have in our real time data and we may want to replace it in different ways depending on our data and the type of analysis that we are doing. If we do not take care of the nulls in the data then we may face issues at the time of writing DAX in Power Bi and the final measure like Totals, Averages, Mean, Median etc. will not give us exact results. So, it is always advised to first treat null values in the data and then go for any sort of calculations that we need for our analysis.
In this article, I will deal with all the ways through which we can handle the null in data; so that you can make your selection according to your requirement.
The ways to handle nulls in Power BI are:
- Replace Null with a default number that you specify
- Fill the Null with the value from a Row above or below the null row
- Use ISBLANK function to find out all the null values in the data & Use DAX to replace the null
For this scenario I have connected Power BI desktop to an excel data, that looks like below (you can see we have some nulls in columns – Block Size, Project Completion and Email Address)
Let’s go to get data and bring this data to Power BI and click on Transform Data tab to open this data in Query Editor. Now, you will see that all the blank rows are coming with word null in Query Editor.
Now, let’s take the first approach to replace the null in the column, i.e.,
- Replace Null with a default number/ text that you specify
For this, let’s go to the email address column and there you will notice that we have few null or in other words we don’t have the mail id of some of our Customers. So where ever we don’t have an email id it is our company norm to replace it with a default email id – info.tj@123.com.
So, let’s move to Power Bi and do this. Remember we are working in Query Editor tab of Power Bi.
Step-1:
Go to Query Editor of the table that you want to edit. Select the Column for which you want to replace the null values and in HOME tab you will find REPLACE VALUES option, like below:
Step-2:
Click on Replace Value option in the Home tab and you will get a dialogue box where you can replace null with any value of your choice. Here, I have replaced all the nulls in the email address column with info.tj@123.com
The final result will be as below:
Now, let’s look into the next approach of handling null in Power BI
- Fill the Null with the value from a Row above or below the null row
Through this approach we replace or rather fill the values from the above or below rows to the null rows.
Step-1:
In the Query Editor tab of Power BI Click on the column for which you want to handle nulls.
Right click on the Column header and in the drop down that opens you will find Fill option, like below (this time I am working on the column- Block Size ):
Fill Values give us 2 options:
- Fill Up: Replaces the null with the value from the below row of that Column.
- Fill Down: Replaces the null with the value from the above row of the column.
You can select any one of these according to your requirement.
Now, Let’s move to the next approach to handle null in Power BI.
- Use ISBLANK function to find out all the null values in the data & Use DAX to replace the null
This approach is done in your Report tab or Data Tab. So, let’s shift to the data tab so that we can easily see the changes happening.
Step-1:
Go to the Modelling tab and Click on New Column to type the below formula:
T/F BlockSize = ISBLANK(‘Rooms Required'[BlockSize])
This results in a new Column showing a TRUE for BLANKS and FALSE for the rows having VALUES
Now, you can skip this step and directly write the DAX formula to replace the blank with the value (it can be number or text) of your choice. But, remember, for this approach the original column that is having null values have to be text data type. Here, I have replaced the null with a default number 14 according to my scenario.
The DAX formula (for a new Column) is as follows:
Default BlockSize = IF(ISBLANK(‘Rooms Required'[BlockSize]),“14”, ‘Rooms Required'[BlockSize])
See the screenshot below: