List.Select Function in Power Query M (5 Easy Examples)

If you have a list and you want to keep only the items that pass a test, the List.Select function is what you reach for. It walks through each item, checks your condition, and hands back a new list with just the matches.

Table of Contents

Syntax of List.Select Function

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

where

list (required, list). The list you want to filter.
selection (required, function). The test applied to each item. It returns true to keep the item or false to drop it. The current item is referred to as _.

Returns: a new list holding only the items for which selection returned true. If nothing matches, you get an empty list {}.

In plain terms, you hand it a list and a rule, and it gives back only the items that follow the rule.

Example 1: Keep numbers above a threshold

Say you have a list of scores and you want only the ones above 50.

List.Select({45,12,88,7,63,20,95},each _ > 50)

Result: {88, 63, 95}

Each number is tested against _ > 50, and only the three that pass are kept.

Example 2: Keep only the even numbers

Here you want to filter a list down to its even values.

List.Select({11,24,37,40,53,66,78},each Number.Mod(_,2) = 0)

Result: {24, 40, 66, 78}

Number.Mod(_,2) gives the remainder after dividing by 2, so a result of 0 means the number is even.

Example 3: Keep text items that start with a letter

You can test text too. Here you keep only the items that start with a lowercase a.

List.Select({"apricot","Avocado","almond","Banana","apple"},each Text.StartsWith(_,"a"))

Result: {"apricot", "almond", "apple"}

Avocado gets dropped because the match is case-sensitive, so its capital A is not the same as a.

Example 4: Pull a table column into a list and filter it

Filtering a single column from a table is a really common job. You grab the column as a list first, then filter that list.

Here is the starting data:

Customer	Amount
Rohan Mehta	1450
Sarah Collins	780
Priya Nair	2300
James Walker	950
Daniel Reed	1620

Pull the Amount column into a list with Source[Amount], then keep the values over 1000:

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Amounts = Source[Amount],
    BigOrders = List.Select(Amounts,each _ > 1000)
in
    BigOrders

This keeps only the order amounts above 1000.

Result: {1450, 2300, 1620}

The three smaller amounts (780 and 950) are left out.

Example 5: Combine two conditions with and

You can chain tests together inside the function, and you can also test list membership with List.Contains. Here you keep numbers that sit between 30 and 60.

List.Select({18,45,60,33,72,27,50},each _ >= 30 and _ <= 60)

Result: {45, 60, 33, 50}

Both halves of the condition must be true, so a number has to be at least 30 and at most 60 to survive.

Things to keep in mind with List.Select

It always returns a list, even with a single match. One match comes back as a one-item list like {45}, not as the bare value. Use List.First if you need the value on its own.
No match gives an empty list, not an error. When nothing passes the test you get {}, so check with List.IsEmpty, or use List.Count, before assuming there is data to work with.
The test must return true or false. A selection function that returns anything else throws Expression.Error: We cannot convert the value ... to type Logical. Compare against something so the result is a logical.
Order is preserved. The kept items stay in their original order, so List.Select never sorts for you. Wrap it in List.Sort if you need a specific order.
It filters, it does not change values. Items come back exactly as they were. To transform the kept items as well, follow up with List.Transform.

Common questions about List.Select

What is the difference between List.Select and List.Where?

There is no functional List.Where in M. The filtering function is List.Select, and the each keyword with _ is how you write the per-item test.

How is List.Select different from Table.SelectRows?

List.Select filters a list of values, while Table.SelectRows filters the rows of a table and keeps every column. Use List.Select once you have already reduced your data to a single list.

List of All Power Query Functions

Related Power Query Functions / Articles: