Downloads
Power Query MTable: build and convertIntermediate

Table.FromRecords

Builds a table from a list of records, where each record is one row with named fields.

Syntax
Table.FromRecords(records as list, optional type as nullable type, optional missingField as nullable number) as table

Returns: 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

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

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.

Related functions

All Power Query M functions · View as Markdown · M basics