A

B

C

D

E

F

G

H

I

J

L

#

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)

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”})

#”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

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

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

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

#”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)
   	Power BI Training for Facility Management Professionals (2 Days)
 	£1,990.00 Add to basketBuy Now
 
 
 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)