List.Contains
Returns true if a value appears anywhere in a list.
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
list: the list to search.value: the value to look for.equationCriteria(optional): how values are compared, such asComparer.OrdinalIgnoreCaseto ignore capital letters.
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
- Comparisons are exact by default. "v-1001" does not match "V-1001" unless you pass Comparer.OrdinalIgnoreCase.
- Spaces matter. Trim the value before you test it.
- For a long list checked against many values, a join in the source or a merge is faster than many calls.
- A null in the list is compared like any other item, so decide how you want blanks to behave.
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.