Type.IsOpenRecord

A

B

C

D

E

F

G

H

I

J

L

M

N

O

P

R

S

T

U

V

W

X

#

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

In this article, we will explore the M code behind the Type.IsOpenRecord function in Power Query and how it can be used to simplify data transformations.

Understanding the Type.IsOpenRecord Function

The Type.IsOpenRecord function is one of the many M functions available in Power Query. It is used to check if a given record is an open record. An open record is a record that can contain additional fields beyond those explicitly defined in its type definition.

The syntax for the Type.IsOpenRecord function is as follows:


Type.IsOpenRecord(record as record) as logical

The function takes a record as its input and returns a logical value that indicates whether the record is open or not. If the record is an open record, the function will return true. Otherwise, it will return false.

The M Code Behind the Type.IsOpenRecord Function

To understand the M code behind the Type.IsOpenRecord function, let us consider an example. Suppose we have a table named “Sales” with the following data:

| Product | Sales |

|———|——-|

| A | 100 |

| B | 200 |

| C | 300 |

We can use the following M code to convert the table to a record:


let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Sales", Int64.Type}}),
    #"Converted to Record" = Record.FromTable(#"Changed Type")
in
    #"Converted to Record"

The above code first loads the data from the “Sales” table, changes the data types of the “Product” and “Sales” columns to text and integer respectively, and then converts the resulting table to a record using the Record.FromTable function.

We can now use the Type.IsOpenRecord function to check if the resulting record is an open record:


let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Sales", Int64.Type}}),
    #"Converted to Record" = Record.FromTable(#"Changed Type"),
    #"Check If Open Record" = Type.IsOpenRecord(#"Converted to Record")
in
    #"Check If Open Record"

The above code adds a step to check if the resulting record is an open record using the Type.IsOpenRecord function. The output of the function will be a logical value indicating whether the record is open or not.

Using the Type.IsOpenRecord Function

The Type.IsOpenRecord function can be used in a variety of scenarios to simplify data transformations. One such scenario is when working with JSON data.

Consider the following JSON data:


{
    "name": "John Doe",
    "age": 30,
    "address": {
        "street": "123 Main St",
        "city": "Anytown",
        "state": "CA",
        "country": "USA"
    },
    "phone": {
        "home": "555-555-5555",
        "work": "444-444-4444"
    }
}

We can use the following M code to convert the JSON data to a record:


let
    Source = Json.Document(JsonData),
    #"Converted to Record" = Record.FromList({Source}, type nullable record [name=text, age=number, address=nullable [street=text, city=text, state=text, country=text], phone=nullable [home=text, work=text]])
in
    #"Converted to Record"

The above code first loads the JSON data, and then converts it to a record using the Record.FromList function. The type definition for the record is explicitly defined in the code.

We can now use the Type.IsOpenRecord function to check if the resulting record is an open record:


let
    Source = Json.Document(JsonData),
    #"Converted to Record" = Record.FromList({Source}, type nullable record [name=text, age=number, address=nullable [street=text, city=text, state=text, country=text], phone=nullable [home=text, work=text]]),
    #"Check If Open Record" = Type.IsOpenRecord(#"Converted to Record")
in
    #"Check If Open Record"

The above code adds a step to check if the resulting record is an open record using the Type.IsOpenRecord function. This is useful when working with JSON data as it allows us to check if the JSON data contains any additional fields beyond those explicitly defined in the type definition.

The Type.IsOpenRecord function is a useful M function available in Power Query that allows users to check if a given record is an open record. The M code behind the function is relatively simple, and it can be used in a variety of scenarios to simplify data transformations. Understanding how to use the Type.IsOpenRecord function can help users work with data more efficiently, and ultimately save time and effort.

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

Power Query and M Intensive Training Course
£1,050.00 – £26,550.00
Select options
Power Query and M Introduction
£395.00 – £9,750.00
Select options
Power Query and M Intermediate
£395.00 – £9,750.00
Select options
Power Query and M Advanced
£395.00 – £9,750.00
Select options

A

B

C

D

E

F

G

H

I

J

L

M

N

O

P

R

S

T

U

V

W

X

#

Understanding the Type.IsOpenRecord Function

The M Code Behind the Type.IsOpenRecord Function

Using the Type.IsOpenRecord Function

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

Power Query and M Intensive Training Course

Power Query and M Introduction

Power Query and M Intermediate

Power Query and M Advanced

Upcoming Courses

Contact Us

Upload Your Profile

A

B

C

D

E

F

G

H

I

J

L

M

N

O

P

R

S

T

U

V

W

X

#

Understanding the Type.IsOpenRecord Function

The M Code Behind the Type.IsOpenRecord Function

Using the Type.IsOpenRecord Function

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

Power Query and M Intensive Training Course

Power Query and M Introduction

Power Query and M Intermediate

Power Query and M Advanced

Upcoming Courses

Contact Us