Table.FromRows - Table Function

Table.FromRows is a Power Query M function that creates a table from a list of rows, where each element of the list is an inner list containing column values for a single row. The function returns a table with the specified rows and columns.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Table.FromRows(
   rows as list,
   optional columns as any,
) as table

Description

Table.FromRows creates a table from a list, rows, where each list element is a row. Optional parameters for columns, table type, or number of columns can be provided.

Examples

Let’s explore how Table.FromRows works through various examples.

Basic Table Creation

In its simplest form, Table.FromRows creates a table by taking a list of lists, where each inner list represents a row in the table. The columns are automatically named as Column1, Column2, and so on.

Table.FromRows(
  {
    { 1, "Apple" },  // values for first row
    { 2, "Prume" }  // values for second row
  }
)

This code generates a table with two rows and two columns. Since no column names are specified, the function assigns default names, resulting in the following table:

Table.FromRows creates table using lists in Power Query M

Specifying the Number of Columns

You can explicitly define the number of columns when creating a table using Table.FromRows. However, this is optional and does not change the output if the number of columns is evident from the data.

Table.FromRows(
  {
    { 1, "Apple" },  
    { 2, "Prume" } 
  },
  2  // creates 2 columns
)

In this example, specifying 2 as the number of columns generates a table identical to the previous one:

Defining Column Names

To make your table more meaningful, you can explicitly define the column names. This helps to better organize and understand the data within the table.

Table.FromRows(
  {
    { 1, "Apple" },  
    { 2, "Prume" } 
  },
  // specifies column names
  { "ProductKey", "Product" }  
)

This code assigns the names “ProductKey” and “Product” to the columns. The resulting table looks like this:

Table.FromRows uses Table Type to specify column names in Power Query M

Notice that the data types are missing.

Specifying Data Types

You can also define the data types for each column when creating a table with Table.FromRows. This makes sure that the columns have clear data types and are checked when loading the data into the destination system. Here’s how you can do it:

Table.FromRows(
  {
    { 1, "Apple" },  
    { 2, "Prume" } 
  },
  // specifies column types
  type table[ ProductKey = Int64.Type, Product = Text.Type ]  
)

In this example, the ProductKey column is explicitly defined as an integer (Int64.Type), and the Product column is defined as text (Text.Type). The resulting table is:

Table.FromRows uses Table Type to specify column names and data types in Power Query M

Try it Yourself

You can paste the following code into the advanced editor to experiment with these examples:

let
  ListsOnly = 
    Table.FromRows(
      {
        { 1, "Apple" },  // values for first row
        { 2, "Prume" }   // values for second row
      }
    ),
  NumberOfColumns = 
    Table.FromRows(
      {
        { 1, "Apple" },  
        { 2, "Prume" } 
      },
      2  // creates 2 columns
    ),
  DefineColumnNames = 
    Table.FromRows(
      {
        { 1, "Apple" },  
        { 2, "Prume" } 
      },
      // specifies column names
      { "ProductKey", "Product" }  
    ),
  SpecifyColumnAndType = 
    Table.FromRows(
      {
        { 1, "Apple" },  
        { 2, "Prume" } 
      },
      // specifies column types
      type table[ ProductKey = Int64.Type, Product = Text.Type ]  
    )
in
  SpecifyColumnAndType

Learn more about Table.FromRows in the following articles:

Create Tables from Scratch in Power Query M (40+ Examples)
Creating tables from scratch in Power Query can be tricky, but this post shows you how. You learn how to work with lists, records and much more! » Read more

Other functions related to Table.FromRows are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-fromrows