Forum Discussion
Rob_Bolton
Mar 11, 2024Copper Contributor
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:
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
Sort By
- Rob_BoltonCopper Contributor
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.
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.