Record.RemoveFields

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Record.RemoveFields

Understanding the Record.RemoveFields Function

The Record.RemoveFields function is a powerful tool within Power Query that allows you to selectively remove fields from a table. This function can be especially useful when working with large datasets that contain many unnecessary or irrelevant fields.

To use the Record.RemoveFields function, simply select the table that you want to remove fields from, and then specify the specific fields that you want to remove. The function will then automatically remove those fields from the table, leaving you with a cleaner and more streamlined dataset.

The M Code Behind Record.RemoveFields

To better understand how the Record.RemoveFields function works, let’s take a closer look at the M code behind this powerful function. The M code for Record.RemoveFields is as follows:

(Table as table, Optional fields as any) as table =>

let

fieldsToRemove = if fields = null then {} else List.Buffer(fields),

keepColumns = List.RemoveMatchingItems(Table.ColumnNames(Table), fieldsToRemove),

result = Table.SelectColumns(Table, keepColumns)

in

result

Let's break down each section of this code to better understand how it works.

The Input Parameters

The Record.RemoveFields function takes two input parameters: the table that you want to remove fields from, and the specific fields that you want to remove (which are optional).

(Table as table, Optional fields as any) as table =>

Defining the Fields to Remove

The first step in the M code for Record.RemoveFields is to define the fields that you want to remove from the table. This is done using the following code:

fieldsToRemove = if fields = null then {} else List.Buffer(fields),

This code first checks to see if any fields have been specified for removal. If no fields have been specified, it creates an empty list. If fields have been specified, they are added to a new list called "fieldsToRemove".

Determining the Columns to Keep

Next, the M code determines which columns to keep in the table. This is done using the following code:

keepColumns = List.RemoveMatchingItems(Table.ColumnNames(Table), fieldsToRemove),

This code first retrieves the column names for the table using Table.ColumnNames(Table). It then removes any columns that match the fields to be removed using the List.RemoveMatchingItems function. The resulting columns are then stored in a new list called "keepColumns".

Selecting the Remaining Columns

Finally, the M code selects the remaining columns from the table using the following code:

result = Table.SelectColumns(Table, keepColumns)

This code uses the Table.SelectColumns function to select the columns specified in the "keepColumns" list. The resulting table is then stored in a variable called "result".

The Record.RemoveFields function is a powerful tool within Power Query that can save you time and hassle when working with large datasets. By understanding the M code behind this function, you can gain a deeper understanding of how it works and how to use it effectively. So give it a try and see how it can improve your workflow today!

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)