There are a few ways on handling null values in custom columns in Power BI. Although, it is important to understand the context of the data so as to decide which approach is best to use when dealing with null values.
In this article, I will explain two different methods on how to handle null values in custom columns in Power BI Desktop.
Read on to learn more.
Getting started
To get started, insert your data into the Power BI Desktop, and click on Transform Data to take you to Power Query Editor (do not load the data directly).
This is what my data in Power Query Editor looks like.
Handling Null Values in Custom Columns
Often, there are two standard methods for handling null values in Power BI custom columns. These methods include:
Filter out or Remove the Empty or Null Values
In your Power Query Editor, check for the columns that have empty or null values. As you can see below, the Motto and CWUR_score columns have null values (they are not 100% valid). So, your job is to filter out the null values.
To filter out the null values, click on the arrow on the column you want to filter.
You will see a window that contains the data of that particular column. Then, uncheck null, and click OK to apply your settings.
NB: Do this for all the columns with null values.
This process automatically filters out the null values in the custom column (as displayed below)
For columns that have both null and empty values, uncheck null and also click Remove Empty, and then OK.
This process removes both the null and empty values in the column.
Replace Null Values
The second method is to replace the null values. To do this, go to the Add Column tab, and click on Conditional Column. You will see the additional column window where you will enter your condition.
Using the CWUR_score column, I created an additional column and used the condition below.
The condition above will change the null values to 0 in the new column: CWUR_score 2.
NB: Repeat this process for all the columns you want to replace their null values.
Now, as you can see below, the null values have been changed to 0. You can delete the initial column (CWUR_score), and replace it with the CWUR_score 2 column.
Conclusion: Handling Null Values in Custom Columns in Power BI
As explained, those are the two methods on how to handle null values in custom columns in Power BI.
However, depending on the requirements and output required of the data, you can use either of the two methods when you are dealing with null values in Power BI.
Thanks for reading!