Excel Power Query from Json with missing fields

user_134076 0
Viewed 905

I am having a bit of trouble figuring out how to get around missing full row errors when having a list of json objects which have optional fields, like this example:

let
    Source = Json.Document("[
        { ""name"": ""Peter"", ""age"": 42, ""email"": ""something""},
        { ""name"": ""Peter"", ""age"": 42 }]"),
    Tabled = Table.FromRecords(Source)
in
    Tabled

That gives me a big fat error on the second row:

# | name  | age   | email     |
--------------------------------
1 | Peter |    42 | something |
2 | Error | Error | Error     |

Expression.Error: The field 'email' of the record wasn't found.
Details:
    name=Peter
    age=42

But I really just wan't it to "ignore" that, so I get something like:

# | name  | age   | email     |
--------------------------------
1 | Peter |    42 | something |
2 | Peter |    42 |           |
powerquery excel
2 Answers

Table.FromRecords() stop parse when missing fields, use Table.FromList() instead.

Try below, you can use expand menu genereate table2 code.

table2 code

let
    Source = Json.Document("[
        { ""name"": ""Peter"", ""age"": 42, ""email"": ""something""},
        { ""name"": ""Peter"", ""age"": 42 }]"),
    table1 = Table.FromList(Source,Splitter.SplitByNothing(),null,null,ExtraValues.Error),
    table2 = Table.ExpandRecordColumn(table1, "Column1", {"name", "age", "email"}, {"Column1.name", "Column1.age", "Column1.email"})
in
    table2

parsed table data

user_35688530
Related