Table.ToRecords

What is Power Query M?

Power Query M is a language used to transform and manipulate data in Microsoft Excel and Power BI. It’s a functional language that allows you to create custom functions, transform data, and perform complex calculations. Power Query M is used in the Power Query Editor, which is a tool used to extract, transform, and load data from various sources.

Understanding the Table.ToRecords Function

The Table.ToRecords function is used to convert a table into a list of records. This function takes a single parameter, which is the table that you want to convert. The resulting list contains one record for each row in the table, where each record contains the values of all the columns in that row.

Here’s an example of how the Table.ToRecords function works:

In this example, we have a table with two columns (Name and Age) and three rows. We use the Table.ToRecords function to convert this table into a list of records. The resulting list contains three records, where each record represents a row in the original table.

The M Code Behind the Table.ToRecords Function

The M code behind the Table.ToRecords function is relatively simple. Here’s what it looks like:

Let’s break down this code and see what it’s doing.

The first line of code defines the function and sets its input parameter to “Table”. The input parameter represents the table that we want to convert.

The second line of code is where the magic happens. We use the List.Transform function to transform the rows of the table into records. The List.Transform function takes two parameters: the first parameter is the list that we want to transform (in this case, the rows of the table), and the second parameter is a function that we want to apply to each element of the list.

The function that we’re applying to each element of the list is defined using the “each” keyword. This keyword is used to represent the current element of the list that we’re working with. In this case, the current element is a row of the table.

The Record.FromList function is used to create a record from the values in the current row. This function takes two parameters: the first parameter is the list of values that we want to include in the record (in this case, the values in the current row), and the second parameter is the list of field names (column names) that we want to use for the record. We use the Table.ColumnNames function to get the list of field names for the table.

Finally, the function returns the resulting list of records.

The Table.ToRecords function is a powerful tool for converting tables into lists of records in Power Query M. Understanding the M code behind this function can help you create more complex and flexible data transformations. With this knowledge, you’ll be able to take advantage of the full power of Power Query M and create custom solutions that meet your unique data needs.

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

Upcoming Courses

Contact Us