Skip to content

List.NonNullCount in Power Query: 10 examples

List.NonNullCount

In this post, we look at the List.NonNullCount Power Query M function. It is an aggregation function that counts the number of non-null items in a list.

Along with detailed notes, there are 10 examples to show how to use the function in different contexts.

Table of Contents:

Purpose

Returns the number of non-null items in a list.

Return Value

The count of all non-null values in a list as a number.

Syntax

List.NonNullCount(list as list) as number

Parameters

  • list – A Power Query list. This list can be:
    • individual items contained within curly brackets e.g. {“Alpha”,4,null}
    • Existing query/step with a list type as an output.
    • Function which returns a list.

Usage Notes

The List.NonNullCount function is in the List function category, it returns the count of non-null items in a list supplied by a single parameter.

The function starts counting at 1. A result of 0 indicates the list contains no items or only null items.

To include null items in the count, use the List.Count function

Both count transformations available in the user interface use the List.NonNullCount function.

  • Transform [1] > Statistics [2] > Count Values [3] uses the List.NonNullCount function.
  • Transform[1] > Statistics[2] > Count Distinct Values [3] uses the combination of List.NonNullCount with a nested List.Distinct function.
Count transformations in User Interface

Examples

Example #1: Count individual list items

The list parameter can be the standard M code syntax, which includes comma-separated items in curly brackets.

There are 4 items in the list below, but as null values are excluded, the function returns 3 (Alpha, Bravo, and Charlie)

= List.NonNullCount({"Alpha", "Bravo", null, "Charlie"})

Returns: 3

Example #2: Count from a separate list query

The list parameter can be a query name, where the query’s result is a list.

The following is a list query called Example List.

= {"Alpha", "Bravo", null, "Charlie", "Delta", "Echo"}
List to be used for examples

The above list is used inside the function as shown below. There is one null value, so the result is 5.

= List.NonNullCount(#"Example List")

Returns: 5

Example #3: Count from a single Table column

Within Power Query, a single table column is a list. Therefore, this column reference can be used as the parameter inside the List.NonNullCount function.

Table to be used for examples

The table above is named Example Table.

The code below counts the number of items in the Item column from the Example Table.

= List.NonNullCount(#"Example Table"[Item])

Returns: 16

If we used the same function on the Value column, it would return 14 as there are null values in rows 4 and 10.

= List.NonNullCount(#"Example Table"[Value])

Returns: 14

Example #4: Count different data types

List.NonNullCount includes all data types, except null. This is demonstrated by the function below. Text, numbers, and logic are included, but nulls are excluded.

= List.NonNullCount({"Alpha", 3, true, null})

Returns: 3

Note: To include null items, use the List.Count function.

Example #5: Count distinct items

The example below shows how to count distinct items using a nested List.Distinct function. This is the same function generated by clicking Transform > Statistics > Count Distinct Values from the user interface.

In the code below, Alpha and Bravo are duplicated; therefore, these items are counted only once, and null values are excluded.

= List.NonNullCount(List.Distinct(
  {"Alpha", "Bravo", null, "Charlie", "Alpha", "Bravo"}))

Returns: 3

Example #6: Count the number of null items

List.Count includes null, while List.NonNullCount excludes them. Therefore, the calculated variance is the number of null values.

= List.Count({"Alpha", "Bravo", "Charlie"})
  - List.NonNullCount({"Alpha", "Bravo", null})

Returns: 1

Example #7: Count the number of words

The Text.Split function splits text into a list. In the example below, the space character splits the text string into 6 separate list items. Text.Split is nested inside the List.NonNullCount function.

= List.NonNullCount(Text.Split("Count the words in this text?"," "))

Returns: 6

Example #8: Used in a formula to generate a list of numbers

List.NonNullCount can be used as a parameter with other Power Query syntax. The example below generates a list of sequential numbers.

The first digit is the start number; the second digit is the end number. The end number is calculated by the List.NonNullCount function.

= {0..List.NonNullCount({>"Alpha", "Bravo", null, "Charlie"})}

Returns: {0,1,2,3}

Example #9: Count an empty list or a null list

For List.NonNullCount, empty curly brackets and a list of null values have the same value.

As there are no items in either list, the function returns TRUE.

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

Returns: TRUE

Example #10: Counting multiple columns inside a custom column

This example shows how to use List.NonNullCount inside a custom column.

In the screenshot below, we start with the Table from Example 3. A custom column has been added which uses List.NonNullCount to count the Item, Size, Region, and Value columns in a list.

Custom Column Added

The formula in the screenshot above is:

=List.NonNullCount({[Item],[Size],[Region],[Value})

After clicking OK, the full M code generated is as follows:

= Table.AddColumn(
  Source, 
  "Count Columns", 
  each List.NonNullCount({[Item], [Size], [Region], [Value]})
)

The references to the Item, Size, Region, and Value columns are within the row context created by Table.AddColumn. Therefore, the result from the custom column is based on row values, rather than the entire column.

Returns: 4 where Value is non-null, or 3 where Value is null.

Related Tutorials

List.NonNullCount is used in the following Tutorials:

  • None

Related Functions