Table.ToRecords
Converts a table into a list of records, one record for each row, with the column names as field names.
Table.ToRecords(table as table) as listReturns: A list of records.
What it does
Table.ToRecords turns each row into a record, where the column names become the field names. A record can be read by name, which makes row-by-row logic easier to read than with Table.ToRows. It is useful for a function that needs to look at one row at a time, such as building a sentence from several named fields.
Syntax and arguments
- table: the table to convert. Required.
Example
A list of invoices is turned into records, and the first invoice's number and amount are read by name.
let
Source = Excel.CurrentWorkbook(){[Name = "Invoices"]}[Content],
Rows = Table.ToRecords(Source),
First = Rows{0},
Number = First[InvoiceNumber],
Amount = First[Amount]
in
Amount
Each row is a record. Rows{0} takes the first record, and the fields are read by name.
Example: a second, different use
Each row is turned into a line for an exception note, using the vendor name and the reference from the row.
let
Source = Excel.CurrentWorkbook(){[Name = "Exceptions"]}[Content],
Rows = Table.ToRecords(Source),
Notes = List.Transform(Rows, (row) => row[Vendor] & " " & row[Reference])
in
Notes
Things to watch
- A record is read by its field name, and the name must match the column name exactly.
- The whole table is loaded into memory as records. On a large table this is slow, so prefer table functions where you can.
- Field values have the types of their columns. Null values need care in text joins.
- Query folding: this step does not fold. Use it after the filters.
In business terms
Row-by-row rules, such as a memo line or a flag that depends on several fields, read most clearly as records. Writing the rule with named fields makes it easy for a reviewer to check.