Removing or replacing null values in Excel Power Query is an important step to preparing data. Null values can lead to inaccurate calculations or cause errors when trying to perform calculations and analyze data. Luckily Excel Power Query makes it easy to remove or replace nulls. We’ll explain a few methods of how to do so.
Power Query gives users a few different ways to handle null values, from quickly replacing them, filtering them out, or conditionally replacing them based on values in other columns.
The method you choose will largely depend on where nulls occur in your dataset and each individual use case.
Let’s jump in!
Table of Contents
Null Values in Excel Power Query
Null values can cause errors when transforming data in Power Query and can also be an indication of how healthy your data is when it comes into Excel. When working with live data connections or consolidating data from multiple reports make sure that the nulls are expected or at least understood.
Sometimes a value may be null because columns don’t align, or there is a problem in a live data connection.
Null value represents an absence of data or a blank entry in a field, signifying that the data is either unavailable, not applicable or was purposely not entered. Think of this like a middle name, many people have one but not everybody does.
When null values exist in a dataset, they are shown with an italicized null. If you see them, they’re not necessarily bad but you may want to remove, filter, or replace them based on where they occur and what the impact is on your data.
Now that we know what null values are, let’s look at how to handle them.
Replacing Null Values in Excel Power Query
To replace Null values in Excel Power Query, launch the Power Query editor, then right click on the column name of a column that contains the null value and select Replace Values. This launches the replace value editor where you can replace “null” with any desired value.
Here’s how to adjust it in more detail.
Step 1.) Select a column, and right click, select Replace Values
Tip: If you need to update null values across multiple columns, you can hold down CTRL + Left Click to select all of the columns with null values, or you can hold down SHIFT and select a range of columns. After selecting multiple columns, right click and apply a data transformation step to apply to all columns.
Step 2.) Replace null with a new value
By typing the word “null” in lower case it will reference the null values in a column. Even though they are italicized within the Power Query Editor and in many other systems require special characters to reference them, Power BI does not require it.
Click OK to accept the change and a new Applied Step will be generated. When you Close and Load the data the changes will be committed, and the data will be updated when loaded into Excel.
Tip: Leave the Replace With field empty if you want to update the values with blank or empty values.
After replacing Null Values, you can similarly replace errors. Most of the data transformation actions that can be performed using Power Query can either be done within the Power Query ribbon, or you can right click a column name and there are many quick options to apply data transformation steps.
Filtering Out Null Values in Power Query
An alternative to replacing null values in Power Query is to filter them out of your data. Click on the small triangle at the top of a data column that contains null values and unselect “null”. The filter will generate a new applied step and remove the null rows from your data.
A benefit of filtering is that the data will be removed when you Close & Load Power Query into Excel, but it won’t be gone forever. You can always go back into the Power Query editor, and re-check null values to add it back to your dataset if you find that you still need it.
Filtering works on the full row though. So any null values that are filtered out will remove all other related entries in the dataset.
Replacing Null Values with Values from Another Column
Excel users can create Conditional Columns in Power Query to replace null values with values from another column with an easy-to-use interface. Go to Add Column, Conditional Column, and fill out the conditional column editor to return a value from a column if a value in is null.
Here’s how in more detail.
Step 1.) Launch the Conditional Column Editor
After selecting Conditional Column, an Add Conditional Column wizard will appear. Behind the scenes it’s generating an IF statement that says, IF a value in a column meets a specific criteria, THEN do ABC if true, or XYZ if false.
In this case, we’ll have it check if a column has a null value, and if it does, then return the value of a different column.
Step 2.) Fill out the Add Conditional Column Screen
The conditional column builder will result in a new column. You can either define specific values by typing them into the boxes, or you can change the ABC123 button into the icon of a column to pull in the value from that column.
Values are found and returned on a row by row basis.
In the example below, we check if the value in the City column is Null, and if it is, then we return the value from the City2 column.
At the bottom of the editor, we tell Power Query that if we don’t find a Null value, then leave the column alone and return the value from the original City column.
Note: You have the ability to Add Clause to add additional criteria. A new row will be added that will look like the first one in the screenshot above. It’s possible to add many different criteria rows but would caution on adding too many as it may be difficult to manage.
Step 3.) Remove and Rename Columns
The results of the new conditional column is a new column to the right of Power Query. In our example it’s one continuous list of cities, but when we go to load this data the column name will be “Custom” and the “City” and “City2” columns will remain.
To fix this, you can select the original columns, then right click on one of the headers and remove the columns from the dataset.
The remaining Custom column can be renamed by double clicking into the header. This will select the text where you can update the name. A new name could have also been assigned when creating the conditional column, but if you wanted to replace it with the name of an existing column, you would not be able to do so as column names must be unique at any point in time in Power Query.
Removing or replacing Null Values in Power Query is quick and easy. Power Query gives people a number of different options which are mostly available by right clicking on column headers. There are quick options to replace values, or a conditional column can be created to more dynamically update nulls.
Filtering will remove the entire row of data from your dataset which may be appropriate, but before you remove large numbers of null values, it is worth taking a few minutes to understand the incoming data. Large quantities of nulls may indicate issues with starting data that should be corrected at the source system to avoid future errors or bad calculations.