Remove Null Values in Power Query

Null or missing values in a dataset can affect calculations, resulting in inaccurate data analysis and reporting.

Using Power Query, you can remove null values from your dataset, ensuring it is clean, consistent, and reliable for accurate analysis and reporting.

I will show you how to use Power Query to remove null values from a dataset in various scenarios.

Scenario #1: Removing Null Values from a Single-Column Dataset

Suppose you have the single-column dataset below with missing or null values in rows 4, 7, and 10.

Data set with null values.

You want to use Power Query to remove the null values. 

Here’s how you can do it:

  1. Select the dataset.
Select the dataset.
  1. Open the Data tab and click the From Table/Range option on the Get & Transform Data group.
Click on the from table range option.

The above step opens the Create Table dialog box with the dataset’s reference filled in and the ‘My table has headers’ checkbox selected.

Create table dialog box with my table as header is checked.
  1. Click OK on the Create Table dialog box.

The above step converts the data range to a table (if it wasn’t a table already) and loads the data onto the Power Query Editor. 

Data opens in Power Query.
  1. Click the filter and sort button with a down arrow icon in the header row, uncheck the ‘null’ checkbox, and click OK.
Click on the filter icon in the column from which you want to remove null.

The above step filters out the null values from the dataset.

Null values are removed from the column.
  1. On the Home tab, click the Close & Load button.
Click on close and load.

The above step closes the Power Query Editor and loads the dataset without null values onto the destination worksheet.

Data loaded in Excel without the null values.

Scenario #2: Removing Null Values from Particular Columns in Specific Rows

Suppose you have the dataset below with null values in particular columns of rows 4, 7, and 10.

Data set with null values in different columns.

You want to use Power Query to remove the null values. 

Here’s how you can do it:

  1. Load the dataset onto the Power Query Editor using steps 1 to 3 in the ‘ Use Power Query to Remove Null Values from a Single-Column Dataset’ section.

The above step loads the dataset onto the Power Query Editor.

Data is opened in Power Query Editor.
  1. Filter out null values in each column as described in step 4 of the ‘ Use Power Query to Remove Null Values from a Single-Column Dataset’ section.

The above step filters out null values in each column of the dataset.

Null values from each column are removed.
  1. On the Home tab, click the Close & Load button.
Click on close and load.

The above step closes the Power Query Editor and loads the dataset without null values onto the destination worksheet.

Data where no row has null values.

Scenario #3: Removing Null Values from All Columns in Specific Rows

Suppose you have the dataset below, where rows 4, 7, and 10 have null values in all columns.

Data set with blank rows.

You want to use Power Query to remove the empty rows from the dataset.

You can use the steps below to do it.

  1. Select the dataset and load it into the Power Query Editor as outlined in steps 1–3 of the ‘Use Power Query to Remove Null Values from a Single-Column Dataset’ section.

The above step loads the dataset onto the Power Query Editor.

Data opened in the Power Query Editor.
  1. Click the Home tab, open the Remove Rows drop-down list on the Reduce Rows group, and select the Remove Blank Rows option.
Click on the remove blank rows option in the home tab in Power Query Editor.

The above step removes the rows with null values in all columns from the dataset.

Blank rows are removed.
  1. On the Home tab, click the Close & Load button.
Click on close and load in the home tab.

The above step closes the Power Query Editor and loads the dataset without empty rows onto the destination worksheet.

Data loaded into an Excel worksheet.

Scenario #4: Use Power Query to Remove Null Values from All Rows in a Specific Column 

Suppose you have the dataset below with missing or null values in all rows of column C.

Get out of the blank column.

You want to use Power Query to remove null values from all the rows of column C.

You can use the steps below to do the job:

  1. Load the dataset onto the Power Query Editor using steps 1 to 3 in the ‘ Use Power Query to Remove Null Values from a Single-Column Dataset’ section.

The above step loads the dataset onto the Power Query Editor.

Data open in the Power Query editor.
  1. Select the ‘Outstanding Balance’ column by clicking its header.
  2. On the Home tab, click the Remove Columns option on the Manage Columns group. 
Click on the remove columns option.

The above step removes the selected column from the table, effectively removing the null values from all its rows.

The selected column is removed that had all the null values in all the cells.
  1. On the Home tab, click the Close & Load button.
Click on the close and load option in the home tab.

The above step closes the Power Query Editor and loads the dataset without the empty column onto the destination worksheet.

Data is loaded into Excel. The data is loaded into Excel.

I have shown you how to remove null values in Power Query in various scenarios. I hope you found the tutorial helpful.

Other articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.