Power Query - JSON Schema - Include elements not present in first (2) expanded objects

[Rob_Bolton]

Hi,

I imagine this is an issue of my inexperience, but I can't find any guidance online, so would be extremely grateful for any help you might be able to offer.

I've noticed that when importing JSON using the Power Query Editor, schema appears to be set according to elements present in the first two expanded objects.

Is there any way to overcome this, and present 'verbose' schema - including elements which are present in subsequent objects which were not in the first two?

Illustrative examples below. 

Rob.

Example 1 - NOK
Additional element in 'record' is present only in the third object, not presented when expanding.

Source data:

{
    "reference": "7b3cf0f1aa101313",
    "recordCount": 3,
    "records": [{
        "recordReference": "2479",
        "recordPriorityIndex": 60,
        "record": [{
            "id": "item001",
            "title": "Item 001"
        }]
    },{
        "recordReference": "2478",
        "recordPriorityIndex": 40,
        "record": [{
            "id": "item002",
            "title": "Item 002"
        }]
    },{
        "recordReference": "2477",
        "recordPriorityIndex": 20,
        "record": [{
            "id": "item003",
            "title": "Item 003",
            "metadata": {
                "extraField": "Additional Info"
            }
        }]
    }]
}

Power Query Editor:

Example 2 - OK

Object with additional element in 'record' is moved to second position. Without changing the steps, additional element is available when expanding.

Source data:

{
    "reference": "7b3cf0f1aa101313",
    "recordCount": 3,
    "records": [{
        "recordReference": "2479",
        "recordPriorityIndex": 60,
        "record": [{
            "id": "item001",
            "title": "Item 001"
        }]
    },{
        "recordReference": "2477",
        "recordPriorityIndex": 20,
        "record": [{
            "id": "item003",
            "title": "Item 003",
            "metadata": {
                "extraField": "Additional Info"
            }
        }]
    },{
        "recordReference": "2478",
        "recordPriorityIndex": 40,
        "record": [{
            "id": "item002",
            "title": "Item 002"
        }]
    }]
}

Power Query Editor:

[#2]

@Rob_Bolton 

It works in my case, please check attached.

[#3]

@Sergei Baklan 

That's very interesting. Thank you for making the test.

I imagine I'm probably doing something wrong in the way I build the query, but if that is the case, I'd like to learn how. Having said that, I am following the same steps with both variations of the source data, and get different results.

Unfortunately, I'm not able to enable the data sources for external files to study the way your query was built. 

In case it reveals an issue with my process, a description of my steps is below. (Also: if our steps were similar, I wonder if your test was with Excel for Windows or Mac?)

Thanks again for your help,

Rob.

(1) Select source file.

(2) Click ['List'] link in initial view.

(3) At view with three [Record] rows, click toolbar 'To table' button. 

(4) Accept default delimiter/extra columns settings.

(5) Expand column, selecting only the 'record' element.

(6) Expand column [no dialog].

(7) Expand column, all elements selected.

As noted, with the Example 1 source data, the result is only table columns for 'id' and 'title'. With the Example 2 source data, there is an additional column for 'metadata' which I can expand until reaching values.

[#4 · Accepted solution]

@Rob_Bolton 

I did that taking the json from the text in the table, not from external file, but it shall be no difference. That is on Windows Desktop app

Now repeated the same from the file. Steps are

Get Data->From file->From JSON

Click on Lists in records

Convert List to Table

Expand column

Expand record column with Lists. Record appears as value

Expand it again and here click on Load more

With that it shows metadata field

Finally expand metadata

which gives

Above steps are in Sample query in attached file, you shall only change the file path on the first step to repeat.

[#5]

@Sergei Baklan 

Thank you very much again for your assistance.

I think we've found the issue - Excel for Mac does not have the 'load more' option when expanding the column. 

Since my previous reply, I sent the two example source data files to a colleague who uses Widows, and their observations were exactly as yours.

I wonder if there's some way to raise this as a bug/enhancement request?

Sincere thanks again for your help,

Rob.

[#6]

@Rob_Bolton , you are welcome.

Never worked on Mac, on Windows you may within Excel go to Help->Feedback->Report a problem.

Or even better is to post an issue on feedback portal Excel · Community (microsoft.com)