List.ContainsAll

D

Back to top

T

Back to top

#

Back to top

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

In this article, we’ll take a closer look at List.ContainsAll and explore its M code. We’ll also provide some practical examples to help you understand how the function works and how you can use it in your own data transformations.

What is List.ContainsAll?

List.ContainsAll is a Power Query M function that takes two lists as arguments and returns a Boolean value. The function checks whether all the elements of the first list appear in the second list. If they do, the function returns true; otherwise, it returns false.

Here’s the syntax for List.ContainsAll:

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

As you can see, the function takes two arguments: list1 and list2. List1 is the list of elements to look for, and list2 is the list to search in. The function returns a Boolean value that indicates whether all the elements in list1 are present in list2.

How List.ContainsAll Works

To understand how List.ContainsAll works, let's take a look at some examples. Suppose we have two lists:

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

list2 = {“pear”, “apple”, “mango”, “banana”, “kiwi”}

If we apply List.ContainsAll to these two lists, we get the following result:

List.ContainsAll(list1, list2)

The function returns true, because all the elements of list1 (i.e., "apple", "banana", and "orange") are present in list2.

Now let's take a look at another example. Suppose we have the following two lists:

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

list2 = {“pear”, “apple”, “mango”, “banana”, “kiwi”}

If we apply List.ContainsAll to these two lists, we get the following result:

List.ContainsAll(list1, list2)

The function returns false, because "grape" is not present in list2.

The M Code Behind List.ContainsAll

Now that we know how List.ContainsAll works, let's take a look at its M code. Here's the M code for the function:

(list1 as list, list2 as list) =>

List.Accumulate(

list1,

true,

(state, current) =>

state and List.Contains(list2, current)

)

As you can see, the function takes two parameters: list1 and list2. It then uses List.Accumulate to iterate over each element in list1 and check whether it is present in list2. If all the elements of list1 are present in list2, the function returns true; otherwise, it returns false.

Practical Examples

Now that we understand the M code behind List.ContainsAll, let's take a look at some practical examples. Suppose we have a table of data that contains a column called "Fruit". We want to filter the table to only include rows where the "Fruit" column contains all of the following fruits: "apple", "banana", and "orange".

Here's how we can use List.ContainsAll to achieve this:

let

Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],

FilteredList = Table.SelectRows(

Source,

each List.ContainsAll({“apple”, “banana”, “orange”}, Text.Split([Fruit], “, “))

)

in

FilteredList

In this example, we first split the "Fruit" column into a list using Text.Split. We then apply List.ContainsAll to this list and the list of fruits we want to filter by. This returns a Boolean value that indicates whether the "Fruit" column contains all of the required fruits.

We then use Table.SelectRows to filter the table based on this Boolean value. The result is a table that only includes rows where the "Fruit" column contains all of the required fruits.

List.ContainsAll is a useful function in Power Query M that allows you to determine whether all the elements of one list are present in another list. By understanding how the function works and the M code behind it, you can use List.ContainsAll to perform complex data transformations and filtering operations in your Power Query projects.

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)