Record.ToTable

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Record.ToTable

Understanding the Record Data Type

Before we dive into the M code behind Record.ToTable, it’s important to understand the record data type. In Power Query, a record is a collection of fields and values that are stored as key-value pairs. For example, a record might contain fields such as Name, Age, and Gender, each with a corresponding value. Records are often used to represent complex data structures, such as JSON objects.

The Basic Syntax of Record.ToTable

The basic syntax of Record.ToTable is as follows:

Record.ToTable(record as record)

This function takes a single argument, which is the record that you want to convert into a table. The result is a table that contains two columns: one for the field names and one for the corresponding values. Each row in the table represents a single field-value pair from the original record.

Converting a Record into a Table

To convert a record into a table using Record.ToTable, you simply need to provide the function with the record that you want to convert. For example, to convert a record called CustomerInfo into a table, you would use the following M code:

Record.ToTable(CustomerInfo)

This would result in a table that contains two columns: one for the field names and one for the corresponding values. Each row in the table represents a single field-value pair from the original record.

Renaming the Table Columns

By default, the columns in the resulting table created by Record.ToTable are named "Name" and "Value". However, you can rename these columns to better reflect the contents of the record. To do this, you can use the optional second argument of Record.ToTable, which is a record that contains the new column names. For example, to change the column names to "Field" and "Data", you would use the following M code:

Record.ToTable(CustomerInfo, [Column1Name=”Field”, Column2Name=”Data”])

This would result in a table that contains two columns: one called "Field" that contains the field names from the original record, and one called "Data" that contains the corresponding values.

Using Record.ToTable with Nested Records

In some cases, the record that you want to convert to a table may contain nested records. For example, a record that represents a customer might contain a nested record that represents the customer's address. When using Record.ToTable with nested records, the resulting table will contain columns for each of the nested records, as well as columns for the fields and values in the top-level record.

Record.ToTable is a powerful function in Power Query that allows you to convert records into tables, making it easier to manipulate and transform complex data structures. By understanding the M code behind this function and its capabilities, you can take full advantage of its features and optimize your data analysis workflows.

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)