Table.IsEmpty
Returns true if the table has no rows, and false if it has at least one.
Table.IsEmpty(table as table) as logicalReturns: true or false.
What it does
Table.IsEmpty answers one question: does this table have any rows at all? It is the simplest guard for a monthly file that might arrive with no data. A row that holds only blanks still counts as a row, so the test is about whether any row exists, not whether the values are filled.
Syntax and arguments
- table: the table to test. Required.
Example
A month with no transactions produces an empty export. Instead of an empty report, the query returns a single line that says so.
let
Source = Excel.CurrentWorkbook(){[Name = "Transactions"]}[Content],
Result = if Table.IsEmpty(Source)
then #table({"Message"}, {{"No transactions in this export"}})
else Source
in
Result
When the table has rows, it passes through unchanged. When it is empty, the one-line message table is returned in its place.
Example: a second, different use
A filtered list of exceptions may be empty. The query reads the answer and keeps a status line for the summary.
let
Source = Excel.CurrentWorkbook(){[Name = "GL"]}[Content],
Exceptions = Table.SelectRows(Source, each [Amount] > 10000 and [Approved] = null),
Status = if Table.IsEmpty(Exceptions) then "No exceptions" else "Exceptions to review"
in
Status
Things to watch
- A table with rows of blanks is not empty. Filter the blank rows out first if you need to test for real content.
- The test reads the table, so placing it on a large query may force a full load. Check the performance of the query.
- The result is true or false, so use it inside an if, not as a table step.
- Query folding: an empty test can fold into an EXISTS check on some sources. Check View Native Query.
In business terms
A monthly process that quietly produces an empty report can look finished when it is not. A clear message that the file was empty is what the reviewer needs to see, before the numbers go out.