List.Contains Function in Power Query M (6 Easy Examples)

List.Contains checks whether a list holds a given value and returns true or false. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to test whether a value is in a list, or filter a table down to the rows whose value is one of a small set, this is the function you reach for.

Table of Contents

Syntax of List.Contains Function

List.Contains(list as list, value as any, optional equationCriteria as any) as logical

where

list (required, list). The list to search inside.
value (required, any). The value to look for within list.
equationCriteria (optional, any). Controls how values are compared, for example case-insensitive matching. Omit it for the default exact match.

Returns: a logical value, either true or false. An empty list always returns false.

In plain terms, you give it a list and a value, and it tells you whether the value is one of the items in the list.

Example 1: Check if a value is in the list

Test whether a list of streaming devices includes Chromecast.

List.Contains({"Roku","Chromecast","Fire Stick","Apple TV"}, "Chromecast")

Result: true

The value is one of the items in the list, so the function returns true.

Example 2: When the value is not present

List.Contains({"Roku","Chromecast","Fire Stick","Apple TV"}, "Nvidia Shield")

Result: false

Nvidia Shield is not in the list, so you get false.

Example 3: Matching is case-sensitive by default

List.Contains({"Roku","Chromecast","Fire Stick","Apple TV"}, "chromecast")

Result: false

The default comparison is case-sensitive, so chromecast does not match Chromecast.

Example 4: Ignore case with a comparer

Pass Comparer.OrdinalIgnoreCase as the third argument to match regardless of case.

List.Contains({"Roku","Chromecast","Fire Stick","Apple TV"}, "chromecast", Comparer.OrdinalIgnoreCase)

Result: true

Now chromecast matches Chromecast because the comparer ignores case.

Example 5: Filter rows whose value is in a list

Filtering rows is the most common real-world use of List.Contains.

Say you have a Shipments query with a Tracking and a Warehouse column.

You want to keep only the rows shipped from Denver or Memphis.

Here is the starting data:

Tracking	Warehouse
TRK001	Denver
TRK002	Memphis
TRK003	Reno
TRK004	Memphis
TRK005	Tampa
TRK006	Denver

Now wrap List.Contains inside Table.SelectRows:

let
    Source = Excel.CurrentWorkbook(){[Name="Shipments"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each List.Contains({"Denver","Memphis"}, [Warehouse]))
in
    #"Filtered Rows"

This keeps every row whose Warehouse is one of the two values in the list.

The result has only the matching rows:

Tracking	Warehouse
TRK001	Denver
TRK002	Memphis
TRK004	Memphis
TRK006	Denver

The Reno and Tampa rows are dropped because their warehouse is not in the list.

Example 6: An empty list returns false

List.Contains({}, "Roku")

Result: false

There is nothing to match against, so an empty list always returns false.

Things to keep in mind with List.Contains

It matches whole values, no wildcards or partial text. List.Contains({"abc"},"a") is false because a is not an item. To test a substring use Text.Contains.
The value type should match the list items. List.Contains({1,2,3},"1") is false because the text "1" is not equal to the number 1. Convert first with Number.From or Text.From.
null is a normal value to search for. List.Contains({1,null,3},null) returns true, so a null in the list is matched like any other item.

Performance and query folding

Against a foldable source like SQL Server, a default List.Contains inside Table.SelectRows can fold to an IN filter, so the database does the work.

Adding an equationCriteria comparer usually breaks folding, so the filter runs locally instead. On Excel, CSV, or folder sources it always runs locally, which is fine for thousands of rows.

Common questions about List.Contains

What is the difference between List.Contains and Table.Contains?

List.Contains checks whether a single value is in a list. Table.Contains checks whether a whole record (a row of named fields) is in a table.

Can I check for several values at once?

Yes, with List.ContainsAny or List.ContainsAll. List.ContainsAny(list, {"Denver","Memphis"}) is true if the list holds either value, and List.ContainsAll requires all of them.

List of All Power Query Functions

Related Power Query Functions / Articles: