Table.FromRows

One of the most versatile M functions in Power Query is Table.FromRows. This function enables users to create a table from a list of rows. In this article, we will take a closer look at the M code behind this function and explore some examples of how it can be used.

Creating a Table from Rows

The Table.FromRows function takes a list of rows as its input and returns a table. Each row in the list is itself a list of values that correspond to the columns in the table. The syntax for the function is as follows:

The ‘rows’ parameter is a list of lists, where each inner list represents a row in the resulting table. The optional ‘columns’ parameter allows users to specify the column names and data types for the table. If this parameter is not provided, Power Query will attempt to infer the column names and data types from the data itself.

Here is an example of how the function can be used to create a simple table:

In this example, we have defined a list of rows that contains the values for the table. We then pass this list to the Table.FromRows function, which creates a table with two columns (‘Column1’ and ‘Column2’) and three rows.

Specifying Column Names and Data Types

As mentioned earlier, the Table.FromRows function can also accept a ‘columns’ parameter that allows users to specify the column names and data types for the resulting table. This can be useful when the data itself does not contain column names or when the default data types inferred by Power Query are not accurate.

The ‘columns’ parameter is a list of records, where each record represents a column in the table. The record contains two fields: ‘Name’ (the name of the column) and ‘Type’ (the data type of the column).

Here is an example of how the function can be used to create a table with specified column names and data types:

In this example, we have defined a list of rows as before, but we have also defined a list of columns that specifies the column names and data types (‘ID’ as an integer and ‘Name’ as text). We then pass both lists to the Table.FromRows function, which creates a table with the specified column names and data types.

The Table.FromRows function is a powerful tool for creating tables in Power Query. It allows users to create tables from lists of rows and to specify column names and data types. By understanding the M code behind this function, users can unlock even more advanced functionality in Power Query and create more complex data transformations.

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

Upcoming Courses

Contact Us