Forum Discussion
Power Query - JSON Schema - Include elements not present in first (2) expanded objects
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.
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.
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.
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)
