Table.ToColumns Function in Power Query M (4 Easy Examples)

If you want to break a table apart into one list per column, Table.ToColumns does exactly that. You get back a list of lists, ready to feed other list functions.

Table of Contents

Syntax of Table.ToColumns Function

Table.ToColumns(table as table) as list

where

table (required, table). The source table you want to split into per-column lists.

Returns: a list of lists, one inner list per column, in column order. Column names are dropped.

Each inner list holds the full set of values for one column, left to right. So the result is column-major, the exact opposite of Table.ToRows.

Example 1 – Get the first column as a list

Say you have a Reps table with rep names, regions, and deal counts.

Rep	Region	Deals
Asha	North	4
Ravi	South	7
Maya	East	5

Splitting the table into columns and grabbing index 0 pulls the first column out as a flat list.

let
    Source = Excel.CurrentWorkbook(){[Name="Reps"]}[Content],
    Cols = Table.ToColumns(Source),
    FirstColumn = Cols{0}
in
    FirstColumn

Result: {"Asha", "Ravi", "Maya"}

Example 2 – Turn a whole table into a list of column lists

Here is a small Customers table.

CustomerID	Name	Phone
1	Bob	123-4567
2	Jim	987-6543

Run Table.ToColumns on the whole table and you get one inner list per column.

let
    Source = Excel.CurrentWorkbook(){[Name="Customers"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"CustomerID",Int64.Type},{"Name",type text},{"Phone",type text}}),
    Columns = Table.ToColumns(Typed)
in
    Columns

Result: {{1, 2}, {"Bob", "Jim"}, {"123-4567", "987-6543"}}

Example 3 – Sum a numeric column pulled out as a list

This Orders table has units per product.

Product	Units
Pens	12
Pads	8
Folders	15
Clips	5

Grab the Units column (index 1) as a list, then hand it to List.Sum.

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Units",Int64.Type}}),
    UnitsList = Table.ToColumns(Typed){1},
    Total = List.Sum(UnitsList)
in
    Total

Result: 40

Example 4 – Zip two columns into paired rows

The Scores table pairs players with points.

Player	Points
Lee	30
Sara	45
Omar	20

Table.ToColumns gives you the two columns, and List.Zip stitches them back into row pairs.

let
    Source = Excel.CurrentWorkbook(){[Name="Scores"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Player",type text},{"Points",Int64.Type}}),
    Columns = Table.ToColumns(Typed),
    Paired = List.Zip(Columns),
    FirstPair = Paired{0}
in
    FirstPair

Result: {"Lee", 30}

Things to keep in mind with Table.ToColumns

It drops column names. The result is positional only, so you recover a column by its index ({0}, {1}), never by header name. If you need to keep columns by name, use Table.SelectColumns instead.
It is column-major, not row-major. {0} is the whole first column, not the first row. If you want rows, reach for Table.ToRows instead.
Table.ToRows has the same signature but the opposite orientation. Use Table.ToColumns for a whole column as a list, Table.ToRows for per-record work.
It is the clean way to pull one column out as a flat list to feed List.Sum, List.Max, List.Distinct, or List.Transform.
Columns from Excel.CurrentWorkbook import untyped, so run Table.TransformColumnTypes first or numeric functions like List.Sum will not behave.

Common questions about Table.ToColumns

Table.ToColumns vs Table.ToRows. What is the difference?

Both take a table and return a list of lists. Table.ToColumns is column-major, so each inner list is a whole column. Table.ToRows is row-major, so each inner list is a whole row.

How do I get a single column as a list?

Index into the result with the column’s position. Table.ToColumns(t){0} gives the first column, {1} the second, and so on. Headers are gone, so you go by position.

List of All Power Query Functions

Related Power Query Functions / Articles: