Excel Power Query from Json with missing fields

Question

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 |           |

Answer 1

Ok so managed to find a solution that is ok for now in my case, although i think a better one could certainly be made as it is a bit crude...

let
    Source = Json.Document("[
        { ""name"": ""Peter"", ""age"": 42, ""email"": ""something""},
        { ""name"": ""Peter"", ""age"": 42 }]"),
    Transformed = List.Transform(Source, each Record.TransformFields(_, {
        { "email", Text.Trim },
        { "name", Text.Trim },
        { "age", Int64.From }
    }, MissingField.UseNull)),
    Tabled = Table.FromRecords(Transformed)
in
    Tabled

Which yields

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

(null goes away when applied to a sheet)

Ideally something that would require far less "code" would be ideal, but for now this will do.

If anyone has any better solutions feel free to share >.<

Answer 2

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

Try below, you can use expand menu genereate 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