Table.ExpandRecordColumn
Opens a column of records, such as the details inside a JSON file, and turns the chosen fields into columns.
Table.ExpandRecordColumn(table as table, column as text, fieldNames as list, optional newColumnNames as nullable list) as tableReturns: 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
- table: the table with the record column. Required.
- column: the name of the record column, in quotes. Required.
- fieldNames: a list of the field names to bring out, in braces. Required.
- newColumnNames: optional new names for the columns, in the same order as the fields. Useful to avoid clashes.
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
- A record may not have every field you name. Check the result for blanks after expanding.
- A field name must match the record exactly, including capitals.
- Expanding a deeply nested record takes several steps. Expand one level at a time and check each result.
- Query folding: record expansion does not fold to SQL. It runs on the loaded data.
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.