Type.ForRecord

D

T

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

Introduction to Type.ForRecord Function

The Type.ForRecord function is used to create a record type value in M. A record is a collection of named values, where each value can be of a different type. For example, a record can contain a name (text), age (number), and address (text) fields. The Type.ForRecord function takes a list of field names and types as arguments and returns a record type value.

The syntax of the Type.ForRecord function is as follows:


Type.ForRecord({

[FieldName1 = FieldType1],

[FieldName2 = FieldType2],

...

})


The field names are optional, and if they are not provided, the fields are automatically named as Field1, Field2, and so on. The field types can be any valid M data types, such as text, number, logical, or record.

Understanding the M Code Behind Type.ForRecord

The M code behind the Type.ForRecord function is relatively simple. It creates a record type value based on the list of field names and types provided as arguments. Let’s take a look at an example:


Type.ForRecord({

[Name = Text.Type],

[Age = Int32.Type],

[Address = Text.Type]

})


This code creates a record type value with three fields: Name (text), Age (number), and Address (text). The Text.Type and Int32.Type functions are used to specify the data types of the fields.

Using Type.ForRecord Function in Power Query

Now that we understand the M code behind the Type.ForRecord function, let’s see how we can use it in Power Query. We can use the Type.ForRecord function to create a record value from a list of fields in a table.

Let’s say we have a table named Sales with two fields: CustomerName (text) and OrderDate (date). We can use the Type.ForRecord function to create a record type value with these two fields as follows:


let

Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],

SalesRecordType = Type.ForRecord({

[CustomerName = Text.Type],

[OrderDate = Date.Type]

}),

SalesTable = Table.TransformColumnTypes(Source, SalesRecordType)

in

SalesTable


In this code, we first get the data from the Sales table using the Excel.CurrentWorkbook() function. We then create a record type value using the Type.ForRecord function, with the CustomerName and OrderDate fields and their respective data types. Finally, we use the Table.TransformColumnTypes function to transform the Sales table to the SalesRecordType record type.

The Type.ForRecord function is a powerful tool in Power Query for creating record type values. With its simple syntax, we can easily create a record type value from a list of fields and their data types. By understanding the M code behind the Type.ForRecord function, we can create custom functions and manipulate data with ease.

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

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)