Table.FromRecords
Builds a table from a list of records, where each record is one row with named fields.
Table.FromRecords(records as list, optional type as nullable type, optional missingField as nullable number) as tableReturns: A new table with one row for each record.
What it does
Table.FromRecords builds a table from records. A record is a set of named values, such as [Account = "1000", Name = "Cash"]. Each record becomes one row, and the field names become the column names. It is the clearest way to write a small table in M, because each value is labelled.
Syntax and arguments
- records: a list of records, in braces. Required.
- type: a table type that sets the column types. Optional.
- missingField: what to do when a record lacks a field. Optional.
Example
A small table of bank accounts is written with names and values in each record. The table is then used as a lookup for the cash report.
let
BankAccounts = Table.FromRecords({
[Bank = "Operating", Account = "1010", Currency = "USD"],
[Bank = "Payroll", Account = "1020", Currency = "USD"],
[Bank = "Savings", Account = "1030", Currency = "USD"]
})
in
BankAccounts
Each record is one row, with columns for Bank, Account and Currency. The field names do not need to be in any particular order.
Example: a second, different use
A table is cleaned by taking its rows as records, changing one field in each, and building a new table from them.
let
Source = Excel.CurrentWorkbook(){[Name = "Invoices"]}[Content],
Rows = Table.ToRecords(Source),
Rounded = List.Transform(Rows, each Record.TransformFields(_, {{"Amount", each Number.Round(_, 2)}})),
Result = Table.FromRecords(Rounded)
in
Result
Things to watch
- Every record should have the same fields. A record with a missing field needs the missingField option.
- The column names come from the field names, so spell them carefully.
- Values are not typed automatically. Set the types after building the table.
- Query folding: a table built from records does not fold to a database.
In business terms
Small lookup tables, such as bank account lists or entity tables, are easier to check when each row is written as a labelled record. The labels make the review simpler.