Do you need to replace the null values with text in your Power BI datasets?

Null values in a dataset can occur due to various reasons, including missing data, data processing errors, data privacy and confidentiality, data type and format issues, and system or hardware issues.

Dealing with null or missing values is a common challenge for data analysts, and Power BI offers a range of features to help users manage these issues. Null values can be particularly problematic when creating visualizations or performing calculations, as they can skew results and create confusion.

By replacing null values with meaningful text, you can improve the accuracy and clarity of your visualizations and gain a better understanding of your data. In this post, you will learn a step-by-step process of replacing null values with text using various methods for handling null values offered in Power BI, such as using DAX formulas, Power Query interface, and Power Query M code.

Replace Null with Text using Power Query

Power Query has a useful tool called “Replace Values” that functions similarly to Excel’s find and replace feature. If you are already familiar with how to use find and replace in Excel, then you will find this tool easy to use.

If you have null values in a column as its present in this image above, you easily replace them with anything you want. You can follow these steps to do this.

1. Select the column containing the null values you want to replace.
2. Go to the Home tab and find the “Replace Values” tool in the Transform category.

3. When you click on Replace Values, it opens the Replace Values dialogue window. Enter “null” in the Value to Find box and whatever value you want to use to replace null in the Replace With box. In this case, 0 is used to replace null. Click on OK when you’re done.

When you’re done, the nulls would have been replaced with 0 in the selected column.

Replace Null with Text Using M Code

M code is a programming language used in Power Query, for data transformation and cleansing. M code allows you to customize and automate data transformation processes, including using it to replace null values in a column.

You can use different approaches to replace null values.

Replace Null Values with Add Column

The Add Column ribbon in Power Query helps you to easily create new columns by performing calculations, transformations, or merging operations on existing columns. This feature offers a wide range of functions and options, including conditional logic, text transformations, and mathematical operations, that you can use to customize new columns according to your analysis needs.

The Add Column ribbon contains various options for creating new columns. To remove null values, you will use the Custom Column menu. The Custom Column command allows you to create new columns based on a condition, line of code or formula.

if [No. of Shares] = null
then 0
else
[No. of Shares]

When you click on Custom Column, the Custom Column dialogue box opens. Copy and paste the above syntax into the Custom column formula box.

This formula uses the if statement to check every row in the [No. of Shares] column and return 0 when a row contains null. The statement returns any value in the [No. of Shares] column that is not null.

You can give the new column a name using the New column name bar. The No syntax errors have been detected statement at the bottom left of the window checks if there’s an error with the formula.

When you’re done, click on OK.

Once you click OK, the new column will be generated. Upon comparison, you will notice that all values, except for those with null values in the original column, will remain unchanged. Next, you can modify the data type of the new column to whole numbers and remove the original column that contains null values.

Replace Null Values with Advanced Editor

If you’re looking to dive deeper into the functionality of Power Query, the Advanced Editor is a powerful tool that can help you to achieve more complex data transformations. The Advanced Editor provides you with direct access to the M code that underlies each data transformation operation in Power Query.

With the Advanced Editor in Power Query, you can create custom data transformations that go beyond what is possible with the standard interface. Additionally, replacing null values using the Advanced Editor does not require you to create a new column, as the replacement takes place directly in the original column. This not only simplifies the data transformation process but also makes it more efficient.

To use the Advanced Editor to replace null values, go to the Query section in the Home tab.

As you open the Advanced Editor dialogue box, you’ll discover the source code that powers the data in Power Query. These lines of code are automatically generated when you import data into Power Query, and each action you take creates a new line of code that is stored in the Advanced Editor.

#"Replace Null" = Table.ReplaceValue(#"Changed Type", null, 0, Replacer.ReplaceValue, {"No. of Shares"})

This syntax allows you to replace null values in the “No. of Shares” column with “0”, just like you would with the Custom Column feature. The syntax uses the Table.ReplaceValue() function.

The function has the following arguments:

• table – the table or query with the column containing the null values.
• oldValue – the value you want to replace.
• newValue – the value you want to replace the oldValue with.
• replacer – the function that will carry out the replacement operation.
• columnsToSearch – a list of column(s) where the replacement will take place.

The syntax uses #”Changed Type” as the table argument because it’s a variable that returns a table. The old and new values to replace are specified as “null” and “0”, respectively. The replace argument leverages the Replacer.ReplaceValue function to substitute values in the “No. of Shares” column. The function is assigned to the Replace Null variable, which is then evaluated in the In statement.

To add a new line of code to the Let statement and assign it to a variable name, simply copy and paste the syntax into the In statement of the query. In M language, the Let statement enables you to evaluate a set of values and assign them to variable names, which can then be utilized in subsequent expressions that follow the In statement. Before copying and pasting the syntax though, ensure that you add a comma to the final line of code (the #”Changed Type” variable).

Click on the Done command after pasting the code to the editor.

After replacing all null values with 0 in the same column, the formula bar will display the syntax used to create the column, reflecting the changes made to the data.

Also, the Replace Null variable is added as a new step in the APPLIED STEPS tab.

Replace Null Values with DAX

Another way you can handle null values in Power BI is to create a calculated column using DAX, the formula language Power BI uses to create custom calculations for analyzing data.

To create a calculated column where all null values are replaced, you’ll first need to load your data into Power BI.

After loading your data, click on the grid icon to view the table. Then click on New column in the Table tools tab. Since the table is now loaded to Power BI, the nulls now display as blanks. Therefore, we’re replacing the blanks with a value.

No. of Shares (nulls replaced) = if(ISBLANK(Table1[No. of Shares]), 0, Table1[No. of Shares])

Copy and paste this syntax in the formula bar. The syntax uses the IF function to create a new column called No. of Shares (nulls replaced). The IF function simply checks for blanks and in the No. of Shares column and replaces them with “0”. All other values that are not blank are replaced as is.

Although a calculated column can help with handling blanks or nulls in your data, it may not be the most efficient method. This is because using calculated columns can increase memory usage by adding extra columns to your data. Additionally, unlike Power Query, the original column cannot be deleted because the new column depends on it.

Conclusion

Power Query and DAX are powerful tools in Power BI that can be used to transform and analyze data efficiently.

When dealing with null values, it is important to consider the efficiency and memory usage when selecting the appropriate method for handling them.

While calculated columns may not always be the most efficient way to deal with nulls, they can be useful in certain situations. By understanding the capabilities and limitations of Power Query and DAX, you can improve their data processing workflows.