Power QueryExercises

Records in depth: fields, defaults and combining

Read, build and combine records, including ones with missing fields.

~9 min read · intermediate · Module 16: M language basics

By Tahir Nas · Examples run in our simulator · Updated

Before this: M basics: let, records and lists

A record is a set of named fields. Table rows are records, JSON objects become records, and option arguments such as [Delimiter = ";"] are records. Knowing how to handle a missing field is the key skill.

Missing fields

Accessing a field that doesn't exist raises an error. [Field]? returns null instead, and Record.FieldOrDefault lets you choose the default:

let
    Settings = [Region = "North", Threshold = 5]
in
    #table({"Expression", "Value"}, {
        {"Settings[Region]", Settings[Region]},
        {"Settings[Currency]?", Settings[Currency]?},
        {"Record.FieldOrDefault(…, ""Currency"", ""EUR"")", Record.FieldOrDefault(Settings, "Currency", "EUR")},
        {"Record.HasFields(…, ""Threshold"")", Record.HasFields(Settings, "Threshold")}
    })

Result

ExpressionValue
Settings[Region]North
Settings[Currency]?null
Record.FieldOrDefault(…, "Currency", "EUR")EUR
Record.HasFields(…, "Threshold")true

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Combining records

The & operator merges two records. Fields from the right-hand record win when both have the same name, which makes it a neat way to apply overrides to defaults:

let
    Defaults = [Delimiter = ",", Encoding = 65001, Currency = "EUR"],
    Overrides = [Delimiter = ";"],
    Options = Defaults & Overrides
in
    Record.ToTable(Options)

Result

NameValue
Delimiter;
Encoding65001
CurrencyEUR

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Records as rows

Inside each, _ is the current row record, so record functions work on rows. Record.FieldNames lists the columns of the row; Record.SelectFields keeps some of them. A list of records becomes a table with Table.FromRecords:

let
    Rows = {[Item = "Kite", Qty = 2], [Item = "Reel", Qty = 6]},
    WithFlag = List.Transform(Rows, each Record.AddField(_, "Bulk", [Qty] >= 5))
in
    Table.FromRecords(WithFlag)

Result

ItemQtyBulk
Kite2false
Reel6true

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Record.TransformFields changes the values of named fields, and Record.RenameFields renames them, which is handy for cleaning an options record or a JSON object before expanding it. Both take the same {field, …} pair lists as their table equivalents, so the patterns you know from columns carry over.

Inside a custom column, _ is the whole row record, so Record.FieldValues(_) gives every value in the row and Record.SelectFields(_, {"Qty", "Price"}) keeps just two. That's how row-wide rules such as 'remove fully blank rows' are written.

Where to find this in Excel

There's no button for this. In Excel you write it in the Power Query editor's Advanced Editor (View > Advanced Editor, also on the Home tab), which shows the whole query as M.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Use Power Query to transform data (the Power Query editor) (Microsoft Learn).

Common mistakes

  • Reading an optional field without ? or a default, so rows without it fail.
  • Expecting the left-hand record to win in &; the right-hand one does.
  • Building rows as records with different field names and getting misaligned columns from Table.FromRecords.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop show a record in the editor as a two-column Name/Value view with an Into Table button (Record.ToTable).
  • Many connector functions take an options record; the & merge pattern is a clean way to share default options between queries.
  • The simulator supports ? field access, & record merging and the Record functions listed on the compatibility page.
Functions in this lesson:Record.FieldOrDefaultRecord.FieldNamesRecord.ToTableRecord.AddField

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the M language basics quiz