List.NonNullCount
ListReturns the count of non-null values in the list.
Syntax
List.NonNullCount(list as list) as numberParameters
| Name | Type | Required | Description |
|---|---|---|---|
list | list | Yes | The list to count non-null values in. |
Return Value
number — The number of items in the list that are not null.
Remarks
List.NonNullCount returns the count of non-null items in a list. It is equivalent to List.Count(List.Select(list, each _ <> null)) but more readable. Unlike List.Count, which counts every element including nulls, List.NonNullCount counts only items with actual values.
This function is commonly used for data quality reporting: calculating fill rates, checking how many cells in a column have values, or counting valid responses in survey data. To derive the null count, subtract from List.Count(list).
Note that false, 0, and "" (empty string) are all non-null and are counted. Only actual null values are excluded. To count values meeting a custom condition, use List.Count(List.Select(list, each instead.
Examples
Example 1: Count non-null values
List.NonNullCount({1, null, 3, null, 5})Result | |
|---|---|
| 1 | 3 |
Example 2: All values are non-null
List.NonNullCount({10, 20, 30})Result | |
|---|---|
| 1 | 3 |
Example 3: Calculate data completeness percentage
let
Values = {100, null, 250, null, null, 400, 150},
Total = List.Count(Values),
NonNull = List.NonNullCount(Values),
Completeness = NonNull / Total
in
#table({"Total", "NonNull", "Completeness %"}, {{Total, NonNull, Completeness * 100}})Total | NonNull | Completeness % | |
|---|---|---|---|
| 1 | 7 | 4 | 57.14 |