Filter Out Null Rows with Power Query

Wondering how to use Power Query (in Excel or Power BI) to filter out rows where the value in every column is null? Then this is the article for you.

Step 1: Start with your sample data

How often does your data look like this?

All the time, right? That’s the nature of working with data. In this example, rows 3, 5, and 6 are missing important values and rows 5 and 6 have empty/blank values instead of null.

Step 2: Replace empty values with null

I am assuming for this article that empty and null values are equivalent to each other. That may not always be the case. You can skip to Step 3 if you want to handle null and empty values differently.

Option 1: Static amount of columns

Select all the columns in your table, right-click, and choose Replace Values:

Leave Value To Find blank, and put null for Replace With:

Click OK.

Option 2: Dynamic amount of columns

If you want to be a little more dynamic, and not manually select the column names, this option could be for you.

Open your Advanced Editor. It should look something like this:

let
    Source = "myfile.csv",
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hire Date", type date}, {"Name", type text}, {"Department", type text}})
in
    #"Changed Type"

Add another step below the last step in your query. The step to replace nulls should look like this:

let
    Source = "myfile.csv",
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hire Date", type date}, {"Name", type text}, {"Department", type text}}),
    #"Replace Nulls" = Table.ReplaceValue(#"Changed Type", "", null, Replacer.ReplaceValue, Table.ColumnNames(#"Changed Type"))
in
    #"Replace Nulls"

Notice that I referenced the name of my previous step (#“Changed Type”) twice in this #“Replace Nulls” step. For your data, use whatever your previous step is named instead. Make sure to put your new step name after the in statement.

Option 2 is essentially doing the same thing as Option 1, except it uses the Table.ColumnNames function to get all the columns from the previous step, so they don’t have to be manually chosen. This dynamic ability will help if columns get added to the data in the future.

Whether you used Option 1 or Option 2, the data should now look like this:

Step 3: Add a column to count non-null columns

Choose Add Column > Custom Column:

In the Custom Column editor, give your new column a name (e.g. Count of Non-Null Columns) and insert the following formula:

= List.NonNullCount(Record.ToList(_))

Your Custom Column editor should look like this:

Click OK.

The data should now look like this:

Step 4: Filter rows

The next step needed is to filter our custom column from the previous step to exclude rows that have no value in any column.

There’s multiple ways you could do this. For this example, let’s select the drop-down of our custom column and choose Number Filters > Greater Than:

In the Filter Rows editor, enter 0 for the is greater than condition.

Click OK.

The data went from this…

…to this:

Noticed that this step filtered out what used to be Row 5, since it didn’t have any data.

Step 5: Remove the custom column

Since the custom column created was just a helper column, let’s now remove it.

Right-click the custom column, and select Remove:

The data should now look like this:

That’s it!

Hope this helps in your learning.

Thanks for reading!