Power Query: Combining JSON with slightly different schemas

15 Jun, 2026

I recently had to take in input from a web service. The payload was a JSON array, but the objects inside the array were all of slightly different schema. Some properties existed on some objects, but not all.

I had to combine all of them into one table in Power Query. However, because Power Query infers the schema from just a sample of the first few records, it was erroring when it encountered the full data set.

The way to deal with this is to enumerate all of the possible properties in the array, explicitly expand on those properties, and then use the MissingField.UseNull option.

This is what it looks like:

let 
    // goes through the entire array and picks up all of the possible property names
    // List.Union makes it a distinct list
    allFields = List.Union(List.Transform(array, Record.FieldNames)),

    // a JSON array is a list of records
    lines = Table.FromRecords(array, allFields, MissingField.UseNull),
in
    lines

#powerquery