Power Query: Easy way to add columns for changing JSON data source

user_270326 0
Viewed 421

I'm fairly new with PQ, so there's likely an easy answer to this one.

Say I'm loading up rows of data from a local JSON file. Then I expand it using what I assume is a fairly standard set of steps:

report = Json.Document(File.Contents("path\to\my\file")),
#"Converted to Table" = Table.FromList(report, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", { /* a long list of properties from my json */},
// a long list of statements that operate on various columns: reorder, cast to type, rename, add conditional columns, etc.

Every sprint or so, a couple of new properties get added to my JSON, and I need to incorporate them into the report and operate on them, as well. Is there a straightforward way of doing it that doesn't involve me going back to that Table.ExpandRecordColumn and adding them into the sets of bracketed property names every time, then manually renaming and reordering them? I'd be happiest if there were a way to do this using the UI, but if there's a way using the Advanced Editor that's more straightforward than the approach I just described, I'll take it. The way I'm doing it doesn't seem all that efficient.

Thank you.

powerbi powerquery
1 Answers

It seems like you have a list of records, which you want to transform into a table.

If all records (in your list) contain the exact same field names as each other, then you can get away with something simple like:

let
    // Instead of File.Contents, I've hard coded some JSON for example purposes.
    report = Json.Document("[{""a"":1,""b"":2,""c"":1},{""a"":4,""b"":1,""c"":5},{""a"":5,""b"":3,""c"":1}]"),
    initialTable = Table.FromRecords(report)
in
    initialTable

(Here, it seems more appropriate to use Table.FromRecords than using Table.FromList in conjunction with Table.ExpandRecordColumn.)

However, if your records have different fields but you still want to expand all fields present across the entire list, then you can dynamically build an exhaustive list of field names:

let
    // Instead of File.Contents, I've hard coded some JSON for example purposes.
    report = Json.Document("[{""a"":1,""b"":2},{""b"":1,""c"":5},{""a"":5,""c"":1}]"),
    fieldsToExpand = List.Distinct(
        List.Combine(List.Transform(report, Record.FieldNames))
    ),
    initialTable = Table.FromRecords(report, fieldsToExpand, MissingField.UseNull)
in
    initialTable

(The MissingField.UseNull tells Power Query to use null where a record did not contain a certain field. Otherwise, I believe it throws an error relating to the missing field.)

user_88117780
Related