List.SingleOrDefault

D

Back to top

T

Back to top

#

Back to top

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

What is the List.SingleOrDefault function?

The List.SingleOrDefault function is used to retrieve the single element from a list that matches a specific condition. If there is more than one element that matches the condition, an error is returned. If there are no elements that match the condition, a null value is returned.

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

List.SingleOrDefault(list as list, optional condition as function)

- `list`: The list of values to search through.

- `condition`: An optional function that determines which element to return. If no function is provided, the function returns the first element in the list.

How does the List.SingleOrDefault function work?

The List.SingleOrDefault function works by iterating through each element in the list and checking if it matches the specified condition. If there is only one element that matches the condition, that element is returned. If there are no elements that match the condition, the function returns a null value. If there are multiple elements that match the condition, an error is returned.

Let's look at an example to illustrate how the List.SingleOrDefault function works. Suppose we have a list of sales data with the following columns: Product, Salesperson, and SalesAmount. We want to retrieve the sales amount for a specific product and salesperson. We can use the List.SingleOrDefault function to achieve this as follows:

let

Source = #table({“Product”, “Salesperson”, “SalesAmount”}, {

{“Product A”, “John”, 100},

{“Product B”, “John”, 200},

{“Product A”, “Sally”, 150},

{“Product B”, “Sally”, 250}

}),

SalesAmount = List.SingleOrDefault(

Table.SelectRows(Source, each ([Product] = “Product A” and [Salesperson] = “John”))[SalesAmount]

)

in

SalesAmount

In this example, we first create a table called "Source" with the sales data. We then use the Table.SelectRows function to filter the table to only include the rows where the Product column equals "Product A" and the Salesperson column equals "John". We then use the List.SingleOrDefault function to retrieve the SalesAmount column for this filtered table. Since there is only one row that matches the filter criteria, the function returns the SalesAmount value of 100.

The List.SingleOrDefault function is a useful tool in Power Query for retrieving a single element from a list that matches a specific condition. By understanding the M code behind this function, you can use it more effectively in your data transformation 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)