Table.RowCount
Returns how many rows a table has.
Table.RowCount(table as table) as numberReturns: A number.
What it does
Table.RowCount returns the number of rows in a table. Use it for control checks, such as confirming that an import picked up every line, or for a message that reports how many items were loaded. It returns a single number, not a table, so it cannot be a step in the table chain. Put it in its own step or in a check.
Syntax and arguments
- table: the table to count. Required.
Example
A bank export should never be empty. The row count is checked, and if it is zero, the query stops with a clear message.
let
Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
Count = Table.RowCount(Source),
Checked = if Count = 0
then error Error.Record("Empty export", "The bank export has no rows.")
else Source
in
Checked
If the file has rows, the table passes through unchanged. If it has none, the query stops and the message explains why.
Example: a second, different use
A control total compares the number of lines loaded with the number expected from the source system's report. The two numbers are shown side by side for the preparer.
let
Source = Excel.CurrentWorkbook(){[Name = "GLExport"]}[Content],
Loaded = Table.RowCount(Source),
ExpectedFromReport = Excel.CurrentWorkbook(){[Name = "ReportTotals"]}[Content],
Expected = Table.RowCount(ExpectedFromReport),
Result = Table.FromRecords({[Check = "Lines loaded", Loaded = Loaded, Expected = Expected]})
in
Result
Things to watch
- This counts rows, not distinct things. Three lines for one invoice count as three rows. Use Table.Group or Table.Distinct for distinct counts.
- Counting forces the query to load its data. Keep the count out of the main report table.
- The count is taken at the step where you place it, so filters after it are not counted.
- Query folding: the count can fold to a database COUNT, but whether it does depends on the steps before it. Check View Native Query.
In business terms
Row counts and control totals are the simplest evidence that an import is complete. A preparer who records the expected and loaded counts can show the reviewer that nothing went missing.