Power Query: JSON files into tables

23 Apr, 2026

A lot of the time, I use JSON files as representations of two-dimensional tables, not as objects with embedded attributes and so on. The way that Power Query handles JSON is very generic and it's not the most convenient for parsing out "tables" - it always takes the same few steps to convert it to a table object, expand out all of the columns, and so on.

This function does that, in one go. It assumes that the JSON is a two-dimensional table. It won't handle embedded properties. It will also expand out all of the properties.

openJsonFlat = (filepath as text) as table => 
    let
        filepath = "file.json",
        Source = Json.Document(File.Contents(filepath)),
        
        tabulated = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        
        fields = Record.FieldNames(tabulated{0}[Column1]),

        expand = Table.ExpandRecordColumn(
            tabulated, 
            "Column1", 
            fields,
            fields
        )
    in
        expand