Record.RemoveFields - Record Function

Record.RemoveFields is a Power Query M function that removes specified fields from a record. The function returns a new record without the specified fields, or throws an exception if a field to be removed does not exist.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Record.RemoveFields(
   record as record,
   fields as any,
   optional missingField as nullable number,
) as record

Argument	Attribute	Description
record		The record value to remove fields from.
fields		A list of field names to remove from the record.
missingField	optional	The MissingField.Type determines the function’s reaction to operations on missing columns. When omitted, it uses MissingField.Error and generates an error for missing columns. Alternatives include MissingField.UseNull, substituting null for missing columns, and MissingField.Ignore, which ignores missing columns.

Description

Record.RemoveFields filters a record, returning a new record without the fields listed in fields. This function is useful for cleaning up records by removing unnecessary data points. By default, if a specified field does not exist, the function returns an error. You can modify this behavior by providing an optional MissingField.Type.

Examples

Basic Usage of Record.RemoveFields

The Record.RemoveFields function allows you to exclude specified fields from an existing record. Here’s a basic example:

// Output: [ Citrus = "Orange", Berry = "Strawberry" ]
let
  myRecord = [ Citrus = "Orange", Berry = "Strawberry", StoneFruit = "Peach" ],
  filteredRecord = Record.RemoveFields( myRecord, { "StoneFruit"} )
in
  filteredRecord

In this example:

myRecord is the original record.
Record.RemoveFields excludes the fields specified in the second argument.

Handling Missing Fields

When using Record.RemoveFields, you may run into situations where the fields you specify do not exist in the original record. Power Query allows you to provide a MissingField.Type to handle such scenarios.

Default Behavior: Error on Missing Fields

By default, if you specify a field that does not exist in the original record, Record.RemoveFields will return an error. This is demonstrated in the following example:

// Output: [Expression.Error] The field 'Tropical' of the record wasn't found.
let
  myRecord = [Citrus = "Orange", Berry = "Strawberry", StoneFruit = "Peach"],
  filteredRecord = Record.RemoveFields(myRecord, {"Tropical"})
in
  filteredRecord

Since the function uses MissingField.Error by default, the above code is identical to the one below:

// Output: [Expression.Error] The field 'Tropical' of the record wasn't found.
let
  myRecord = [Citrus = "Orange", Berry = "Strawberry", StoneFruit = "Peach"],
  filteredRecord = Record.RemoveFields(myRecord, {"Tropical"}, MissingField.Error)
in
  filteredRecord

Ignore Missing Fields

If you want to ignore fields that are not present in the original record, you can set the optional third argument to MissingField.Ignore. This allows the function to proceed without errors, only excluding the existing fields:

// Output: [ Citrus = "Orange", Berry = "Strawberry" ]
let
  myRecord = [ Citrus = "Orange", Berry = "Strawberry", StoneFruit = "Peach" ],
  filteredRecord = Record.RemoveFields( myRecord, {"Tropical", "StoneFruit"}, MissingField.Ignore )
in
  filteredRecord

In this example, Tropical is ignored since it does not exist in myRecord. However, the StoneFruit value is removed, resulting in a record with the original Citrus and Berry fields.

Add Missing Fields as Null

Alternatively, you can use MissingField.UseNull to ensure you don’t run into an error:

// Output: [Citrus = "Orange", Berry = "Strawberry", Tropical = null]
let
  myRecord = [Citrus = "Orange", Berry = "Strawberry", StoneFruit = "Peach"],
  filteredRecord = Record.RemoveFields(myRecord, {"StoneFruit"}, MissingField.UseNull)
in
  filteredRecord

Since we are removing fields, (instead of adding them), this enumeration has the same effect as MissingField.Ignore.

Other functions related to Record.RemoveFields are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/record-removefields