If you have a few lists of values and want to turn them into a table where each list becomes a column, the Table.FromColumns function is what you reach for. You hand it your lists, optionally tell it what to call the columns, and it builds the table. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.
Syntax of Table.FromColumns Function
Table.FromColumns(lists as list, optional columns as any) as table
where
lists(required, list). A list of lists, where each inner list becomes one column. The values inside an inner list fill that column from top to bottom, so the number of inner lists sets the number of columns.columns(optional, any). Sets the column names and, in one form, their data types. It can be a list of names like{"Name","City"}, a table type liketype table[ID=Int64.Type], or an integer count. Omit it and the columns are auto-named.
Returns: a table built column by column from lists. If some inner lists are shorter than others, the missing cells are filled with null.
In plain terms, you give it a list of lists and it stands each one up as a column.
Example 1: Build a table from two lists
Pass two lists and nothing else. The first list becomes the first column and the second list becomes the second column.
let
Source = Table.FromColumns({{"Asha","Marco","Lena"},{"Delhi","Rome","Berlin"}})
in
Source
The result is a two-column table:
| Column1 | Column2 |
|---|---|
| Asha | Delhi |
| Marco | Rome |
| Lena | Berlin |
Each inner list runs down a column, not across a row. Because no names were given, the columns come out as Column1 and Column2.
Example 2: Name the columns with a list of names
To set real column names, pass a second list with one name per column. This is the form you will use most.
let
Source = Table.FromColumns({{"Asha","Marco","Lena"},{"Delhi","Rome","Berlin"}},{"Name","City"})
in
Source
Now the headers read Name and City:
| Name | City |
|---|---|
| Asha | Delhi |
| Marco | Rome |
| Lena | Berlin |
The names line up with the lists in order, so the first name labels the first list and so on.
Example 3: Unequal-length lists fill with null
The lists do not have to be the same length. Here the second list has only two values while the first has three.
let
Source = Table.FromColumns({{"Asha","Marco","Lena"},{"Delhi","Rome"}},{"Name","City"})
in
Source
The shorter column gets a null in its missing bottom cell so the table stays rectangular:
| Name | City |
|---|---|
| Asha | Delhi |
| Marco | Rome |
| Lena | null |
Lena has no matching city, so that cell is null rather than an error.
Example 4: Set names and data types with a table type
Passing a table type as the second argument sets the names and the data types together, in one step.
let
Source = Table.FromColumns({{101,102,103},{"Asha","Marco","Lena"}},type table[EmployeeID=Int64.Type,Name=Text.Type])
in
Source
The headers come from the type, and the columns are typed:
| EmployeeID | Name |
|---|---|
| 101 | Asha |
| 102 | Marco |
| 103 | Lena |
EmployeeID arrives as a whole number and Name as text. With a plain list of names you would have to add a separate Table.TransformColumnTypes step to get the same typing.
Things to keep in mind with Table.FromColumns
- Each inner list becomes a column, not a row. This is the most common surprise. If you want each inner list to become a row instead, use
Table.FromRows. - Without a
columnsargument you get default names. The columns come out asColumn1,Column2,Column3, and so on, in order (Example 1). - Unequal-length lists pad the short columns with
null. The table stays rectangular and the shorter columns getnullin the bottom cells (Example 3). - Columns are type
anyunless you pass a table type. A list of names sets names only. Passtype table[...]to set names and real data types in one go (Example 4).
Common questions about Table.FromColumns
What is the difference between Table.FromColumns and Table.FromRows?
Both build a table from a list of lists, but they read the inner lists in opposite directions.
Table.FromColumns treats each inner list as a column. Table.FromRows treats each inner list as a row, so the same input produces a transposed result.
List of All Power Query Functions
Related Power Query Functions / Articles:
