Table.ToRows

Understanding Tables in Power Query

Before diving into the M code behind `Table.ToRows`, it is important to understand what a table is in Power Query. In Power Query, a table is a collection of rows and columns that represents data. Each row in a table represents a record, while each column represents a field or attribute of the record.

Tables in Power Query are created by importing data from various sources, such as Excel files, CSV files, databases, and web pages. Once a table is created, you can perform various operations on it, such as filtering, sorting, transforming, and aggregating data.

The M Code Behind Table.ToRows

The `Table.ToRows` function is a built-in M function in Power Query that takes a table as its argument and returns a list of rows. Each row in the list is itself a list of values from the original table.

Here is the M code behind the `Table.ToRows` function:

Let’s break down this code and understand what each line does.

Line 1: The Function Signature

The first line of the code defines the function signature, which specifies the name of the function (`Table.ToRows`) and the type of its argument (`table`). In Power Query, function signatures are written in the format `FunctionName(ArgumentName as ArgumentType) as ReturnType`.

Line 2: Converting the Table to a List

The second line of the code uses the `Table.ToList` function to convert the input table into a list. The `Table.ToList` function takes a table as its argument and returns a list of records, where each record is a dictionary with the column names as keys and the row values as values.

Line 3: Selecting All Columns of the Table

The third line of the code uses the `Table.SelectColumns` function to select all the columns of the input table. The `Table.SelectColumns` function takes a table as its first argument and a list of column names as its second argument. In this case, we pass the `Table.ColumnNames(Table)` expression as the second argument, which returns a list of all the column names of the input table.

Line 4: Converting Each Record to a List of Values

The fourth line of the code uses the `List.Transform` function to transform each record in the list into a list of values. The `List.Transform` function takes a list as its first argument and a transformation function as its second argument. In this case, we pass an anonymous function as the transformation function, which uses the `Record.FieldValues` function to extract the values of each record and return them as a list.

Line 5: Returning the List of Rows

The fifth and final line of the code returns the list of rows, which is the result of the `List.Transform` function in line 4.

In this article, we have explored the M code behind the `Table.ToRows` function in Power Query. We have seen how this function takes a table as its argument and returns a list of rows, each row being a list of values from the original table. By understanding the M code behind this function, we can better understand how Power Query works and how we can use it to transform our data.

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

Upcoming Courses

Contact Us