Record.Field

D

Back to top

T

Back to top

#

Back to top

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

What is the Record.Field function?

The Record.Field function is used to extract a value from a record in Power Query. A record is a data structure that contains one or more fields, where each field has a name and a value. For example, a record might contain fields for the first name, last name, and email address of a person. The Record.Field function takes two arguments: the record and the name of the field to extract. For example, the following code extracts the value of the “first name” field from a record:

let

source = #table(

{“first name”, “last name”, “email”},

{{“John”, “Doe”, “john.doe@example.com”}}),

record = Table.First(source),

first_name = Record.Field(record, “first name”)

in

first_name

In this example, we create a table with three columns: "first name", "last name", and "email". We then create a record from the first row of the table using the Table.First function. Finally, we extract the value of the "first name" field using the Record.Field function and assign it to the variable "first_name".

Understanding the M code behind Record.Field

To understand the M code behind the Record.Field function, we need to understand how records are represented in Power Query. In Power Query, records are represented as lists of field names and corresponding values. For example, the record {first_name = "John", last_name = "Doe", email = "john.doe@example.com"} would be represented as the following list:

{

{“first_name”, “John”},

{“last_name”, “Doe”},

{“email”, “john.doe@example.com”}

}

The Record.Field function works by searching through this list for a field with the specified name, and returning the corresponding value.

The M code for the Record.Field function is as follows:

(record as record, field as text) as nullable any =>

let

result = try record{ field } otherwise null

in

result

This code defines a function that takes two arguments: the record to extract from (as a record data type) and the name of the field to extract (as a text data type). The function returns a nullable value, meaning that it can return null if the field does not exist in the record.

The function works by using the M "try otherwise" construct to attempt to extract the value of the specified field from the record. If the field exists, the value is returned. If the field does not exist, the "otherwise" clause is executed and null is returned.

Using Record.Field in Power Query

The Record.Field function is commonly used in Power Query to manipulate and transform data. Here are some examples of how the function can be used:

1. Renaming fields in a record

The Record.Field function can be used to rename fields in a record. To do this, we extract the value of the old field using Record.Field, and then create a new record with the same values but a different field name. For example, the following code renames the "first name" field to "given name":

let

source = #table(

{“first name”, “last name”, “email”},

{{“John”, “Doe”, “john.doe@example.com”}}),

record = Table.First(source),

given_name = Record.Field(record, “first name”),

new_record = [given name = given_name, last name = record[last name], email = record[email]]

in

new_record

In this example, we extract the value of the "first name" field using Record.Field and assign it to the variable "given_name". We then create a new record with the same values but with a different field name ("given name").

2. Filtering records based on field values

The Record.Field function can also be used to filter records based on the values of specific fields. For example, the following code filters a table of people based on their email domain:

let

source = #table(

{“first name”, “last name”, “email”},

{

{“John”, “Doe”, “john.doe@example.com”},

{“Jane”, “Doe”, “jane.doe@example.com”},

{“Bob”, “Smith”, “bob.smith@acme.com”}

}),

filtered = Table.SelectRows(source, each Text.EndsWith(Record.Field(_, “email”), “@example.com”))

in

filtered

In this example, we use the Table.SelectRows function to filter the "source" table based on a condition that checks whether the email domain ends with "@example.com". We use the Record.Field function to extract the value of the "email" field from each row in the table.

The Record.Field function is a powerful tool for manipulating and transforming data in Power Query. By understanding the M code behind the function, you can use it to perform complex data operations and build more effective queries. Whether you are a beginner or an experienced Power Query user, the Record.Field function is an essential part of your toolkit.

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)