Downloads
Power Query MList: pick and buildIntermediate

List.ContainsAny

Returns true if any of several values appears in a list.

Syntax
List.ContainsAny(list, values, optional equationCriteria)

Returns: A logical value, true or false.

What it does

List.ContainsAny checks a list against a second list of values and returns true if at least one of them is present. It is a quick way to flag a row or a batch when it has any of several codes, such as any of three restricted payment methods, without writing one test per code.

Syntax and arguments

Example

A memo field on bills is checked for any of three words that signal a possible duplicate or a rush payment. The batch is flagged if any word appears.

let
    BatchCodes = {"STD", "URGENT", "STD"},
    Watch = {"URGENT", "MANUAL", "CHECK-ONLY"},
    NeedsReview = List.ContainsAny(BatchCodes, Watch)
in
    NeedsReview

NeedsReview is true, because "URGENT" is in the batch. The test is one line, and the watch list can be changed without rewriting the logic.

Example: a second, different use

Checking a list of account codes against a list of restricted accounts, as one of several control tests on a journal batch.

Things to watch

In business terms

Flagging journal batches, scanning memo fields for keywords, and checking payments against lists of blocked or restricted codes all use this pattern. Document the watch list as part of the control.

Related functions

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