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

If you want to turn a Power Query table into a list of rows, so you can loop over each row or build text from its values, the Table.ToRows function is what you reach for.

It hands you each row as its own list of values, which is exactly what you need when a step expects lists instead of a table.

Table of Contents

Syntax of Table.ToRows Function

Table.ToRows(table as table) as list

where

table (required, table). The table you want to break into a list of rows.

Returns: a list whose items are themselves lists, one inner list per row. The column headers are dropped, so each inner list holds only that row’s values.

In plain terms, you give it a table and it gives you back the rows as a nested list, with the values in the same column order as the table.

Example 1: Turn a table into a list of rows

Say you have a Sales table with a Region and a Units column.

Here is the starting data:

Region	Units
East	10
West	25
North	8
South	17

You want each row as its own list of values.

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Rows = Table.ToRows(Source)
in
    Rows

Result: {{"East",10},{"West",25},{"North",8},{"South",17}}

Each row becomes its own inner list. The headers Region and Units are gone, and the rows stay in the same order.

Example 2: Count the rows in a table

Because Table.ToRows gives you a list, you can count its items to get the number of rows.

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    N = List.Count(Table.ToRows(Source))
in
    N

Result: 4

List.Count returns how many inner lists there are, which is one per row.

Example 3: Grab a single row as a flat list

You can index into the outer list to pull one row out by its position.

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    FirstRow = Table.ToRows(Source){0}
in
    FirstRow

Result: {"East",10}

{0} takes the first row, and you get back a flat list of that row’s values.

Example 4: Join each row into a delimited string

Sometimes you want one delimited string per row, say for a CSV-style export or a lookup key.

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Joined = List.Transform(Table.ToRows(Source),each Text.Combine(List.Transform(_,Text.From),"|"))
in
    Joined

Result: {"East|10","West|25","North|8","South|17"}

The inner List.Transform runs Text.From over each value so numbers become text, then Text.Combine joins them with a |. The outer List.Transform does this for every row.

Things to keep in mind with Table.ToRows

Headers are dropped, you get values only. The result has no column names, just the values in column order. So reading a value depends on the column’s position, and if you reorder columns the positions change.
Row order is preserved. The inner lists come out in the same order the rows sit in the table.
The result is nested, so a single value needs two indexes. To reach one cell you index the outer list for the row, then the inner list for the value, like rows{0}{1}.
Table.ToColumns is the column-wise sibling. It slices the table by column instead of by row, returning one inner list per column.
Table.ToList combines each row into one value. Instead of a nested list it gives a flat list, joining each row’s columns into a single text value.
Table.FromRows is the inverse. It rebuilds a table from a list of row-lists, so it undoes what Table.ToRows does.

Common questions about Table.ToRows

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

Table.ToRows gives one inner list per row, Table.ToColumns gives one inner list per column, and Table.ToList gives a flat list where each row is collapsed into a single combined value.

How do I get one specific cell from the result?

Index twice: the first index picks the row and the second picks the value within it. For example, Table.ToRows(Source){1}{0} returns the first value of the second row.

List of All Power Query Functions

Related Power Query Functions / Articles: