List.Select

D

Back to top

T

Back to top

#

Back to top

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

Overview of the List.Select Function

The List.Select function is used to filter a list based on a condition. It takes two arguments: the first argument is the list to filter, and the second argument is the condition to filter on. The condition is specified using a function that takes a single argument, which is the item from the list being evaluated.

For example, consider the following list:

{“apple”, “banana”, “cherry”, “date”, “elderberry”}

Suppose we want to filter this list to include only items that start with the letter "b". We can do this using the List.Select function as follows:

List.Select({“apple”, “banana”, “cherry”, “date”, “elderberry”}, each Text.StartsWith(_, “b”))

The second argument to List.Select is a function that takes a single argument (represented by the underscore) and returns a Boolean value indicating whether the item should be included in the filtered list. In this case, we use the Text.StartsWith function to test whether the item starts with the letter "b".

The resulting list will be:

{“banana”}

The M Code Behind List.Select

The M code behind the List.Select function is relatively simple, but knowing how it works can help you to better understand how to use the function and create more complex queries.

The basic structure of the M code for List.Select is as follows:

List.Select(list as list, condition as function) as list =>

List.Select(list, each condition(_))

The first line specifies the input parameters for the function: the list to filter (list) and the condition to filter on (condition). The second line is the actual implementation of the function, which applies the condition to each item in the list using the List.Select function.

The underscore (_) represents the current item being evaluated. The condition function is applied to each item in the list, and only items for which the condition returns true are included in the filtered list.

Advanced Usage of List.Select

While the basic usage of List.Select is fairly straightforward, there are many advanced use cases that can be accomplished using this function.

One common use case is to filter a list based on multiple conditions. This can be achieved using the List.AllTrue or List.AnyTrue functions in combination with the List.Select function.

For example, suppose we want to filter a list of sales data to include only rows where the sales amount is greater than $100 and the sales region is "West". We can use the following code:

List.Select(salesData, each List.AllTrue({_[SalesAmount] > 100, _[SalesRegion] = “West”}))

The List.AllTrue function takes a list of Boolean values and returns true if all of the values are true. We create a list of two conditions using the curly braces ({}) syntax and pass this list as an argument to List.AllTrue. The resulting function returns true only if both conditions are true, effectively filtering the list to include only rows where the sales amount is greater than $100 and the sales region is "West".

The List.Select function is a powerful tool for filtering and selecting specific items from a list in Power Query. Understanding the M code behind this function can help you to create more complex queries and better manipulate data in Power Query. With the knowledge gained from this article, you can take your data transformation and cleansing skills to the next level.

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)