List.MatchesAny

D

Back to top

T

Back to top

#

Back to top

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

Understanding List.MatchesAny

List.MatchesAny is a function in Power Query M that takes two arguments: a value and a list of values. It returns a Boolean value indicating whether the value is contained in the list. Here is the syntax of the List.MatchesAny function:

List.MatchesAny(list as list, expression as any, optional equationCriteria as any) as logical

The first argument, list, is the list of values that you want to check. The second argument, expression, is the value that you want to check for. The third argument, equationCriteria, is optional and allows you to specify a custom comparison function.

The List.MatchesAny function is case-sensitive by default. This means that if you are checking for a string value, the case of the value must match exactly with the case of the value in the list. However, you can change this behavior by providing a custom comparison function using the equationCriteria argument.

The M Code Behind List.MatchesAny

Now that we have a basic understanding of the List.MatchesAny function, let's take a look at the M code behind it. The M code for List.MatchesAny is relatively simple and can be broken down into two parts:

List.MatchesAny = (list as list, expression as any, optional equationCriteria as any) as logical =>

List.Contains(List.Transform(list, each equationCriteria(_, expression)), true)

The first part of the code defines the List.MatchesAny function and its arguments. The second part of the code is the actual implementation of the function.

The List.MatchesAny function is essentially a wrapper function that calls the List.Contains function with a transformed list. The List.Transform function is used to apply the equationCriteria function to each element in the list, which returns a list of Boolean values indicating whether each element matches the expression. The List.Contains function is then called to check whether any of the values in the transformed list are true.

Using List.MatchesAny in Power Query M

Now that we have a better understanding of the List.MatchesAny function and its M code, let's take a look at some examples of how it can be used in Power Query M.

Example 1: Checking for a Single Value

Suppose you have a list of product categories and you want to check if a particular product belongs to one of these categories. You can use the List.MatchesAny function to check whether the product category is contained in the list:

let

productCategory = “Electronics”,

categoryList = {“Electronics”, “Clothing”, “Books”, “Home Goods”},

result = List.MatchesAny(categoryList, productCategory)

in

result

In this example, the List.MatchesAny function returns true because "Electronics" is contained in the categoryList.

Example 2: Checking for Multiple Values

Suppose you have a list of products and you want to check whether any of them belong to a list of categories. You can use the List.MatchesAny function in combination with the List.Select function to filter the list of products:

let

productList = Table.FromRecords({

[Product = “iPhone”, Category = “Electronics”],

[Product = “T-Shirt”, Category = “Clothing”],

[Product = “War and Peace”, Category = “Books”],

[Product = “Coffee Maker”, Category = “Home Goods”]

}),

categoryList = {“Electronics”, “Clothing”},

filteredList = List.Select(productList, each List.MatchesAny(categoryList, [Category]))

in

filteredList

In this example, the List.Select function is used to filter the productList based on whether the Category column matches any of the values in the categoryList. The List.MatchesAny function is used to check whether each Category value matches any of the values in the categoryList.

List.MatchesAny is a powerful function in Power Query M that allows you to check whether a value is contained in a list of values. By understanding the M code behind this function, you can gain a better understanding of how it works and how it can be used in your data analysis workflows.

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)