List.NonNullCount Function in Power Query M (5 Easy Examples)

If you want to count how many items in a list actually have a value, ignoring the blank ones, the List.NonNullCount function is what you reach for. It counts every item except null. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of List.NonNullCount Function

List.NonNullCount(list as list) as number

where

list (required, list). The list whose non-null items you want to count.

Returns: a number, the count of items in the list that are not null. An empty list or a list of only null values returns 0.

In plain terms, you hand it a list and it tells you how many entries are filled in rather than blank.

Example 1: Count the filled values in a list

Count how many items have a value when some entries are null.

List.NonNullCount({1,null,3,null,5})

Result: 3

There are five items in the list, but two of them are null, so only three get counted.

Example 2: Count a list with no blanks

When nothing is null, the count matches the full length of the list.

List.NonNullCount({"Mon","Tue","Wed","Thu","Fri"})

Result: 5

All five weekday names are real values, so all five are counted.

Example 3: An empty string still counts

An empty string "" is a real value, not a blank, so it is counted.

List.NonNullCount({"alex","",null})

Result: 2

Both "alex" and the empty string "" get counted here. The null is the only thing left out.

Example 4: A list of only nulls returns zero

When every item is null, nothing is counted.

List.NonNullCount({null,null,null})

Result: 0

Example 5: Count the answered rows in a table column

A common real-world use is counting how many rows in a column actually have a value.

Say you have a Survey table where some respondents left the Rating blank.

Here is the starting data:

Respondent	Rating
R001	4
R002
R003	5
R004
R005	3

Pass the Rating column to List.NonNullCount:

let
    Source = Excel.CurrentWorkbook(){[Name="Survey"]}[Content],
    Result = List.NonNullCount(Source[Rating])
in
    Result

The blank cells load as null, so they are left out of the count.

Result: 3

Three respondents gave a rating, even though the table has five rows.

Things to keep in mind with List.NonNullCount

Only null counts as missing. An empty string "", the number 0, and false are all real values, so they are counted. If you want to treat empty strings as blank too, remove null values and clean the list first.
To count a table column, pass Source[ColumnName]. A column reference like Source[Rating] (or Table.Column(Source,"Rating")) hands the function a list of that column’s values.
An error value in the list stops the count. If any item is an error, the function raises that error instead of returning a number. Remove or replace errors first.
Use List.Count when you want every item. List.Count includes null values in its total, while List.NonNullCount leaves them out.

Common questions about List.NonNullCount

What is the difference between List.NonNullCount and List.Count?

List.Count returns the total number of items, including null values. List.NonNullCount returns only the items that are not null.

How do I count the null values instead?

Subtract one from the other: List.Count(myList) - List.NonNullCount(myList) gives you the number of null items.

List of All Power Query Functions

Related Power Query Functions / Articles: