Table.ToRows
Converts a table into a list of rows, where each row is a list of its values.
Table.ToRows(table as table) as listReturns: A list of lists.
What it does
Table.ToRows turns a table into a list of rows. Each row becomes a list of its values, in column order. The column names are not part of the result. It is useful when another function needs the raw values, or when you want to pick out one row by its position.
Syntax and arguments
- table: the table to convert. Required.
Example
A table of exchange rates has one row for each currency. The first row is read as a list, to take the first currency and rate.
let
Source = Excel.CurrentWorkbook(){[Name = "Rates"]}[Content],
AsRows = Table.ToRows(Source),
FirstRow = AsRows{0}
in
FirstRow
The first step gives a list of rows. Position 0 is the first row, so FirstRow is one list of that row's values in column order.
Example: a second, different use
A count of rows is taken from a list of rows, and the value in the second column of the last row is returned.
let
Source = Excel.CurrentWorkbook(){[Name = "Checks"]}[Content],
Rows = Table.ToRows(Source),
LastRow = List.Last(Rows),
SecondValue = LastRow{1}
in
SecondValue
Things to watch
- Positions are zero-based. The first value of a row is at position 0.
- The column names are lost. Use Table.ToRecords when you need names.
- Column order decides which value is which. A changed column order silently changes the result.
- Query folding: this step does not fold. It loads all the rows.
In business terms
Reading one row or one value by position is useful for a check, such as the control total in the last row of an export. For most work, a named column such as Table.Max or a table function is clearer.