List.Select

Updated on
Rick de Groot

List.Select is a Power Query M function that returns a list of values from an input list that match a specified selection condition. The function returns a new list containing the values that match the selection condition.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

List.Select(
   list as list,
   selection as function,
) as list

Description

The List.Select function extracts and compiles a new list of values from an input list based on a selection condition. This function allows you to extract only the necessary elements based on your custom condition.

Examples

To understand the potential of the List.Select function, let’s dive into some examples illustrating its applications.

Consider a simple list of integers. We can leverage the power of the List.Select function to return only the elements that are less than or equal to 2.

List.Select( { 1, 1, 2, 3, 4, 5, 6 },             // Output: { 1, 1, 2 }
             each _ <= 2 )

Similarly, we can filter a list to include negative values only.

List.Select( { -1, -2, 5, 6 },                    // Output: { -1, -2 }
             each _ < 0 )

Expanding the possibilities further, the List.Select function allows us to incorporate more complex functions into our selection conditions. For instance, we can filter for even or odd numbers using the Number.IsEven and Number.IsOdd functions.

List.Select( { 1, 1, 2, 3, 4, 5, 6 },             // Output: { 2, 4, 6 }
             each Number.IsEven( _ ) )

List.Select( { 1, 1, 2, 3, 4, 5, 6 },             // Output:  { 1, 1, 3, 5 }
             each Number.IsOdd( _ ) )

The List.Select function isn’t just limited to numeric values, it can handle text values as well. Here, we extract all text values from a mixed list:

List.Select( { "a", 1, "c", 3, 4, 5, 6 },         // Output: { "a", "c" }
             each _ is text )                     // 

List.Select( { "a", 1, "c", 3, 4, 5, 6 },         // Output: { "a", "c" } 
             each Value.Type( _ ) = type text )

Similarly, we can adjust the condition to return numeric values:

List.Select( { "a", 1, "c", 4, 5, 6 },            // Output: { 1, 4, 5, 6 }
              each _ is number )            

List.Select( { "a", 1, "c", 4, 5, 6 },            // Output: { 1, 4, 5, 6 }
             each Value.Type( _ ) = type number )

Finally, the List.Select function can also handle more complex data structures. In the following example, it returns only list values:

List.Select( { "a", 1, "c", { 3, 4, 5 }, 6 },     // Output: { { 3,4,5 } }
             each _ is list )

List.Select( { "a", 1, "c", { 3, 4, 5 }, 6 },     // Output: { { 3,4,5 } }
             each Value.Type( _ ) = type list )

As these examples show, the List.Select function is a useful tool for anyone working with lists.

Learn more about List.Select in the following articles:

Other functions related to List.Select are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-select

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy