Record.AddField

D

Back to top

T

Back to top

#

Back to top

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

What is Record.AddField?

Record.AddField is a Power Query M function that allows you to add a new field to a record. A record is a collection of named values, similar to a row in a table. The new field can be based on an existing field or calculated using a formula. The syntax for the Record.AddField function is as follows:

Record.AddField(record as record, field as text, value as any) as record

Where:

- record: The record to which the new field is added.

- field: The name of the new field.

- value: The value of the new field.

Creating a Simple Record

Before we dive into the details of the Record.AddField function, let's create a simple record to work with. In Power Query, a record is enclosed in curly braces {} and consists of a list of key-value pairs, where the key is a field name and the value is the field value. For example, the following code creates a record with three fields:

let

myRecord = [

FirstName = “John”,

LastName = “Smith”,

Age = 30

]

in

myRecord

This code defines a new variable called myRecord and assigns it a record with three fields: FirstName, LastName, and Age. The field values are "John", "Smith", and 30, respectively.

Adding a New Field to a Record

Now that we have a basic understanding of records in Power Query, let's see how we can add a new field to a record using the Record.AddField function. In the following example, we add a new field called "FullName" to the myRecord variable, which concatenates the FirstName and LastName fields:

let

myRecord = [

FirstName = “John”,

LastName = “Smith”,

Age = 30

],

newRecord = Record.AddField(myRecord, “FullName”, myRecord[FirstName] & ” ” & myRecord[LastName])

in

newRecord

This code defines a new variable called newRecord and assigns it the result of calling the Record.AddField function on the myRecord variable. The first argument is the record to which the new field is added, which is myRecord in this case. The second argument is the name of the new field, which is "FullName". The third argument is the value of the new field, which is a concatenation of the FirstName and LastName fields using the & operator.

Calculating the New Field Value

In the previous example, we added a new field to the record based on existing fields. However, we can also calculate the new field value using a formula. In the following example, we add a new field to the myRecord variable called "AgeInDays", which calculates the age of the person in days:

let

myRecord = [

FirstName = “John”,

LastName = “Smith”,

Age = 30

],

newRecord = Record.AddField(myRecord, “AgeInDays”, myRecord[Age] 365)

in

newRecord

This code defines a new variable called newRecord and assigns it the result of calling the Record.AddField function on the myRecord variable. The first argument is the record to which the new field is added, which is myRecord in this case. The second argument is the name of the new field, which is "AgeInDays". The third argument is the value of the new field, which is calculated by multiplying the Age field by 365.

In this article, we explored the M code behind the Power Query M function Record.AddField. We learned that Record.AddField is used to add a new field to a record and that the new field can be based on existing fields or calculated using a formula. We also learned that records are a collection of named values and that they can be created using curly braces {} and key-value pairs. By understanding the M code behind Record.AddField, you can take advantage of the full power of Power Query to transform and analyze your 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)