List.RemoveNulls Function in Power Query M (6 Examples)

List.RemoveNulls takes a list and returns a new list with every null value taken out. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you have a list with some null values in it and you just want a clean version without them, this is the function you reach for.

Table of Contents

Syntax of List.RemoveNulls Function

List.RemoveNulls(list as list) as list

where

list (required, list). The list you want to strip the null values out of.

Returns: a new list with every null element removed. If the list has no nulls, you get the original list back unchanged. If every element is null, you get an empty list {}.

In plain terms, you hand it a list and it gives you back the same list minus the nulls.

Example 1: Remove nulls from a number list

Drop the null values out of a short list of numbers.

let
    Source = List.RemoveNulls({12, null, 8, null, 15, 9})
in
    Source

Result: {12, 8, 15, 9}

The two nulls are gone and the remaining numbers stay in their original order.

Example 2: Empty strings are not removed

This is the one to watch. List.RemoveNulls removes null, but it leaves empty strings "" in place.

let
    Source = List.RemoveNulls({"Apple", null, "", null, "Pear"})
in
    Source

Result: {"Apple", "", "Pear"}

The two nulls are removed, but the empty string "" survives because it is not a null.

Example 3: Clean a table column of nulls

A common job is pulling a column out of a table and clearing its nulls before you work with it.

Here is the starting data:

SensorID	Temperature
S-01	21
S-02	null
S-03	19
S-04	null
S-05	23

Pull the Temperature column into a list, then strip the nulls:

let
    Source = Excel.CurrentWorkbook(){[Name="Readings"]}[Content],
    TempColumn = Table.Column(Source,"Temperature"),
    Cleaned = List.RemoveNulls(TempColumn)
in
    Cleaned

Table.Column gives you the column as a list, and List.RemoveNulls clears the blanks.

Result: {21, 19, 23}

You are left with just the three real readings.

Example 4: A list of all nulls returns an empty list

When every element is a null, there is nothing left to keep.

let
    Source = List.RemoveNulls({null, null, null})
in
    Source

Result: {}

You get an empty list {}, not a null. That distinction matters if a later step checks for an empty list.

Example 5: Count the non-null values

Wrap the result in List.Count to see how many real values are left.

let
    Source = List.Count(List.RemoveNulls({4, null, 7, null, null, 2}))
in
    Source

Result: 3

The list has three nulls and three numbers, so the count comes back as 3.

Example 6: Average after dropping nulls

Clear the nulls first, then average what is left.

let
    Source = List.Average(List.RemoveNulls({10, null, 20, null, 30}))
in
    Source

Result: 20

The average is taken over 10, 20, and 30, which gives 20.

Things to keep in mind with List.RemoveNulls

It removes only null, nothing else. Empty strings "", zero 0, and false all stay (Example 2). To also remove null values along with blanks, filter instead with List.Select(list,each _ <> null and _ <> "").
The order of the survivors is preserved. The values that stay come back in the same order they were in, so you can safely use the result where position matters.
A list with no nulls comes back unchanged. You can run it on any list without checking first; if there is nothing to remove, you get the same list back.
Use it to clean a column before an aggregate. List.Average and List.Count already skip nulls, but many other operations do not, so clearing nulls first keeps results predictable.

Common questions about List.RemoveNulls

What is the difference between List.RemoveNulls and List.Select for removing nulls?

List.RemoveNulls(list) is a shortcut that only drops null. List.Select(list,each _ <> null) does the same thing but lets you add more conditions, like also dropping empty strings. If you instead want to change each item rather than filter, reach for List.Transform.

Does List.RemoveNulls work on a table?

No, it only takes a list. To clean a table column, pull the column into a list first with Table.Column(Source,"ColumnName"), then pass that to List.RemoveNulls.

List of All Power Query Functions

Related Power Query Functions / Articles: