Expand All Nested Columns Dynamically in Power Query M

Detects every record-type column in a table and expands all of them in one pass, without naming them individually.

API & Web DataAdvancedjsonnestedexpanddynamic

Power Query M code

let
    Source = YourTable,
    ExpandAll = List.Accumulate(
        List.Select(Table.ColumnNames(Source), each Type.Is(Value.Type(Table.Column(Source, _)), type record)),
        Source,
        (state, columnName) => Table.ExpandRecordColumn(state, columnName, Record.FieldNames(Table.Column(state, columnName){0}))
    )
in
    ExpandAll

Tidy JSON with the JSON Formatter

Notes

Runs once per nested column, so run it again if expanding reveals a second layer of nested records.

Requirements

How to use this snippet

  1. In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
  2. Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
  3. Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Expand All Nested Columns Dynamically.