Record.AddField Function in Power Query M (5 Easy Examples)

If you want to add a new field to a record in Power Query, with a name and a value you choose, the Record.AddField function is what you reach for. It returns a new record with that field tacked on at the end. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Record.AddField Function

Record.AddField(record as record, fieldName as text, value as any, optional delayed as nullable logical) as record

where

record (required, record). The source record you want to add a field to.
fieldName (required, text). The name of the new field.
value (required, any). The value to store in the new field. It can be any type, including a function.
delayed (optional, nullable logical). Pass true to defer evaluating value until the field is first read. Omit it and the value is evaluated right away.

Returns: a new record with the named field added at the end. The original record is left unchanged.

In plain terms, you hand it a record, a field name, and a value, and it gives you back a copy of the record with that one new field on the end.

Example 1: Add one field to a record

Say you have a product record with a name and a price, and you want to flag whether it is in stock.

let
    Source = Record.AddField([Product="Notebook",Price=4.50],"InStock",true)
in
    Source

Result: [Product = "Notebook", Price = 4.5, InStock = true]

The InStock field is added on the end with the value true, leaving the original two fields as they were.

Example 2: Add a field computed from existing fields

The value does not have to be a fixed literal. You can compute it from fields already in the record.

Here an order has a Quantity and a UnitPrice, and you want a Total field that multiplies them.

let
    Order = [Quantity=12,UnitPrice=4.50],
    Source = Record.AddField(Order,"Total",Order[Quantity]*Order[UnitPrice])
in
    Source

Result: [Quantity = 12, UnitPrice = 4.5, Total = 54]

Order[Quantity]*Order[UnitPrice] works out to 54, and that becomes the value of the new Total field. To read a single field back out of a record like this, use Record.Field.

Example 3: The original record is not changed

Record.AddField does not modify the record you pass in. It returns a new record and leaves the original alone.

Here you add a Region field to Original, then check Original afterward.

let
    Original = [Name="Mara",Team="Sales"],
    Added = Record.AddField(Original,"Region","West"),
    Check = Record.FieldNames(Original)
in
    Check

Result: {"Name", "Team"}

Original still has only Name and Team. The Region field went into Added, which is [Name = "Mara", Team = "Sales", Region = "West"], not into the record you started with.

Example 4: Add a field to every row of a table

Record.AddField works on a single record, so to use it across a table you run it per row with Table.TransformRows, then turn the records back into a table with Table.FromRecords.

Here is the starting data:

City	Miles
Austin	240
Denver	410
Tucson	175

Add a Gallons field to each row, dividing Miles by 25:

let
    Source = Excel.CurrentWorkbook(){[Name="Trips"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"City",type text},{"Miles",type number}}),
    Rows = Table.TransformRows(Typed,each Record.AddField(_,"Gallons",[Miles]/25)),
    Result = Table.FromRecords(Rows)
in
    Result

Each row is a record, so Record.AddField adds the Gallons field to it the same way it did the single records above.

The result has the new column on the end:

City	Miles	Gallons
Austin	240	9.6
Denver	410	16.4
Tucson	175	7

For a whole table like this, Table.AddColumn is usually the cleaner choice. Reach for Record.AddField when you are inside a single record.

Example 5: Adding a field that already exists throws an error

The new field name has to be one the record does not already have. Reuse a name that is already there and you get an error.

Record.AddField([Name="Mara",Team="Sales"],"Team","Marketing")

This raises Expression.Error: The field 'Team' already exists in the record. because Team is already there.

To replace a field’s value instead of adding it, merge records with the & operator, for example [Name="Mara",Team="Sales"] & [Team="Marketing"].

Things to keep in mind with Record.AddField

The new field always lands at the end. The order is the original fields first, then the one you added, no matter where you might want it. Reorder afterward with Record.SelectFields if position matters.
An existing field name throws. Adding a name the record already has raises Expression.Error: The field 'X' already exists in the record. To overwrite instead, use record merge record & [Field=value] or Record.Combine.
value can be any type. A number, text, a list, another record, even a function. Nothing restricts what you store in the new field.
delayed controls when the value is evaluated. Pass true and the value expression is not run until the field is first read, which is handy for an expensive or function-typed value. Leave it out and the value is computed immediately.
For a whole table, use Table.AddColumn. Record.AddField is the per-record primitive. To add a column across every row, Table.AddColumn is the idiomatic tool (Example 4 bridges the two). To pick which columns to keep, Table.SelectColumns is the table-level equivalent of selecting fields.

Common questions about Record.AddField

How do I overwrite a field instead of adding a new one?

Record.AddField only adds, and errors if the field exists. To replace a value, merge records with &, like record & [Field="new value"], which updates the field if present or adds it if not.

What is the difference between Record.AddField and Table.AddColumn?

Record.AddField adds one field to a single record, while Table.AddColumn adds a column across every row of a table. Use the record version inside a single row, the table version for the whole dataset.

List of All Power Query Functions

Related Power Query Functions / Articles: