List.MinN

D

T

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

What is List.MinN Function?

List.MinN is a function in Power Query M language that is used to find the smallest n number of items in a list. The function takes two arguments: the list to be processed and the number of smallest items to be returned.

The syntax of the function is as follows:


List.MinN(list as list, optional count as number) as list


Here, the list argument is the list to be processed, and the count argument is the number of smallest items to be returned. If the count argument is not provided, the function returns the smallest item in the list.

How List.MinN Function Works?

The List.MinN function works by first sorting the list in ascending order and then selecting the smallest n items from the list. If the count argument is not provided, the function returns only the smallest item in the list.

Let's take an example to understand how List.MinN function works. Consider the following list:


{5, 2, 6, 3, 1, 8, 9}


If we want to find the smallest three items from this list, we can use the List.MinN function as follows:


List.MinN({5, 2, 6, 3, 1, 8, 9}, 3)


The function will first sort the list in ascending order:


{1, 2, 3, 5, 6, 8, 9}


And then it will select the first three items from the sorted list:


{1, 2, 3}


Using List.MinN Function in Power Query

Now that we know how the List.MinN function works, let's see how it can be used in Power Query to manipulate data.

Suppose we have a table of sales data that contains the sales amount for each product in different regions. We want to find the lowest three sales amounts for each product. We can use the List.MinN function to achieve this.

First, we need to transform the data so that it is in the required format. We can use the Unpivot Columns feature in Power Query to transform the table into a list of sales amounts for each product and region combination. The resulting table will look like this:

| Product | Region | Sales Amount |

|---------|--------|--------------|

| A | North | 100 |

| A | South | 200 |

| A | East | 150 |

| A | West | 120 |

| B | North | 80 |

| B | South | 70 |

| B | East | 90 |

| B | West | 60 |

| C | North | 50 |

| C | South | 60 |

| C | East | 40 |

| C | West | 30 |

Next, we can group the data by Product and apply the List.MinN function to the Sales Amount column to find the lowest three sales amounts for each product. The resulting table will look like this:

| Product | Lowest 1 | Lowest 2 | Lowest 3 |

|---------|----------|----------|----------|

| A | 100 | 120 | 150 |

| B | 60 | 70 | 80 |

| C | 30 | 40 | 50 |

List.MinN is a powerful function in Power Query M language that can be used to find the smallest n number of items in a list. The function works by sorting the list in ascending order and selecting the smallest n items from the list. It can be used in Power Query to manipulate data efficiently.

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)