Forum Discussion

Rob_Bolton's avatar
Rob_Bolton
Copper Contributor
Mar 11, 2024
Solved

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

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:

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

    • Rob_Bolton's avatar
      Rob_Bolton
      Copper Contributor

      SergeiBaklan 

      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.

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP

        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.

         

         

         

         

Resources