Skip to content

List.Count in Power Query: 10 examples

List.Count Featured Image

In this post, we look at the List.Count Power Query M function. It is an aggregation function that counts the number of 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 items in a list.

Return Value

The count of all values in a list as a number.

Syntax

List.Count(list as list) as number

Parameters

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

Usage Notes

The List.Count function is categorized as a List function. It returns the count of items in a list supplied as a single parameter.

While Power Query uses a zero base, the List.Count function starts counting at 1. A result of 0 indicates the list contains no items.

List.Count includes all items from the list, including null values and errors. To exclude null items, use the List.NonNullCount function.

The two count transformations included in the user Interface do not use List.Count.

  • 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 and List.Distinct functions.
Count transformations in User Interface

Examples

Example #1: Count individual list items

A list can be created using standard M code syntax with the list items included within curly brackets.

= List.Count({"Alpha", "Bravo", "Charlie", "Delta"})

Returns: 4

Example #2: Count from a separate list query

If the list is a separate query, the query name can be used in place of the list.

The following is a list query called Example List.

= {"Alpha", "Bravo", "Charlie", "Delta", "Echo", "Foxtrot"}

The above list is used inside the List.Count function as shown below 

= List.Count(#"Example List")

Returns: 6

Example #3: Count from a single Table column

A single table column is treated as a list. Therefore, this can be used as the parameter in the List.Count function.

Table for List.Count Example

The table above is named Example Table.

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

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

Returns: 16

Example #4: Count different data types

List.Count includes all data types, as shown by the function below.

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

Returns: 4

Note: To exclude null items, use the List.NonNullCount function.

Example #5: Count distinct items

The example below shows how to count distinct items using the List.Distinct function. Alpha and Bravo are duplicated; therefore, only one instance of each distinct value is counted.

= List.Count(List.Distinct({"Alpha", "Bravo", "Charlie", "Delta", "Alpha", "Bravo"}))

Returns: 4

Example #6: Count a list of lists

List.Count only works with a one-dimensional list. The example below shows a list of 4 items, each of which is another list of two items. List.Count does not look at items in the sub-lists, but counts the number of lists.

= List.Count({
  {"Alpha", 3}, 
  {"Bravo", 12}, 
  {"Charlie", 33},
  {"Delta", 18}
})

Returns: 4

Example #7: Count the number of words

The example below counts the number of words returned by the Text.Split function. The space character splits the text string into separate list items. There are 5 spaces; therefore, the result is 6 words.

= List.Count(Text.Split("How many words in this text?"," "))

Returns: 6

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

List.Count can be used as a parameter inside other functions. The example below generates a list of numbers using the List.Numbers function.

The first parameter of List.Numbers is the start; the second parameter is the count. Therefore, the function creates a list of 4 items starting at 0.

= List.Numbers(0,List.Count({"Alpha","Bravo","Charlie","Delta"}))

Returns: {0,1,2,3}

List.Numbers example

Alternatively, the following code creates a list of 5 items from 0 to 4:

= {0..List.Count({"Alpha","Bravo","Charlie","Delta"})}

Returns: {0,1,2,3,4}

Example #9: Count an empty list

A list may contain zero items, this is represented by empty curly brackets.

As there are no items in the example below, the function returns 0.

= List.Count({})

Returns: 0

Example #10: Counting multiple columns inside a custom column

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

The following transformation uses the same Table as Example 3. List.Count includes the Item, Size and Region columns in a list.

The formula in the screenshot above is:

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

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

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

The Table.AddColumn function provides row context. Therefore, the Item, Size and Region values are based on their row, rather than the entire column.

Returns: 3 (in every row of the Table).

Result of Count Column in Custom Column

Related Tutorials

List.Count is used in the following Tutorials:

Related Functions