List.AnyTrue
Returns true if at least one item in a list of logical values is true.
List.AnyTrue(list)Returns: A logical value, true or false.
What it does
List.AnyTrue answers the question "is there at least one exception?" It takes a list of true and false results and returns true if any of them is true. Combine it with List.Transform to test each item against a rule, then report whether any item failed.
Syntax and arguments
list: a list of logical values. To test numbers or text, first turn them into true and false results with List.Transform.
Example
A payment run should contain no payment to a vendor that is not on the approved list. The check needs a yes or no answer.
let
PayeeCodes = {"V-1001", "V-2044", "V-9999"},
Approved = {"V-1001", "V-2044"},
NotApproved = List.Transform(PayeeCodes, each not List.Contains(Approved, _)),
AnyProblem = List.AnyTrue(NotApproved)
in
AnyProblem
NotApproved is {false, false, true}, and AnyProblem is true, so the run needs review. The rule answers "any?" in one step, and the list shows which item failed.
Example: a second, different use
Checking whether any month in a budget file has a negative amount: test each month with List.Transform, then pass the results to List.AnyTrue.
Things to watch
- An empty list gives false. A check on an empty list therefore passes, so confirm the list is not empty with List.Count first.
- It returns only yes or no. Use List.Select to see which items failed.
- Nulls in the test results may behave unexpectedly. Make the rule return true or false for every item.
- For table rows, the same idea is Table.SelectRows followed by Table.RowCount.
In business terms
Exception checks in a close, such as any unapproved vendor, any duplicate invoice or any unbalanced journal, are often a single yes or no. Document the rule and keep the list of failing items as evidence.