Downloads
Power Query MTable: combine and joinIntermediate

Table.ExpandRecordColumn

Opens a column of records, such as the details inside a JSON file, and turns the chosen fields into columns.

Syntax
Table.ExpandRecordColumn(table as table, column as text, fieldNames as list, optional newColumnNames as nullable list) as table

Returns: A table with the chosen fields as columns.

What it does

Some sources, such as a JSON file or a web service, put several values in a single cell as a record. A record is a small set of named fields. Table.ExpandRecordColumn takes the fields you name out of that record and makes each one a column. It is the step you use after Json.Document or Web.Contents to get at the values.

Syntax and arguments

Example

A payment export from an API holds a Details column, and each cell is a record with a currency and a fee. The two fields become columns of their own.

let
    Source = Excel.CurrentWorkbook(){[Name = "PaymentDetails"]}[Content],
    Expanded = Table.ExpandRecordColumn(Source, "Details", {"Currency", "Fee"}, {"Currency", "Fee"})
in
    Expanded

Each row gets its own Currency and Fee columns, and the Details column is removed.

Things to watch

In business terms

Bank, payment and tax APIs return detailed information inside nested records. Expanding the fields the close process needs turns that response into a table an accountant can check and reconcile.

Related functions

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