Table.FromRecords

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.FromRecords

What is Table.FromRecords Function?

Table.FromRecords is a Power Query M function used to create a new table from a list of records. A record is a collection of key-value pairs that represent a single row in a table. The function takes a list of records and converts it into a table with the columns specified in the keys.

The syntax for the Table.FromRecords function is as follows:

Table.FromRecords(records as list, optional columns as any)

The records parameter is a list of records, and the columns parameter is optional. If not specified, the function will use the keys from the first record in the list as the column names.

Understanding the M Code behind Table.FromRecords

To understand the M code behind the Table.FromRecords function, let’s consider an example. Suppose we have a list of records as follows:

let

records = {

[Name="John", Age=25, Gender="Male"],

[Name="Jane", Age=30, Gender="Female"],

[Name="Mark", Age=35, Gender="Male"]

}

in

Table.FromRecords(records)

The above M code creates a list of three records, each containing the fields Name, Age, and Gender. The Table.FromRecords function is then used to convert this list into a table with columns Name, Age, and Gender.

The code inside the Table.FromRecords function is as follows:

Table.FromRecords(

records,

type table [

Name = text,

Age = Int64.Type,

Gender = text

]

)

The second parameter in the Table.FromRecords function specifies the column names and their data types. In this case, we have specified Name as text, Age as Int64, and Gender as text.

Customizing Table.FromRecords Function

The Table.FromRecords function can be customized by modifying the columns parameter. For example, suppose we want to change the data type of the Age column to Decimal. We can modify the M code as follows:

let

records = {

[Name="John", Age=25, Gender="Male"],

[Name="Jane", Age=30, Gender="Female"],

[Name="Mark", Age=35, Gender="Male"]

},

columns = [

Name = type text,

Age = type number,

Gender = type text

]

in

Table.FromRecords(records, columns)

Here, we have modified the columns parameter to specify the data type of Age as number instead of Int64. This will create a new table with the Age column as a decimal instead of an integer.

In conclusion, the Table.FromRecords function in Power Query is a powerful tool for creating tables from lists of records. By understanding the M code behind this function, you can customize it to meet your specific needs. With Power Query and M, data transformation and analysis become much easier and efficient.

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)