List.FirstN

D

Back to top

T

Back to top

#

Back to top

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

But how exactly does List.FirstN work? In this article, we’ll explore the M code behind this powerful function and provide some examples of how it can be used.

Understanding List.FirstN

Before we dive into the M code behind List.FirstN, let’s first take a closer look at the function itself. List.FirstN takes two inputs: the list you want to extract items from, and the number of items you want to extract. Here’s an example of how it’s used:

let

myList = {1, 2, 3, 4, 5},

firstThree = List.FirstN(myList, 3)

in

firstThree

In this example, we're creating a list called "myList" that contains the numbers 1 through 5. We then use List.FirstN to extract the first three items from this list. The result is a new list containing only the numbers 1, 2, and 3.

The M Code Behind List.FirstN

Now that we understand how List.FirstN works, let's take a look at the M code behind it. Here's the basic structure of the function:

List.FirstN(list as list, count as number) as list =>

let

result = List.FirstN(list, count)

in

result

As you can see, List.FirstN is a fairly simple function. It takes two inputs: "list," which is the list you want to extract items from, and "count," which is the number of items you want to extract. The function then uses the "let" statement to define a new variable called "result." This variable is defined as the output of the List.FirstN function, which takes "list" and "count" as its inputs.

Finally, the function returns the "result" variable. This essentially means that List.FirstN is just a wrapper function that passes its inputs to the built-in List.FirstN function.

Examples of Using List.FirstN

Now that we understand the M code behind List.FirstN, let's take a look at some examples of how it can be used. Here are a few scenarios where List.FirstN might come in handy:

Example 1: Extracting the Most Recent Sales Data

Suppose you have a large dataset containing sales data for the past year. You only need to analyze the most recent quarter of data, so you want to extract the first 90 days' worth of data from the dataset.

Here's how you could do that using List.FirstN:

let

salesData = // your sales data here,

recentSales = List.FirstN(salesData, 90)

in

recentSales

In this example, we're using List.FirstN to extract the first 90 items from the "salesData" list. This gives us a new list containing only the most recent sales data.

Example 2: Filtering Out Low-Value Orders

Suppose you have a dataset containing order information for a large number of customers. You want to identify customers who have placed at least one order with a value greater than $100. To do this, you could use List.FirstN to extract the first high-value order for each customer:

let

orderData = // your order data here,

highValueOrders = List.Transform(List.Group(orderData, “CustomerID”), each List.FirstN(List.Sort(_, {“OrderValue”}), 1)),

highValueCustomers = List.Transform(highValueOrders, each _{0})

in

highValueCustomers

In this example, we're using List.Group to group the order data by customer ID. We then use List.Transform to apply a function to each group, which extracts the first high-value order for that customer. Finally, we use List.Transform again to extract the customer ID from each high-value order.

Example 3: Extracting the Top N Products by Sales

Suppose you have a dataset containing sales data for a large number of products. You want to identify the top 10 best-selling products. To do this, you could use List.FirstN to extract the top 10 products by sales:

let

productSalesData = // your product sales data here,

topProducts = List.FirstN(List.Sort(productSalesData, {“TotalSales”}, Order.Descending), 10),

in

topProducts

In this example, we're using List.Sort to sort the product sales data by total sales, in descending order. We then use List.FirstN to extract the first 10 items from the sorted list, which gives us the top 10 best-selling products.

List.FirstN is a simple yet powerful function in the M language. By understanding the M code behind this function, you can use it to extract only the data you need from large datasets. Hopefully, the examples provided in this article have given you some ideas of how List.FirstN can be used in your own 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)