List.MatchesAll

D

Back to top

T

Back to top

#

Back to top

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

Syntax

The syntax for the List.MatchesAll function is as follows:

List.MatchesAll(list1 as list, list2 as list) as logical

The first argument, list1, is the list that we want to check for matches. The second argument, list2, is the list that we want to compare against. The function returns a logical value, which is TRUE if all the elements in list1 match the corresponding elements in list2, and FALSE otherwise.

Example

Let's take a look at an example to illustrate how the List.MatchesAll function works. Suppose we have two lists:

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

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

We can use the List.MatchesAll function to check whether all the elements in list1 match the corresponding elements in list2:

List.MatchesAll(list1, list2)

This will return TRUE, since all the elements in list1 match the corresponding elements in list2.

Practical Applications

The List.MatchesAll function can be useful in a variety of scenarios. For example, suppose we have a dataset containing information about employees, including their first name, last name, and department. We also have a list of departments that we want to filter the dataset by. We can use the List.MatchesAll function to check whether the department of each employee matches any of the departments in the list, and filter the dataset accordingly.

Here's an example of how we can use the List.MatchesAll function to filter the dataset:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WclTSUTIwNjJQVrJRMjNQ0i5JTUkFQlK8xSjJQ0gUvNjIxKLXEPzs3MjAyNDI2MDQzNrAzMjAzNjEyNjMzNjQxszMDIwMzYxMjYzMTQzMrMBKjJwMjAzNjEyNjM0MzY0Mfg1UAgGQMAAAAAAAAAAAA==”, BinaryEncoding.Base64)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#”First Name” = _t, #”Last Name” = _t, Department = _t]), Encoding.Unicode),

departments = {“Sales”, “Marketing”},

filtered = Table.SelectRows(Source, each List.MatchesAll(departments, {_[Department]}))

in

filtered

In this example, we first define the dataset as a table called Source. We also define a list of departments that we want to filter by. We then use the Table.SelectRows function to filter the dataset, using the List.MatchesAll function to check whether the department of each employee matches any of the departments in the list.

The List.MatchesAll function in Power Query is a powerful tool that allows us to check whether all the elements in two lists match. It can be used in a variety of practical scenarios, such as filtering datasets by a list of values. By understanding the M code behind this function, we can unlock its full potential and make our data analysis more efficient and effective.

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)