List.ContainsAny

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.ContainsAny

What is the List.ContainsAny Function?

The List.ContainsAny function is a built-in function in Power Query’s M language. Its primary purpose is to check whether a list contains any of the items in another list. The function takes two arguments: the first argument is the list to check, and the second argument is the list of items to look for.

The syntax for List.ContainsAny is as follows:

List.ContainsAny(list as list, values as list) as logical

The function returns a logical value (true or false) depending on whether any of the items in the second list are found in the first list.

How Does List.ContainsAny Work?

List.ContainsAny works by iterating over each item in the second list and checking whether it is present in the first list. If any of the items in the second list are found in the first list, the function returns true. Otherwise, it returns false.

Here is an example of how List.ContainsAny works:

let

list1 = {“apple”, “banana”, “orange”},

list2 = {“banana”, “pear”},

result = List.ContainsAny(list1, list2)

in

result

In this example, the function checks whether any items in list2 ("banana" and "pear") are found in list1 ("apple", "banana", "orange"). Since "banana" is found in list1, the function will return true.

Examples of Using List.ContainsAny

List.ContainsAny can be used in a wide range of scenarios in Power Query. Here are some examples:

Example 1: Checking Whether a Column Contains Any of Several Values

Suppose we have a table of customer data with a column called "State". We want to filter the table to only include rows where the State column contains any of several specific values. We can use List.ContainsAny to do this.

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMjIwMjJRsgxNzYjNzY0MjQ1MzKwMTWykFmSmpFYk5JcV5mkApz8ooA”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#”State” = _t]),

StatesToInclude = {“CA”, “TX”, “NY”, “FL”},

FilteredRows = Table.SelectRows(Source, each List.ContainsAny(StatesToInclude, {_[State]})),

#”Filtered Rows” = Table.SelectColumns(FilteredRows,{“State”})

in

#”Filtered Rows”

In this example, we define a list called "StatesToInclude" with the values we want to check for. We then use List.ContainsAny within the Table.SelectRows function to filter the table to only include rows where the State column contains any of the values in the StatesToInclude list.

Example 2: Checking Whether a Text String Contains Any of Several Values

Suppose we have a column of text strings that represent product names. We want to filter the table to only include rows where the product name contains any of several specific words. We can use List.ContainsAny to do this.

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMjIwMjJRsgxNzYjNzY0MjQ1MzKwMTWykFmSmpFYk5JcV5mkApz8ooA”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#”Product Name” = _t]),

WordsToInclude = {“apple”, “banana”, “orange”},

FilteredRows = Table.SelectRows(Source, each List.ContainsAny(WordsToInclude, Text.SplitAny(_[Product Name], “W”))),

#”Filtered Rows” = Table.SelectColumns(FilteredRows,{“Product Name”})

in

#”Filtered Rows”

In this example, we define a list called "WordsToInclude" with the words we want to check for. We then use List.ContainsAny within the Table.SelectRows function to filter the table to only include rows where the Product Name column contains any of the words in the WordsToInclude list. We use the Text.SplitAny function to split the Product Name column into separate words, so that we can search for individual words rather than entire phrases.

List.ContainsAny is a powerful function in Power Query's M language that can be used in a wide range of scenarios. By understanding how it works and how to use it effectively, you can extend the capabilities of Power Query to handle even the most complex data transformation tasks.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)