Table.FromColumns - Table Function

Table.FromColumns is a Power Query M function that creates a table of type columns from a list containing nested lists with column names and values. The function returns a table with columns and their respective values.

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

Syntax

Table.FromColumns(
   lists as list,
   optional columns as any,
) as table

Description

Table.FromColumns creates a table from a list of columns and specified values. It forms a table of type columns from a list lists containing nested lists with column names and values. If some columns have more values than others, missing values will be filled with ‘null’ for nullable columns.

Examples

The Table.FromColumns function allows you to create tables by defining the data for each column and optionally the column names and types. Let’s break down how this function works with a few examples.

Basic Table Creation

In its simplest form, you can create a table by only providing the values for each column, without specifying column names or data types. Here’s an example:

Table.FromColumns(
     {
        { 1, 2 },            // Values for the 1st column
        { "Apple", "Prume" } // Values for the 2nd column
     }
)

This code generates a table with two columns. Since we didn’t specify column names, the function automatically assigns default names like “Column1” and “Column2.” The resulting table looks like this:

Specifying Column Names

To make your table more meaningful, you can explicitly define the column names. Here’s how you can do it:

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

In this example, we’ve added column names “ProductKey” and “Product” to the table. The output would look like this:

Table.FromColumns creates table and specifies column names in Power Query M

Take note that these columns still miss data types. If desired, we can also specify those.

Adding Data Types

Sometimes, it’s important to define the data types for each column right from the start. Here’s how you can define a table with both column names and data types:

Table.FromColumns(
     {
         { 1, 2 },
         { "Apple", "Prume" }
     },
     type table[ ProductKey = Int64.Type, Product = Text.Type ]
)

In this case, the ProductKey column is defined as an integer (Int64.Type), and the Product column is defined as text (Text.Type). The resulting table now has data types:

Table.FromColumns creates table with column names and data types in Power Query M

Handling Missing Values

What happens if the data you provide doesn’t cover all the rows for each column? The Table.FromColumns function automatically fills in any missing values with null. For example:

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

In this example, the Product column only has two values (“Apple” and “Prume”), but the ProductKey column has three values (1, 2, and 3). The function fills the missing value in the Product column with null, resulting in the following table:

Table.FromColumns adds null for missing values in Power Query M

This automatic handling of missing data helps ensure that your table is always complete, even if some values are missing.

Try it yourself

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

let
    Values = Table.FromColumns(
     {
        {1, 2},            // Values for the 1st column
        {"Apple", "Prume"} // Values for the 2nd column
     }
),
    NameAndValue = Table.FromColumns(
     {
         { 1, 2 },
         { "Apple", "Prume" }
     }, 
     { "ProductKey", "Product" }  // specifies column names
),
    NameTypeAndValue = Table.FromColumns(
     {
         { 1, 2 },
         { "Apple", "Prume" }
     },
     type table[ ProductKey = Int64.Type, Product = Text.Type ]
),
    MissingValues = Table.FromColumns(
     {
         { 1, 2, 3 },         // specifies 3 column values
         { "Apple", "Prume" } // specifies 2 column values
     },
     type table[ ProductKey = Int64.Type, Product = Text.Type ]
)
in
    MissingValues

Learn more about Table.FromColumns 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
Get Value from Previous Row using Power Query
This article shows how to get the previous row value using Power Query. You can adjust the 3 ways to also retrieve an earlier or later row. » Read more

Other functions related to Table.FromColumns are:

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