Downloads
Power Query MList: pick and buildBeginner

List.Contains

Returns true if a value appears anywhere in a list.

Syntax
List.Contains(list, value, optional equationCriteria)

Returns: A logical value, true or false.

What it does

List.Contains answers "is this value in the list?" It is the simplest way to check a code against an approved list, a vendor against a blocked list or a period against a closed list. It returns true or false, which makes it easy to use in an if or in a filter.

Syntax and arguments

Example

Before a payment is released, the vendor code must be on the approved list. The check is a single yes or no.

let
    ApprovedVendors = {"V-1001", "V-1002", "V-2044"},
    CandidateVendor = "V-2044",
    IsApproved = List.Contains(ApprovedVendors, CandidateVendor),
    Message = if IsApproved then "Release payment" else "Hold for review"
in
    Message

IsApproved is true, so the message is "Release payment". The same test with a different code would return false and the payment would be held.

Example: a second, different use

Filtering a table to rows whose code is on a list: inside Table.SelectRows, each List.Contains(ApprovedVendors, [vendorCode]) keeps only the approved rows.

Things to watch

In business terms

Approved vendor checks, blocked account lists, closed period tests and allowed tax codes are all membership tests. Keep the reference list in its own query so the same list can be used in every check.

Related functions

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