Record.ReorderFields - Record Function

Record.ReorderFields is a Power Query M function that reorders the fields in a record based on the specified field order. The function returns a new record with the fields reordered, maintaining their values and leaving unspecified fields in their original positions.

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

Syntax

Record.ReorderFields(
   record as record,
   fieldOrder as list,
   optional missingField as nullable number,
) as record

Argument	Attribute	Description
record		The record to sort fields in.
fieldOrder		A list containing field names as text in the desired order.
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.ReorderFields adjusts the sequence of fields within a record according to a new specified order given in fieldOrder. It rearranges the fields without changing their corresponding values, and fields not mentioned remain in their original sequence. 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.ReorderFields

You can specify the desired order of the fields in the second argument. Here’s a basic example:

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

In this example:

myRecord is the original record.
Record.ReorderFields reorders the fields according to the list provided in the second argument.

When reordering values, be careful to only include fields that exist in your record. If a field does not exist, Power Query throws an error. Here’s an example:

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

Handling Missing Fields

When working with records, you might encounter situations where a field you want to manipulate does not exist. To handle these scenarios, the M language offers the MissingField.Type enumeration. This allows you to specify different behaviors for missing fields.

Default Behavior: MissingField.Error

The MissingField.Error enumeration returns an error if a field is not found. This is the default behavior used by functions like Record.ReorderFields, even when you do not explicitly mention a MissingField.Type in the arguments.

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

Ignore Missing Fields: MissingField.Ignore

If you prefer the function to ignore missing fields and proceed with reordering the existing ones, you can use MissingField.Ignore. This option skips the operation for fields that do not exist.

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

In this example, the function skips the attempt to reorder the nonexistent field Tropical and leaves the original record unchanged.

Add Missing Fields as Null: MissingField.UseNull

Another option is MissingField.UseNull. When this setting is used, the function will add a new field with a null value if the specified field to reorder does not exist.

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

In this scenario, a new field named Tropical is added to the record with a value of null because the original field Tropical was not found.

Other functions related to Record.ReorderFields are:

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