List.RemoveNulls
Removes every null (empty) item from a list.
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
list: any list. Nulls are the only items removed.
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
- A cell that contains only spaces is not null. Trim it with Text.Trim first, then check for an empty string.
- A zero is a real value. Removing nulls does not remove zeros, so a missing amount entered as 0 will still count.
- Check how many items are removed. A sudden drop after a refresh can mean the source changed.
- Table columns also have nulls. Use Table.SelectRows with
each [amount] <> nullto drop whole rows.
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.