PowerQuery how to insert null values for missing fields using Table.FromRecords

Question

I am creating a custom connector for PowerBI using the PowerQuery SDK, it's purpose will be to read data from a JSON over HTTP API and transform it to a table view.

I am able to read the JSON response, the response is an array of JSON objects. I am using this PowerQuery function to read this result into a table:

    responseJson = Get30DaysHistory(),
    #"TestTable" = Table.FromRecords(responseJson)
in
   #"TestTable";

This works as long as all objects contain the same fields, but if the fields in a record differ than an error is thrown. I am trying to understand how to apply this function to my above code, I just want missing fields to have null values added:

https://docs.microsoft.com/en-us/powerquery-m/missingfield-usenull

here is a sample of the JSON output, for added context the field in question that is throwing an error with this data sample is "pageId" as it is present in the first record but not the second:

[
{
    "type": "page",
    "uri": "://test/url": {
        "name": "Chrome",
        "version": "69.0.3497",
        "os": "Windows"
    },
    "appId": -323232,
    "pageId": "nVTd9XqSEy1mPhaWGNlW9GWw5gU",
    "ts": 1538768760000,
    "lastTs": 1538768809248,
    "duration": 49248
},
{
    "type": "feature",
    "uri": "://test/url",
    "parsedUserAgent": {
        "name": "Chrome",
        "version": "69.0.3497",
        "os": "Windows"
    },
    "appId": -323232,
    "featureId": "diOobYRaY56e191qW2a7_pXGIzk",
    "ts": 1538768802811
},
{
    "type": "feature",
    "uri": "://test/url",
    "parsedUserAgent": {
        "name": "Chrome",
        "version": "69.0.3497",
        "os": "Windows"
    },
    "appId": -323232,
    "featureId": "_Net3QEcQ2mmg4z9wV95Gqkvx0k",
    "ts": 1538768803809
},
{
    "type": "page",
    "uri": "://test/url",
    "parsedUserAgent": {
        "name": "Chrome",
        "version": "69.0.3497",
        "os": "Windows"
    },
    "appId": -323232,
    "pageId": "cQIxuputqop_FYvfM9ehhd2CUvI",
    "ts": 1538768809248,
    "lastTs": 1538768840412,
    "duration": 31164
}

]

Updated with solution:

shared Testamun.Contents = (optional message as text) =>
let
    responseJson = Get30DaysHistory("testval"),
    headers =
     let
        allHeaders = List.Combine(List.Transform(responseJson, Record.FieldNames)),
        uniqueHeaders = List.Distinct(allHeaders)
     in
        uniqueHeaders,
        testTable = Table.FromRecords(responseJson, headers, MissingField.UseNull)
 in 
    testTable;

chillin chillin · Accepted Answer · 2018-12-17T20:55:59

Untested. Written on phone, sorry for bad indentation.

The solution/implementation may be something like:

responseJson = Get30DaysHistory(),
headers =
    let
        allHeaders = List.Combine(List.Transform(responseJSON, Record.FieldNames)),
        uniqueHeaders = List.Distinct(allHeaders)
    in
        uniqueHeaders,
testTable = Table.FromRecords(responseJson, headers, MissingField.UseNull)
    in
testTable

although this assumes responseJson is always of the structure shown in your question (i.e. a list of records).

Another way (though am not sure which one is more performant/efficient) might be:

responseJson = Get30DaysHistory(),
listOfTables = List.Transform(responseJSON, Record.ToTable),
testTable = Table.Combine(listOfTables)
    in
testTable

If neither of these work for you, let me know and I'll try to get access to a machine to test your sample JSON. Good luck.

PowerQuery how to insert null values for missing fields using Table.FromRecords

1 Answers