Search notes:

Power Query M formula language, primitive types: record

A record stores name-value pairs.

Simple example

A record is created with square brackets. Note the name/value pairs and that a record is not an array/list (as in other languages where an array is denoted with square brackets):

[
  num  =  42,
  txt  = "Hello world",
  dat  = #date(2021, 4, 11)
]

Github repository about-Power-Query-Formula-M, path: /language/types/primitive/record/simple.M

When evaluated in Excel, for example with this VBA code, this formula evaluates to:

Note that Excel for a reason chooses to display the date type as number rather than formatted as date.

Automatic resolving of dependencies among record members

It is possible to refer to values of other fields in the same record when assigning a value to a field. Formula M will automatically resolve dependencies. (Compare with automatic resolving of dependencies in let … in expressions).

[
   foo = 2 * baz,
   bar = 7,
   baz = 3 * bar
]

Github repository about-Power-Query-Formula-M, path: /language/types/primitive/record/field-dependency.M

When evaluated, this code produces:

Get value of a given field (field access)

If R is a record, the expression R[fieldName] gets the value of the record's field whose name is fieldName.

The following example evaluates to "Hello world":

let
   R = [
     num =  42,
     txt = "Hello world"
   ]
in
   R[txt]

Github repository about-Power-Query-Formula-M, path: /language/types/primitive/record/get-field.M

The underscore as default record when accessing a field

The expression [FLD] is a shorthand form of _[FLD]. The following code evaluates to "Hello world":

let
   _ = [
         num =  42,
         txt = "Hello world"
       ]
in
       [txt]

Github repository about-Power-Query-Formula-M, path: /language/types/primitive/record/underscore.M

This shorthand notation es especially useful together with each expressions.

Index