Table.RemoveFirstN

D

Back to top

T

Back to top

#

Back to top

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

One of the most useful functions in Power Query is the Table.RemoveFirstN function, which allows users to remove a specified number of rows from the beginning of a table. This function is particularly useful when working with data that has headers or other metadata that needs to be removed before analysis.

In this article, we will take a closer look at the M code behind the Table.RemoveFirstN function, and explore some of the ways in which it can be used to streamline data analysis workflows.

Understanding the M Code Behind Table.RemoveFirstN

In Power Query, all data transformation tasks are accomplished through the use of M code. M is a functional language that is used to define queries and transformations in Power Query.

The Table.RemoveFirstN function is a built-in M function that allows users to remove a specified number of rows from the beginning of a table. The function takes two arguments: the table to be modified, and the number of rows to remove.

Here is an example of the basic syntax of the Table.RemoveFirstN function:

Table.RemoveFirstN(table as table, count as number) as table

In this example, the “table” argument represents the table that you want to modify, while the “count” argument represents the number of rows that you want to remove.

For example, if you have a table that contains headers in the first two rows, and you want to remove these headers, you could use the following M code:

Table.RemoveFirstN(#"Original Table", 2)

In this code, the “#” before the table name indicates that the table is a reference to a previous step in the query. The “2” indicates that we want to remove the first two rows of the table.

Using Table.RemoveFirstN to Streamline Data Analysis Workflows

The Table.RemoveFirstN function is a powerful tool for streamlining data analysis workflows in Power Query. Here are some examples of how it can be used:

Removing Headers and Footers from Data Tables

When working with data tables, it is common to have headers or footers that need to be removed before analysis. The Table.RemoveFirstN function makes it easy to remove these unwanted rows.

For example, if you have a table that contains headers in the first row and footers in the last two rows, you could use the following M code to remove both the headers and footers:

let

Source = Excel.Workbook(File.Contents("C:MyData.xlsx"), null, true),

Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

#"Removed Top Rows" = Table.RemoveFirstN(Sheet1_Sheet,1),

#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",2)

in

#"Removed Bottom Rows"

In this code, the “Removed Top Rows” step removes the first row of the table (which contains the headers), while the “Removed Bottom Rows” step removes the last two rows of the table (which contain the footers).

Extracting Data from Tables with Irregular Headers

Sometimes, data tables have irregular headers that make it difficult to extract the data that you need. For example, a table might have headers that span multiple columns, or that are merged with other cells.

The Table.RemoveFirstN function can help to simplify this process by removing the irregular headers before analysis.

For example, if you have a table with irregular headers in the first two rows, you could use the following M code to remove these headers:

let

Source = Excel.Workbook(File.Contents("C:MyData.xlsx"), null, true),

Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

#"Removed Top Rows" = Table.RemoveFirstN(Sheet1_Sheet,2),

#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows")

in

#"Promoted Headers"

In this code, the “Removed Top Rows” step removes the first two rows of the table (which contain the irregular headers), while the “Promoted Headers” step promotes the remaining headers to the top of the table for easier analysis.

Filtering Tables by Date

Another common use case for the Table.RemoveFirstN function is when filtering data tables by date. Often, data tables will contain a date column that needs to be filtered before analysis.

The Table.RemoveFirstN function can be used in conjunction with the Table.SelectRows function to filter a table by date.

For example, if you have a table that contains a “Date” column, and you want to filter the table to show only records from the last 30 days, you could use the following M code:

let

Source = Excel.Workbook(File.Contents("C:MyData.xlsx"), null, true),

Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

#"Removed Top Rows" = Table.RemoveFirstN(Sheet1_Sheet,1),

#"Filtered Rows" = Table.SelectRows(#"Removed Top Rows", each [Date] > Date.AddDays(DateTime.LocalNow(),-30))

in

#"Filtered Rows"

In this code, the “Removed Top Rows” step removes the first row of the table (which contains the headers), while the “Filtered Rows” step filters the table to show only records where the “Date” column is greater than 30 days ago.

The Table.RemoveFirstN function is a powerful tool for streamlining data analysis workflows in Power Query. By allowing users to remove a specified number of rows from the beginning of a table, this function can be used to remove headers and footers, extract data from tables with irregular headers, and filter tables by date. By understanding the M code behind this function, users can take full advantage of its capabilities to streamline their data analysis workflows.

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)