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
Return a table from a list of customer names in a list. Each value in the customer list item becomes a row value, and each list becomes a column.
Table.FromColumns( {
{1, "Bob", "123-4567"},
{2, "Jim", "987-6543"},
{3, "Paul", "543-7890"}
} )
/* Output:
Table.FromRecords( {
[Column1 = 1, Column2 = 2, Column3 = 3],
[Column1 = "Bob", Column2 = "Jim", Column3 = "Paul"],
[Column1 = "123-4567", Column2 = "987-6543", Column3 = "543-7890"]
} )
*/
Create a table from a given list of columns and a list of column names.
Table.FromColumns(
{
{1, "Bob", "123-4567"},
{2, "Jim", "987-6543"},
{3, "Paul", "543-7890"}
},
{"CustomerID", "Name", "Phone"}
)
/* Output:
Table.FromRecords( {
[CustomerID = 1, Name = 2, Phone = 3],
[CustomerID = "Bob", Name = "Jim", Phone = "Paul"],
[CustomerID = "123-4567", Name = "987-6543", Phone = "543-7890"]
} )
*/
Create a table with different number of columns per row. The missing row value is null.
Table.FromColumns(
{
{1, 2, 3},
{4, 5},
{6, 7, 8, 9}
},
{"column1", "column2", "column3"}
)
/* Output:
Table.FromRecords( {
[column1 = 1, column2 = 4, column3 = 6],
[column1 = 2, column2 = 5, column3 = 7],
[column1 = 3, column2 = null, column3 = 8],
[column1 = null, column2 = null, column3 = 9]
} )
*/
Related articles
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
Related functions
Other functions related to Table.FromColumns are: