Record.ReorderFields

D

Back to top

T

Back to top

#

Back to top

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

Understanding `Record.ReorderFields`

The `Record.ReorderFields` function is used to reorder the fields in a record. In Power Query, a record is a structured data type that consists of one or more named fields. Each field has a name and a value, and the value can be of any data type.

The `Record.ReorderFields` function takes two arguments: a record, and a list of field names in the desired order. It returns a new record with the same fields as the original record, but with the fields reordered according to the specified list.

Here’s an example of how to use `Record.ReorderFields`:

let

originalRecord = [Name=”John”, Age=30, Occupation=”Engineer”],

newRecord = Record.ReorderFields(originalRecord, {“Age”, “Name”, “Occupation”})

in

newRecord

In this example, we have a record called `originalRecord` with three fields: `Name`, `Age`, and `Occupation`. We then use `Record.ReorderFields` to create a new record called `newRecord` with the same fields, but in a different order: `Age`, `Name`, and `Occupation`.

The M Code Behind `Record.ReorderFields`

To understand the M code behind `Record.ReorderFields`, it's helpful to first understand how records are represented in M. In M, a record is represented as a list of key-value pairs, where each key is a field name and each value is the corresponding field value.

For example, the following M expression defines a record with three fields:

[

Name = “John”,

Age = 30,

Occupation = “Engineer”

]

To reorder the fields in a record, we need to manipulate this list of key-value pairs. Here's an example M function that implements `Record.ReorderFields`:

let

reorderFields = (record as record, fieldOrder as list) as record =>

let

fieldList = Record.FieldValues(record),

reorderedList = List.Select(fieldList, each List.Contains(fieldOrder, Record.FieldName(_))),

remainingList = List.Select(fieldList, each not List.Contains(fieldOrder, Record.FieldName(_))),

reorderedRecord = Record.FromList(reorderedList),

remainingRecord = Record.FromList(remainingList)

in

Record.Combine({reorderedRecord, remainingRecord})

in

reorderFields

This function takes two arguments: `record`, which is the original record, and `fieldOrder`, which is the desired field order. It then performs the following steps:

1. Converts the record to a list of key-value pairs using the `Record.FieldValues` function.

2. Reorders the list of key-value pairs based on the desired field order using the `List.Select` function.

3. Separates the reordered key-value pairs from the remaining key-value pairs using `List.Select`.

4. Converts the reordered key-value pairs back into a record using the `Record.FromList` function.

5. Converts the remaining key-value pairs back into a record using the `Record.FromList` function.

6. Combines the two records using the `Record.Combine` function.

The `Record.ReorderFields` function is a powerful tool for reordering fields in a record. By understanding the M code behind this function, you can gain a deeper understanding of how Power Query transforms data.

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)