Type.RecordFields

D

Back to top

T

Back to top

#

Back to top

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

What is a Record in Power Query?

In Power Query, a record is a data structure that consists of a set of named values. It is similar to a database record or a dictionary in other programming languages. Each value in a record is associated with a unique name, which is referred to as a field. For example, the following record has two fields: Name and Age.

[

Name = "John",

Age = 30

]

Records are often used to represent data in a structured way, especially when dealing with complex data structures such as JSON or XML documents.

How to Use the Type.RecordFields Function

The Type.RecordFields function is used to extract the fields of a record and return them as a list of field names. The function takes a record as its argument and returns a list of text values.

Type.RecordFields(record as record) as list

For example, if we have a record that looks like this:

[

Name = "John",

Age = 30,

Address = "123 Main St"

]

We can use the Type.RecordFields function to extract the fields of the record like this:

Type.RecordFields([Name = "John", Age = 30, Address = "123 Main St"])

This would return the following list of field names:

{

"Name",

"Age",

"Address"

}

Using Type.RecordFields in Data Transformations

The Type.RecordFields function is often used in data transformations to dynamically reference fields within a record. For example, suppose we have a table of customer data that contains records like this:

[

ID = 1,

Name = "John",

Age = 30,

Address = "123 Main St"

]

We may want to extract just the Name and Age fields from the record and create a new table that only contains those fields. We can do this using the Table.TransformColumns function and the Type.RecordFields function like this:

let

Source = Table.FromRecords({

[ID = 1, Name = "John", Age = 30, Address = "123 Main St"],

[ID = 2, Name = "Jane", Age = 25, Address = "456 Park Ave"]

}),

FieldsToKeep = Type.RecordFields(Record.FieldValues(Table.FirstN(Source, 1))),

NewTable = Table.TransformColumns(Source, List.Union({{"ID"}, FieldsToKeep}), each Record.Field(_, ColumnName))

in

NewTable

This code would create a new table that contains only the ID, Name, and Age columns from the original table.

The Type.RecordFields function is a powerful tool in the Power Query language that allows users to dynamically reference fields within a record. It is often used in data transformations to extract specific fields from a record and create new tables that contain only the desired fields. By understanding the M code behind this function, users can take full advantage of the capabilities of Power Query and transform their data in a flexible and efficient manner.

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)