The M Code Behind the Power Query M function Type.OpenRecord

Introduction to Record Types

Before we dive into the M code behind the Type.OpenRecord function, let’s first understand what a record type is. A record is a data type that consists of a collection of named fields, where each field has a specified data type. For example, a record type for a person might include fields such as “Name”, “Age”, and “Address”. Each field in the record has a specific data type, such as “text”, “number”, or “date”.

Record types are useful for organizing and structuring data in a consistent and meaningful way. They allow you to group related fields together and enforce a consistent structure for your data. In Power Query, record types are often used to represent rows of data from a table or query.

The Type.OpenRecord Function

The Type.OpenRecord function is used to create a record type from a list of field names and types. The syntax for the function is as follows:

Type.OpenRecord([Field1 = Type1, Field2 = Type2, ...])

In this syntax, `Field1`, `Field2`, and so on represent the names of the fields in the record, and `Type1`, `Type2`, and so on represent the data types of the fields.

Here’s an example of how to use the Type.OpenRecord function to create a record type for a person:


Name = Text.Type,

Age = Int32.Type,

Address = Text.Type


In this example, the record type includes three fields: “Name”, “Age”, and “Address”. The data types of these fields are “text”, “number”, and “text”, respectively.

Understanding the M Code Behind Type.OpenRecord

Now that we know how to use the Type.OpenRecord function, let’s take a closer look at the M code behind it. The M code for this function is relatively simple and consists of just a few lines.

The first line of the code defines the function and specifies the input parameters:

Type.OpenRecord = (fields) =>

In this line, `fields` represents the list of field names and types that are passed as input to the function.

The next line of the code defines the record type using the `Record.AddField` function:

Record.AddField(null, fields)

In this line, `null` represents the initial record value, and `fields` represents the list of field names and types. The `Record.AddField` function is used to add each field to the record.

Finally, the code returns the record type:

[Type = #table({"Name", "Type"}, {{ "Record", fields}})]

In this line, `#table` creates a table with two columns: “Name” and “Type”. The name of the record type is “Record”, and the fields are specified in the `fields` input parameter.

The Type.OpenRecord function is a powerful tool for creating record types in Power Query. It allows you to easily define the structure of your data and enforce consistency across your queries. By understanding the M code behind this function, you can gain a deeper understanding of how Power Query works and how to use it more effectively in your data transformation projects.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

10-12 Jul 23 (London or Online)
25-27 Sep 23 (London or Online)
20-22 Nov 23 (London or Online)

Contact Us


    Your Name (required)


    Email (required)


    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)