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
| Expression | Value |
|---|---|
| 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
| Name | Value |
|---|---|
| Delimiter | ; |
| Encoding | 65001 |
| Currency | EUR |
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
| Item | Qty | Bulk |
|---|---|---|
| Kite | 2 | false |
| Reel | 6 | true |
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.
Record.FieldOrDefaultRecord.FieldNamesRecord.ToTableRecord.AddFieldNext step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the M language basics quiz