Table.FirstN

D

Back to top

T

Back to top

#

Back to top

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

What Is Power Query M?

Power Query M is the powerful functional programming language used in Power Query. It provides a way to create custom functions, transforms data, and perform calculations. Power Query M is based on the F# programming language, and it is used to create formulas that are used to transform data in Power Query. The M code is written in a text editor and executed in the Power Query Editor.

What Is the Table.FirstN Function?

The Table.FirstN function is a Power Query M function that returns the first n rows of a table. It takes two arguments, the table to be filtered, and the number of rows to return. The syntax of the function is as follows:

Table.FirstN(table as table, countOrCondition as any) as table

The first argument, `table as table`, specifies the table to be filtered. The second argument, `countOrCondition as any`, specifies the number of rows to return or a Boolean expression that evaluates to true or false.

How to Use the Table.FirstN Function

To use the Table.FirstN function, follow these steps:

1. Open the Power Query Editor by selecting “Edit Queries” from the “Data” tab in Excel or Power BI.

2. Import the data you want to filter into Power Query.

3. Select the table you want to filter in the “Queries” pane.

4. Click the “Add Column” tab in the “Ribbon” and select “Custom Column”.

5. In the “Custom Column” dialog box, enter the following formula:

Table.FirstN([Table], 10)

This formula will return the first 10 rows of the table.

6. Click “OK” to close the dialog box.

The resulting table will contain the first 10 rows of the original table. You can change the number of rows returned by modifying the second argument of the Table.FirstN function.

Breaking Down the M Code

The M code behind the Table.FirstN function is relatively simple. It is essentially a filter function that returns the first n rows of a table. Here is the M code for the Table.FirstN function:

let

Table.FirstN = (table as table, countOrCondition as any) as table =>

let

count =

if Value.Is(countOrCondition, type number) then countOrCondition

else Table.RowCount(Table.SelectRows(table, countOrCondition)),

result = Table.FirstN(table, count)

in

result

in

Table.FirstN

The M code is written in a functional programming style and consists of a single function, Table.FirstN. The function takes two arguments, the table to be filtered, and the number of rows to return.

The function first evaluates the second argument to determine whether it is a number or a Boolean expression. If it is a number, then the function returns the first n rows of the table. If it is a Boolean expression, then the function uses the Table.SelectRows function to filter the table and return the first n rows that match the condition.

The Table.FirstN function is a powerful tool for filtering tables in Power Query. It allows you to extract a specified number of rows from a table and perform further transformations on the resulting data. The M code behind the function is relatively simple and can be modified to suit your specific needs. By mastering the Table.FirstN function, you can become a more proficient user of Power Query and unlock its full potential.

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)