Downloads
Power Query MTable: build and convertIntermediate

Table.ToColumns

Converts a table into a list of columns, where each column is a list of its values.

Syntax
Table.ToColumns(table as table) as list

Returns: A list of lists, one for each column.

What it does

Table.ToColumns turns a table into a list of its columns. Each column becomes a list of values. The column names are not included. For reading one column by name, the simpler form Table[ColumnName] is clearer. Table.ToColumns is used when a function needs all the columns as lists, such as a rebuild with Table.FromColumns.

Syntax and arguments

Example

A bank export is converted to its columns, and the Amount column is read as a plain list of values, by position.

let
    Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
    Columns = Table.ToColumns(Source),
    Amounts = Columns{2}
in
    Amounts

Position 2 is the third column in the table. The list in Amounts holds that column's values in row order. If the columns are reordered, the position changes, so prefer naming the column with Source[Amount].

Example: a second, different use

A table is rebuilt from its columns after one column is changed, using Table.FromColumns.

let
    Source = Excel.CurrentWorkbook(){[Name = "Invoices"]}[Content],
    Names = Table.ColumnNames(Source),
    Columns = Table.ToColumns(Source),
    Rebuilt = Table.FromColumns(Columns, Names)
in
    Rebuilt

Things to watch

In business terms

Working one column at a time is common in schedules and checks, such as summing an amount column for a control total. The named form is the one an auditor will read most easily.

Related functions

All Power Query M functions · View as Markdown · M basics