Type.ClosedRecord - Type Function

Type.ClosedRecord is a Power Query M function that returns a closed version of a given record type, or the same type if it is already closed. The function returns the closed version of the input record type.

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

Syntax

Type.ClosedRecord( type as type ) as type

Description

The Type.ClosedRecord function takes a record type as input and returns a closed version of the record type. In case the record was already closed, it returns the same type.

Examples

Let’s look at an example where this is useful.

Conversion from Open to Closed Record Type

Suppose you have an open record type in the shape of:

type [ Date = date, Amount = number, ... ].

You can transform this open record into a closed record by writing:

// Output: type [ Date = date, Amount = number ]
Type.ClosedRecord( type [ Date = date, Amount = number, ... ] )

Now you may wonder when this is useful. For this example, you need to understand some about open record types in Power Query.

The Need for Closed Record Types in Tables

In Power Query, an open record type is a flexible data structure, typically defined in the form of type [ Date = date, Amount = number, ... ]. The format allows additional fields to be incorporated in the future without the need to alter the type structure.

However, there are scenarios where the dynamic nature of an open record type is less suitable, for example when defining the structure of tables. Tables in Power Query require the use of a closed record type, a more fixed structure compared to its open counterpart. Using an open record type directly for table definitions leads to errors, as tables necessitate a definite structure without optional fields.

Practical Example

Consider a scenario where you wish to define the type of a table using the structure from an open record type. Directly using an open record type results in an error, as Power Query requires a closed record type for table structures.

The following example illustrates how to effectively use Type.ClosedRecord for this purpose:

let
  myTable = Table.FromColumns( { {#date( 2024, 1, 1) }, { 4433} }, { "Date", "Amount" } ),
  myType = Type.ClosedRecord( type [ Date = date, Amount = number, ... ] ),
  setTypes = Value.ReplaceType( myTable, type table myType )
in
  setTypes

This script returns a table with the desired types set correctly. Attempting the same with an open record type would result in the error: “Expression.Error: The row type of a table type must be a closed record type with no optional fields.”

The ability to transform an open record type into a closed record type is a niche functionality that ensures data type compatibility, particularly for table structures. You may not need it often, but it comes in handy when you do.

Other functions related to Type.ClosedRecord are:

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