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

Table.ToList takes a table and returns a list, with one element per row. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to collapse the rows of a table into a flat list of values, this is the function you reach for.

Table of Contents

Syntax of Table.ToList Function

Table.ToList(table as table, optional combiner as nullable function) as list

where

table (required, table). The source table you want to turn into a list.
combiner (optional, nullable function). A function that merges each row’s column values into a single value. Omit it and the default joins every column of a row into one text value, separated by a comma.

Returns: a list with one element per source row. Each element is whatever the combiner produced for that row.

In plain terms, it walks the table row by row and hands you back a list where each row has become one value.

Example 1: Convert a table to a list with the default combiner

Say you have a People1 table with a First and a Last column, and you want it as a list.

Here is the starting data:

First	Last
Asha	Rao
Ben	Lee
Mia	Park

Call Table.ToList with no combiner:

let
    Source = Excel.CurrentWorkbook(){[Name="People1"]}[Content],
    Result = Table.ToList(Source)
in
    Result

Result: {"Asha,Rao", "Ben,Lee", "Mia,Park"}

This is the surprising part. The default combiner glues both columns of each row together into one text value with a comma between them, so you get one string per row, not the raw values.

Example 2: Join each row with a custom delimiter

Same table, but you want a space and a pipe between the values instead of a comma.

Here is the starting data:

First	Last
Asha	Rao
Ben	Lee
Mia	Park

Pass Combiner.CombineTextByDelimiter with your own separator:

let
    Source = Excel.CurrentWorkbook(){[Name="People2"]}[Content],
    Result = Table.ToList(Source,Combiner.CombineTextByDelimiter(" | "))
in
    Result

Result: {"Asha | Rao", "Ben | Lee", "Mia | Park"}

The combiner controls the separator, so each row’s columns are now joined with | instead of the default comma.

Example 3: Turn a single column into a clean list

This is the common real use. You have a one-column Cities3 table and you want a plain list of those city names.

Here is the starting data:

City
Austin
Denver
Reno
Tampa

Call Table.ToList on the single-column table:

let
    Source = Excel.CurrentWorkbook(){[Name="Cities3"]}[Content],
    Result = Table.ToList(Source)
in
    Result

Result: {"Austin", "Denver", "Reno", "Tampa"}

With only one column per row, the default combiner has nothing to join, so each element is just that cell’s value. You get a clean list of the column.

Example 4: Build a full-name list with a custom combiner

You have a Staff4 table with First and Last, and you want full names like Priya Nair joined by a space.

Here is the starting data:

First	Last
Priya	Nair
Omar	Khan
Lena	Voss

Pass a custom combiner that joins the row’s values with a space:

let
    Source = Excel.CurrentWorkbook(){[Name="Staff4"]}[Content],
    Result = Table.ToList(Source,each Text.Combine(_," "))
in
    Result

Result: {"Priya Nair", "Omar Khan", "Lena Voss"}

Here the combiner receives the row as a list of values in _, not as a record. So you pass _ straight to Text.Combine. Field access like [First] does not work inside this combiner, because there is no record to read fields from.

Things to keep in mind with Table.ToList

The default combiner joins ALL columns of each row with a comma into one text. If you expected raw values, you instead get one comma-joined string per row (Example 1). Pass your own combiner, or select a single column first, if you want the values untouched.
A custom combiner’s function receives the row as a LIST of values, not a record. Use _ (the whole row list), as in each Text.Combine(_," "). Field access like [First] fails here because the combiner is handed a list, not a record.
The default text combiner needs text and will stringify or error on numbers. A numeric column hits Expression.Error: We cannot convert the value 5 to type Text. Wrap values with Text.From inside a custom combiner, or convert the column to text first.
To pull one column as a list, Source[ColumnName] is cleaner. It returns the column’s values with their native types and no comma-joining, which is usually what you want from a single column anyway.
Filter the table first if you only want some rows. Wrap the source in Table.SelectRows before the Table.ToList step so the list only carries the rows you kept.
Table.ToList flattens, its siblings do not. Table.ToList gives a flat list (one value per row). Table.ToRows gives a list of lists (each row as a list of its cells), and Table.ToColumns gives a list of lists (each column as a list). To go the other way and stack tables back together, use Table.Combine.

Common questions about Table.ToList

How do I get just one column of a table as a list?

Use Source[ColumnName], for example Source[City]. It returns that column’s values as a list with their original types, with no comma-joining that Table.ToList would apply.

What is the difference between Table.ToList, Table.ToRows, and Table.ToColumns?

Table.ToList returns a flat list with one combined value per row. Table.ToRows returns a list of lists, one inner list per row. Table.ToColumns returns a list of lists, one inner list per column.

List of All Power Query Functions

Related Power Query Functions / Articles: