List.Min

D

Back to top

T

Back to top

#

Back to top

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

What is the List.Min Function?

Before we get into the M code behind the List.Min function, let’s first review what this function actually does. The List.Min function is used to find the smallest value in a list of numbers or values. Here’s an example of how the List.Min function can be used in Power Query:

let

myList = {4, 6, 2, 8, 3},

minVal = List.Min(myList)

in

minVal

In this example, we define a list of numbers called "myList" and then use the List.Min function to find the smallest value in that list. The result of this query would be 2, since that is the smallest value in the list.

The M Code Behind List.Min

So how does the List.Min function actually work? Let's take a look at the M code behind this function to find out. Here's the M code for the List.Min function:

(List) => List.Combine(List.Select(List, each _ <> null and not Number.IsNaN(_)), (x, y) => if x < y then x else y)

Let's break this code down piece by piece to see how it works.

The Input Parameter

The List.Min function takes a single input parameter, which is a list of values. In the M code, this parameter is represented by the "List" variable.

Filtering Null and NaN Values

The first step in the List.Min function is to filter out any null or NaN values from the input list. This is done using the List.Select function, which takes two parameters: the input list and a filtering function. Here's the filtering function used in the List.Min function:

each _ <> null and not Number.IsNaN(_)

This function checks each value in the input list and returns true if the value is not null and is not NaN. This ensures that we only work with valid values when finding the minimum value.

Combining the List

Once the null and NaN values have been filtered out, the List.Combine function is used to combine the remaining values into a single list. Here's the code used to combine the list:

List.Combine(List.Select(List, each _ <> null and not Number.IsNaN(_)))

This code uses the List.Select function to filter out null and NaN values, and then passes the resulting list to the List.Combine function. The List.Combine function simply combines all the values in the list into a single list.

Finding the Minimum Value

Finally, the List.Min function uses a lambda function to find the minimum value in the list. Here's the lambda function used in the List.Min function:

(x, y) => if x < y then x else y

This function takes two input parameters (x and y) and compares them to determine which value is smaller. The List.Min function uses this lambda function to compare all the values in the list and find the smallest one.

The List.Min function is a powerful tool for finding the smallest value in a list of numbers or values. While it might seem like a simple function on the surface, the M code behind it is quite complex and involves several steps to filter out null and NaN values and combine the remaining values into a single list. By understanding the M code behind the List.Min function, you'll be better equipped to use this function effectively 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)