Table.RemoveRows

D

Back to top

T

Back to top

#

Back to top

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

What is Power Query?

Power Query is a data transformation and cleaning tool that is part of Microsoft Excel and Microsoft Power BI. It allows users to connect to various data sources, clean and transform data, and load it into Excel or Power BI for analysis. Power Query uses a functional language called M to define data transformations.

Understanding the Table.RemoveRows Function

The Table.RemoveRows function is used to remove rows from a table based on specified criteria. The syntax for this function is as follows:

Table.RemoveRows(table as table, rows as any, optional missingField as nullable number) as table

– table: This is the table that you want to remove rows from.

– rows: This is the criteria that you want to use to remove the rows. It can be a list of values, a function, or a logical expression.

– missingField: This is an optional parameter that specifies how to handle missing values. If set to null, missing values will be ignored.

The Table.RemoveRows function returns a new table with the specified rows removed.

The M Code Behind Table.RemoveRows

The M code for Table.RemoveRows is as follows:

let

RemoveRows = (table as table, rows as any, optional missingField as nullable number) as table =>

let

columnNames = Table.ColumnNames(table),

#"Filtered Rows" = Table.SelectRows(table, each not List.Contains(rows, Record.ToList(_))),

#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows", columnNames)

in

#"Reordered Columns"

in

RemoveRows

The M code above defines a function called RemoveRows that takes in a table, rows, and an optional missingField parameter. The function first extracts the column names from the input table using the Table.ColumnNames function. It then uses the Table.SelectRows function to filter out the rows that match the specified criteria. The criteria are defined by the rows parameter, which can be a list of values, a function, or a logical expression.

The Table.SelectRows function iterates over each row in the input table and checks whether it matches the criteria specified in the rows parameter. If a row matches the criteria, it is excluded from the resulting table. The remaining rows are then reordered to match the original column order using the Table.ReorderColumns function. Finally, the function returns the resulting table.

Customizing the Table.RemoveRows Function

The M code for Table.RemoveRows can be customized to suit specific data cleaning needs. For example, if you want to remove rows that contain missing values in a specific column, you can modify the function as follows:

let

RemoveMissingValues = (table as table, column as text) as table =>

let

columnNames = Table.ColumnNames(table),

#"Filtered Rows" = Table.SelectRows(table, each not Value.Is([column], type null)),

#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows", columnNames)

in

#"Reordered Columns"

in

RemoveMissingValues

The modified function above takes in a table and a column name as parameters. It first extracts the column names from the input table using the Table.ColumnNames function. It then uses the Table.SelectRows function to filter out rows that contain missing values in the specified column. The function checks if the value in the specified column is null using the Value.Is function. If the value is not null, the row is included in the resulting table. The remaining rows are then reordered to match the original column order using the Table.ReorderColumns function. Finally, the function returns the resulting table.

The Table.RemoveRows function plays an essential role in data cleaning and preparation in Power Query. Its M code is customizable and can be modified to suit specific data cleaning needs. Understanding the M code behind this function is crucial for effective data cleaning and preparation in Power Query. With the knowledge gained from this article, users can be confident in their ability to manipulate data in Power Query and produce accurate and meaningful results.

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)