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

List.Count returns the number of items in a list as a whole number. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to know how many values are in a list, how many rows sit in a column, or how many items are left after a filter, this is the function to reach for.

Table of Contents

Syntax of List.Count Function

List.Count(list as list) as number

where

list (required, list). The list whose items you want to count. It can be a literal list, a column pulled from a table, or the output of another list function.

Returns: a number, the count of items in the list. An empty list {} returns 0, and null items are counted like any other value.

In plain terms, you hand it a list and it tells you how many things are in it.

Example 1: Count the items in a list

Count how many values are in a simple list of fruit.

let
    Source = List.Count({"apple", "banana", "cherry", "date"})
in
    Source

Result: 4

There are four text values in the list, so the function returns 4.

Example 2: Count rows in a table column

Counting the values in a column is one of the most common uses of List.Count.

Say you have a PoolLessons table and you want to know how many swimmers are signed up.

Here is the starting data:

Swimmer	Lane
Priya	1
Marcus	2
Elena	3
Tomas	4

Reference the table, then count the Swimmer column:

let
    Source = Excel.CurrentWorkbook(){[Name="PoolLessons"]}[Content],
    Result = List.Count(Source[Swimmer])
in
    Result

Result: 4

Source[Swimmer] turns the column into a list, and List.Count returns how many values it holds.

Example 3: Count items after filtering

You can count the result of another list operation to answer questions like “how many rows pass this test?” It is handy alongside other column checks, like when you find the maximum value in a column in Power Query.

Here you have a TrailHikes table and you want to know how many hikes are longer than 5 miles.

Here is the starting data:

Trail	Miles
Cedar Ridge	8
Otter Falls	3
Granite Loop	12
Willow Bend	2

Filter the Miles column with List.Select, then count what is left:

let
    Source = Excel.CurrentWorkbook(){[Name="TrailHikes"]}[Content],
    LongHikes = List.Select(Source[Miles], each _ > 5),
    Result = List.Count(LongHikes)
in
    Result

Result: 2

List.Select keeps only the values above 5, and List.Count reports how many made the cut.

Example 4: Count words in a sentence

Split text into a list first, then count the pieces. This is a quick way to count words.

let
    Sentence = "Water the orchids before the frost arrives",
    Words = Text.Split(Sentence, " "),
    Result = List.Count(Words)
in
    Result

Result: 7

Text.Split breaks the sentence on each space into a list of words, and List.Count returns how many there are.

Example 5: Nulls are counted, an empty list is zero

List.Count counts every position in the list, including null values. An empty list returns 0.

let
    WithNulls = List.Count({"red", null, "blue", null, "green"}),
    Empty = List.Count({}),
    Result = {WithNulls, Empty}
in
    Result

Result: WithNulls is 5, Empty is 0.

The first list has five positions, two of which are null, so the count is 5. The empty list has nothing in it, so the count is 0.

Things to keep in mind with List.Count

It works on a list, not a table. Pass a column with Table[ColumnName] to get a list first. To count table rows directly, use Table.RowCount.
The argument must be a list. Passing a single value throws Expression.Error: We cannot convert a value of type ... to type List. Wrap a scalar in { } if you really need a one-item list.
It counts items, not distinct items. Duplicates each add to the total. To count only unique values, wrap the list in List.Distinct first: List.Count(List.Distinct(myList)), or remove duplicates in Power Query beforehand.
To skip blanks, use List.NonNullCount. List.Count includes every null, so for a count that ignores blanks switch to List.NonNullCount, or remove null values in Power Query first.

Common questions about List.Count

How do I count the rows in a single column?

Turn the column into a list with Table[ColumnName], then pass it to List.Count, as in Example 2. To count all rows of a table regardless of column, use Table.RowCount.

What is the difference between List.Count, Table.RowCount, and List.NonNullCount?

List.Count counts items in a list (including null), Table.RowCount counts rows in a table, and List.NonNullCount counts only the non-null items in a list.

List of All Power Query Functions

Related Power Query Functions / Articles: