The M language has so-called structured values, to which lists, records and tables belong. Each value type serves specific purposes and this post is intended to give an introduction to records. This post is part of a series about lists, records and tables in M. I have included some screenshots with M code in this post. You can download the code from the screenshots as a txt file here. The download does not require a password. If you are still asked for your OneDrive password, change your browser to Edge.

What is a record in M?

In M there are two kinds of values: primitive and structured values. Examples for primitive values are:

• „a“,
• 1,
• true.

They are primitive in that way, that they are not contructed out of other values. In contrast to primitive values, we have so called structured values in M, which are composed of other values, primitive and structured ones. A record is one of those structured values (the others are lists and tables) and it is described as „a set of fields. A field is a name/value pair where the name is a text value that is unique within the field’s record.“

A defined record is introduced with an opening squared bracket „[“ and ended with a closing squared bracket „]“. Even if records usually contain fields, they can be empty, which looks like this: [].

An example of a very simple representative of a record is:

[A=1, B=2]

Before taking a more detailed look at records of what they are and what they are for, let’s discuss why to use records at all.

Why use records at all?

In the vast majority of cases, a record will be used as an intermediate product for other queries. In any case, it is extremely rare for a record to be loaded into the data model. If a record in Power BI is nevertheless loaded into the data model, it behaves like a table. Field names become column captions and field values become values of the table.

Reasons for using records in Power Query can include the following: You…

• … want to create a record to pass as a parameter to a native M function,
• … are using a function that creates a record as a return value and want to process it further,
• … want to access/ work with rows in tables,
• … would like to create documentation for custom functions, using the meta data record in M,
• … want to implement proper error handling within your m scripts,
• … and many more

Let’s take a look at how to create records.

How to create records in M

In my experience there are 3 ways in M to create a record:

1. Using the record initialization syntax with squared brackets

You can define a record by using an opening squared bracket „[„, and optional field-list and end it with a closing squared bracket „]“. The simplest record is an empty record.

Empty record

The empty record is the simplest of all records, in which the field-list is empty:

[] → Record.FieldCount([]) = 0

An empty record is of course more of a special case, because records normally contain fields with values.

Non-empty records

To create a non-empty record, I can add comma seperated set of fields. Fields are pairs of names of type text and values of any possible type:

In the screenshot above you can see two things:

• The field names can be written without quotation marks, even if they contain spaces.
• The values of records (as well as lists) can contain values of any type.

For field names it is important that no field name occurs more than once within the same record. These are used as unique identifiers. If this rule is violated, the following error message is displayed:

Defining records manually is one way. Let’s look at how to create records with corresponding functions.

2. Using native M functions, that create records

At the time of writing, we have 26 functions in M that return a record. Popular examples are:

• Record.FromList() 
• Record.FromTable()
• Date.ToRecord()
• DateTime.ToRecord()

Calling the intrinsic variable #shared returns a record, containing all names and values of all the queries (tables, constants, records, lists, native M functions, custom functions, etc.) in the current Excel/ Power BI Desktop file:

All those functions create records based on the input parameters. But there is another way to create a record.

3. Referring to a row in a table

The third way to get a record is to reference a row of a table using the following syntax: Table{row number}:

Note that the row reference in a table, like list-items, is based on a zero-based index. So the value 1 must always be subtracted from the desired row number to reference the desired row.

If you create a calculated column within a table, you can use the underscore „_“ to reference the current row of the table, which means the current record. This makes the following formulas possible:

The above formula works as follows:

1. The underscore „_“ represents the current row/ record of the table
2. Record.ToList(_) converts this record to a list
3. List.Sum() calculates the sum of all the fields of the given record

Now that we know what records are and how they are created, we are going to focus on dealing with records.

Operators for records+ equivalent functions

There are 3 operators that can be used in conjunction with records: „=“ and „<>“ make it possible to compare records, while „&“ combines/ merges records. Here are some examples of how to use that:

Record comparisons

The following examples show that the order of the record fields is irrelevant for a comparison. As long as the records to be compared have the same fields, i.e. the same field names and values, the records are identical:

[ a = 1, b = 2 ] = [ b = 2, a = 1 ] → true
[ a = 1, b = 2, c = 3 ] <> [ a = 1, b = 2 ] → true

Record merge

Sometimes you don’t want to compare different records with each other, but combine them. This succeeds as follows:

[ a = 1, b = 2 ] & [ c = 3 ] → [ a = 1, b = 2, c = 3 ]
[ a = 1, b = 2 ] & [ a = 3 ] → [ a = 3, b = 2 ]. This can also be achieved by using the function Record.Combine({ [ a = 1, b = 2 ],[ a = 3 ]})

ATTENTION: The second example shows, that when combining/ merging records the fields from the most right record will override fields from the left record(s), should there be an overlap in field names.

Another aspect that is interesting with regard to records is how to access the fields in a record.

Accessing record fields

Once you have a record, it is sometimes necessary to access special fields within the record directly.

Using the lookup operator []

To access an field in a record, you can use the – so called – lookup operator „[]“ to search for record fields by name. The following examples explain this in detail:

Take the following record: MyRecord = [A=1, B=2].  Here are some examples of how to access the record fields of this record:

MyRecord[A] = 1,

MyRecord[B] = 2,

MyRecord[C] leads to the following error message, saying that an attempt is made to access an field of the record that does not exist in the record:

To overcome this last error message, you can use the so called optional-field-selection as follows, which returns null, if the selected field doesn’t exist:

MyRecord[C]? → null

Reference to record fields based on their position in the record

In lists, the corresponding item is referenced by its position within the list. Unfortunately, this is not so easy in records. In records the reference is (normally) based on the field name, but not on the position of the field within the record. If this is exactly what I need, I have to go a few detours.

The function Record.SelectFields() includes a record (MyRecord) in the first parameter from which certain fields are to be selected. The fields to be selected can be defined in the second parameter. The second parameter can be of any type. Therefore I can refer here to the function Record.FieldNames(), which returns all field names of the records as a list, and then use the positional index operator {} to select the item in the list (ATTENTION: zero-based index), which I want to have.

Using native M functions

Due to the fact that fields in records are referenced on the basis of their field names and not on the basis of their position as in lists, there are hardly any functions for records that would be equivalent to List.First(), ListLastN(), etc.. The only example I can think of on this topic is a modification of the above example to combine Record.SelectFields() with any second parameter.

Functions that use records as input

If I look at the functions that include a record as a function parameter at the moment, I see only 30 functions in M. If I add those functions where the record can also be „nullable“ (which means the parameter is optional), I get 87 functions.

An interesting representative of functions that record records as parameters is the function Web.Contents(). The function has the following syntax:

Web.Contents(url as text, optional options as nullable record) as binary 

While the first parameter only takes up the url as text, the optional parameter „options“ hides a record with a large variety of settings. Take a look at the following screenshot:

At this point I will not go into the individual fields of the record, because this could fill its own post. However, it is good to see that this record contains a large number of fields of different types and thus influences the behavior of the Web.Contents() function.

Functions that create records as output

The function DateTime.ToRecord() converts a datetime value into a record:

When I use this function in a calculated column within a table, it looks like this:

In step 1 I refer to the value in column „Now“ for the calculation of the new column. Then I expand the created record in step 2 and create the 6 new columns Year, Month, Day, Hour, Minute and Second.

Finally, let’s have a look at some very special forms of records.

Special records

M has special forms of records, which are needed for certain purposes.

Metadata record

Each value in M has a so-called metadata record, which is empty by default. You can add content to this record by using the keyword meta after an expression (in my example the expression 1). To retrieve the saved metadata information, you can use the function Value.Metadata(), with a reference to the value (Source), in which the expression was saved.

Using the metadata record to document custom functions

The metadata record can be used to document custom functions within the function and to provide help to users of the function. I have written a three-part article about this in the past that explains in great detail how this works.

The error record

M has an arsenal of error messages in place if it was not possible to get a value from an expression. For example, try to create a record with two identical record field names and you get the following error message:

The record couldn’t be evaluated, due to identical field names. But there are situations in M where the language itself cannot identify an error, but I want to raise an error in the logic of my code:

Imagine I want to write a custom function that does nothing more than return the given value. This is certainly not meaningful, but an easy to understand example: (x) => x.
If I now want this function to return all values, unless it is the value 0 (zero!), then I have to generate the error myself, if for M the inclusion of the value 0 as parameter is no error at all. Take a look at the follwing M code:

Within the function Error.Record() I define a record, which contains the 3 fields:

1. (error) reason,
2. (error) message and
3. (error) detail. 

I am then able to call the record created by this function  elsewhere with – in my example – „error ErrIfZero“ if it is necessary.

This was certainly not an exhaustive enumeration of the characteristics of records, but I hope they have brought you a little bit closer to this topic and also explained why you have to deal with records if you want to master the M language.

Greetings from Germany,

Lars