Downloads
Power Query MList: transform and filterBeginner

List.RemoveNulls

Removes every null (empty) item from a list.

Syntax
List.RemoveNulls(list)

Returns: A list with the same non-null items, in their original order.

What it does

Blank cells in a spreadsheet or database come through to Power Query as null. List.RemoveNulls takes them out of a list so that later steps, such as totals, counts and averages, work only on real values. It does not touch zeros, empty text made of spaces, or false values.

Syntax and arguments

Example

An AP clerk pulled the amounts for a batch of bills. Some lines are still open and show blank. The total should count only the bills that have an amount.

let
    Amounts = {1250, null, 480, null, 75},
    Clean = List.RemoveNulls(Amounts),
    Total = List.Sum(Clean)
in
    Total

Clean is {1250, 480, 75}, and the total is 1805. Removing them first also makes the intent of the total clear to the next person who reads the query.

Example: a second, different use

Counting how many lines actually have a value: List.Count(List.RemoveNulls(Amounts)) gives 3 in the example above, while List.Count(Amounts) gives 5.

Things to watch

In business terms

Open invoices, blank accrual lines, and partial month data often have blanks. Cleaning them before a sum or average keeps reports consistent, and a count before and after the step is a quick control check.

Related functions

All Power Query M functions · View as Markdown · M basics