Record.SelectFields - Record Function

Record.SelectFields is a Power Query M function that filters a record to include only the specified fields. The function returns a new record containing only the fields specified in the input list.

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

Syntax

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

Argument	Attribute	Description
record		The record to select fields from.
fields		A list of field names to select 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.SelectFields filters a record, returning only the fields listed in fields. This function helps in isolating specific data points within a record. In case a specified field does not exist, the function returns an error by default. You can change this behavior by providing an optional MissingField.Type.

Examples

Basic Usage of Record.SelectFields

The Record.SelectFields function allows you to create a new record containing only the specified fields from an existing record. Here’s a basic example:

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

In this example:

myRecord is the original record.
Record.SelectFields creates a new record containing only the fields specified in the second argument.

Handling Missing Fields

When using Record.SelectFields, you may run into situations where the fields you specify do not exist in the original record. In these cases, 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.SelectFields 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"],
  selectedFields = Record.SelectFields(myRecord, {"Citrus", "Tropical"})
in
  selectedFields

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"],
  selectedFields = Record.SelectFields(myRecord, {"Citrus", "Tropical"}, MissingField.Error )
in
  selectedFields

Ignore Missing Fields

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

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

In this example, Tropical is ignored since it does not exist in myRecord, resulting in a record with only the Citrus field.

Add Missing Fields as Null

Alternatively, you can use MissingField.UseNull to add missing fields with a null value:

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

Here, the function adds Tropical as a new field with a null value because it is not found in myRecord.

Other functions related to Record.SelectFields are:

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