List.Count
Returns how many items are in a list, including any nulls.
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
list: the list to count. A table column is a list, soInvoices[invoiceNumber]works.
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
- A null counts as an item. Use List.RemoveNulls first if you want only filled values.
- Duplicates count too. Use List.Distinct first if you want unique values.
- For a row count on a table, Table.RowCount is the clearer function.
- Compare counts before and after each step that filters rows. A step that drops rows without you knowing is the most common cause of a wrong total.
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.