List.NonNullCount

List.NonNullCount is a Power Query M function that counts the non-null items in a list. The function returns the number of non-null items in the list.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

List.NonNullCount( list as list ) as number

Description

The List.NonNullCount function returns the number of non-null items in a list. Unlike List.Count, which counts every item regardless of its type or errors, List.NonNullCount specifically excludes null values from its count. Also notice that in the presence of errors within the list, the List.NonNullCount function will throw an error.

Examples

Counting Non-Null Items in a Simple List

In the first example, we examine a list containing both string values and a null value. The following expression counts all values that are not null.

List.NonNullCount( { "a", "b", null, "d", "e" } ) // Output: 4

This function effectively ignores the null value, providing a count of only the desired strings.

Non-Null Count in a Mixed-Type List

The function works just as easily with a list composed of different types of values:

List.NonNullCount( { true, false, null, 100, {1, 2, 3}, [Key="Value"], null } ) // Output: 5

Despite the diversity of data types and the presence of null values, the function accurately counts all non-null elements.

Non-Null Count with Errors in the List

Unlike the List.Count function, List.NonNullCount requires the list containing the values to contain valid values. Errors are not allowed. Because of that, the following expression returns an error:

List.NonNullCount( { 1, 2, 3, null, "text", 5/"a", null } ) // Output: Error

The given error is: “Expression.Error: We cannot apply operator / to types Number and Text”. This is one of the different error messages in the M language.

Related functions

Other functions related to List.NonNullCount are:

• List.Average
• List.Count
• List.Covariance
• List.Median
• List.Mode
• List.Modes
• List.Percentile
• List.Product
• List.StandardDeviation
• List.Sum

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-nonnullcount