Here’s a question from a reader:
“How can I replace null values in my data using Power Query in Power BI?”
Null values can distort your data and its analysis. Hence, replacing null values in Power BI is a key part of the data preparation process. This tutorial demonstrates how to use Power Query to efficiently handle null values in an employee dataset. We’ll cover step-by-step instructions, provide real-world HR examples, and offer troubleshooting tips to ensure your data is clean and ready for analysis.
Replacing null values in Power Query
- Open Power BI Desktop and load your table.
- Go to Home and then Transform data to open the Power Query Editor application.
- Select the column(s) where you want to replace null values.
- Click on Transform and then, from the drop down, hit Replace Values.
- In the “Replace Values” dialog,
- Value To Find: Leave blank (this represents null)
- Replace With: Enter your desired replacement value
- Then hit the OK button.
For completeness, here is the M code needed to replace empty null values in the Department column with “Unassigned“:
= Table.ReplaceValue(EmployeeData, null, "Unassigned", Replacer.ReplaceValue, {"Department"})For numerical data, you can replace the NULL values with 0:
= Table.ReplaceValue(EmployeeData, null, 0, Replacer.ReplaceValue, {"Salary"})Remove empty values in Power BI
There are instances where you might decide to remove empty observations from your data set.
- Open Power Query.
- Right click your column header – in our Case – the Department column.
- Hit Remove Empty.
- Power Query will rows containing empty observations from your dataset as shown below (note that the amount of rows is now 16 instead of 20 above):
