Downloads
Power Query MList: summarizeBeginner

List.Count

Returns how many items are in a list, including any nulls.

Syntax
List.Count(list)

Returns: A whole number, the number of items.

What it does

List.Count tells you how many items a list has. It is the first thing to check after a refresh: if last month had several hundred invoices and this month shows a handful, something went wrong upstream. It counts every item, including nulls and duplicates. To count only real values, remove nulls first, and to count different values, combine it with List.Distinct.

Syntax and arguments

Example

A close checklist needs a record count for the bank file that was loaded this morning, and a count of how many lines have an amount.

let
    Lines = {"deposit", "withdrawal", null, "fee", "deposit"},
    AllLines = List.Count(Lines),
    WithAmount = List.Count(List.RemoveNulls(Lines))
in
    WithAmount

AllLines is 5, because the null still counts as an item. WithAmount is 4, because the blank line is removed first. Both numbers belong in the close file, so a reader can see how many blank lines the file contained.

Example: a second, different use

Counting distinct customers: List.Count(List.Distinct(Invoices[customerName])).

Things to watch

In business terms

Record counts are the basic control in any data load: rows in the source, rows loaded, rows rejected. Keeping these numbers in the close file gives an auditor a simple trail from the source to the report.

Related functions

All Power Query M functions · View as Markdown · M basics