List.MinN

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:

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

The function will first sort the list in ascending order:

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

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