Record.TransformFields - Record Function

Record.TransformFields is a Power Query M function that applies specified transformations to a record’s fields. The function returns a modified record after applying the transformations.

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

Syntax

Record.TransformFields(
   record as record,
   transformOperations as list,
   optional missingField as nullable number,
) as record

Argument	Attribute	Description
record		The record value to transform values in.
transformOperations		A list of operations to perform on your values. Each operations is a pair of field names and a function to transform the value.
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.TransformFields updates a record by applying a series of transformations specified in transformOperations. Each operation pairs a field name with a function that modifies the field’s value. This can be done for one or many fields simultaneously. 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

The Record.TransformFields function in Power Query M allows you to specify various operations to perform on a record. In a way it is the record equivalent to the Table.TransformColumns function. Let’s explore how this function works with some examples.

Basic Example: Counting Items in a List

Consider a simple record with a single field named Letters, which contains a list of letters:

[ Letters = { "a", "b", "c", "d" } ]

Suppose you want to replace this list with the count of items it contains. You can achieve this using the Record.TransformFields function:

// Output: [ Letters = 4 ]
let
  myRecord = [ Letters = { "a", "b", "c", "d" } ],
  transform = Record.TransformFields( myRecord, { "Letters", List.Count } )
in
  transform

In this operation, the List.Count function counts the number of items in the Letters list and transforms the field to this count, resulting in [ Letters = 4 ].

Multiple Transformations on a Record

Now, let’s look at a more complex example where we need to perform multiple transformations on different fields of a record:

[ Name = "RICK", Age = 372, FullName = {"Rick", "de", "Groot"} ]

Unfortunately, the values in this record are not as desired. We need to clean and transform the values in this record as follows:

Name: Capitalize only the first letter.
Age: Convert the age from months to years by dividing by 12.
FullName: Combine the list of name parts into a single name.

Here’s how you can specify multiple operations to the Record.TransformFields function:

// Output: [ Letters = 4 ]
let
  myRecord  = [ Name = "RICK", Age = 372, FullName = { "Rick", "de", "Groot" } ], 
  transform = 
    Record.TransformFields (
      myRecord, 
      {
        { "Name", Text.Proper }, 
        { "Age", each _ / 12 }, 
        { "FullName", each Text.Combine ( _, " " ) }
      }  
    )
in
  transform

In this example, the second argument of Record.TransformFields specifies three operations:

Name: Text.Proper function capitalizes the first letter of the Name value.
Age: The function each _ / 12 converts the age from months to years.
FullName: The Text.Combine function merges the list of name parts into a single string.

Handling Missing Fields with Record.TransformFields

In some scenarios, you might not know what values to expect in a record. By default, the Record.TransformFields function will return an error if it encounters a missing field. Here’s an example demonstrating this situation:

// Output: [Expression.Error] The field 'Numbers' of the record wasn't found.
let
  myRecord = [ Letters = { "a", "b", "c", "d" } ],
  transform = Record.TransformFields( myRecord, { "Numbers", List.Sum } )
in
  transform

To handle these scenarios, the Record.TransformFields function supports a MissingField.Type enumeration. Let’s see how you can use these enumerations.

MissingField.Error (Default)

By default, MissingField.Error is used, which results in an error if the field is missing. The following example shows this:

// Output: [Expression.Error] The field 'Numbers' of the record wasn't found.
let
  myRecord = [ Letters = { "a", "b", "c", "d" } ],
  transform = Record.TransformFields( myRecord, { "Numbers", Number.Abs }, MissingField.Error )
in
  transform

MissingField.Ignore

You can choose to ignore any operations that specify fields that don’t exist in the record by using the MissingField.Ignore enumeration. In this case, the function skips the transformation and returns the original record:

// Output: [ Letters = { "a", "b", "c", "d" } ]
let
  myRecord = [ Letters = { "a", "b", "c", "d" } ],
  transform = Record.TransformFields( myRecord, { "Numbers", Number.Abs }, MissingField.Ignore )
in
  transform

Here, since the Numbers field doesn’t exist, the function ignores the operation and returns the original record unchanged.

MissingField.UseNull

Another option is to use MissingField.UseNull. When the function encounters a missing field, it adds a new field to the record, populates it with a null value, and then applies the specified transformation logic:

// Output: [ Letters = { "a", "b", "c", "d" }, Numbers = null ]
let
  myRecord = [ Letters = { "a", "b", "c", "d" } ],
  transform = Record.TransformFields( myRecord, { "Numbers", Number.Abs }, MissingField.UseNull )
in
  transform

In this example, the MissingField.UseNull enumeration creates the Numbers field, adds a null value and applies the Number.Abs function. Since the function receives a null value, it returns null as a result.

Other functions related to Record.TransformFields are:

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