Type.ForRecord - Type Function

Type.ForRecord is a Power Query M function that generates a type representing records with specific type constraints on fields. The function returns a record type with the specified field type constraints.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Type.ForRecord(
   fields as record,
   open as logical,
) as type

Argument	Attribute	Description
fields		This is a record where each field represents a column in the custom record type. Each field in this record should be another record, specifying the data type (using the `Type` field) and whether the field is optional (using the `Optional` field).
open		This is a boolean value that determines the nature of the record type. If set to `true`, the record type is open, meaning it can include additional fields not specified in the `fields` record. If set to `false`, the record type is closed, strictly limited to the fields defined in the `fields` record.

Description

The Type.ForRecord function in Power Query creates a custom record type based on specified field definitions and a flexibility attribute.

The output of the Type.ForRecord function is a new, custom record type that conforms to the specifications given in the fields input and adheres to the openness defined by the open input.

Examples

Creating a Basic Record Type

Suppose you want to create a type for a record with two columns. The first column is named Date the second is called Amount. The Type.ForRecord function expects a record as first argument, and the second parameter indicates whether the column is optional or not.

Your record should contain a field name and value for each type you want to specify. The field value is another record with the fields Type and Optional. That looks as follows:

[
  Date   = [ Type = type date,   Optional = false ],
  Amount = [ Type = type number, Optional = false ]
]

You can then combine this with the Type.ForRecord function together with the second argument that indicates whether or not the record is open.

// Output: a closed record type
let
  typeConstraints = 
    [
      Date   = [ Type = type date,   Optional = false ],
      Amount = [ Type = type number, Optional = false ]
    ],
  result = Type.ForRecord( typeConstraints, false)
in
  result

Applying the Record Type to a Table

Once the record type is defined, you can apply it to structure a table. This ensures that the table columns conform to the specified types:

// Output: a closed record type
let
  typeConstraints = [
    Date   = [ Type = type date, Optional = false ], 
    Amount = [ Type = type number, Optional = false ]
  ], 
  closedrecordType = Type.ForRecord ( typeConstraints, false ), 
  Result = 
    Table.FromColumns (
      {{ #date ( 2024, 1, 1 ), #date ( 2024, 1, 2 ) }, { 100, 200 }}, 
      type table closedrecordType
    )
in
  Result

Dynamic Record Type Creation with List.Transform

For more dynamic scenarios, List.Transform can be used in conjunction with Type.ForRecord. This method dynamically generates record types based on column names and types:

let
    columnNames = {"Date", "Amount"},
    columnTypes = {type date, type number},
    rowColumnTypes = List.Transform( columnTypes, ( t ) => [Type = t, Optional = false] ),
    rowType = Type.ForRecord( Record.FromList( rowColumnTypes, columnNames ), false )
in
    #table( type table rowType, {{#date ( 2024, 1, 1 ), 100 }, {#date ( 2024, 1, 2 ), 200}} )

 /* Output: 
#table( 
    type table [Date = date, Amount = number],
    {{ #date ( 2024, 1, 1 ), 100 }, {#date ( 2024, 1, 2 ), 200 }}
 )
 */

Other functions related to Type.ForRecord are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/type-forrecord