Today I’ll show you a very useful technique how to deal with a JSON record that contains a wild mixture of different elements like this:
If you click on one of the expandable elements, their content will be shown, but you’ll loose all the “surrounding” information (metadata) that is visible now. This is often an issue, regardless if you want to create multiple tables from it to build a star-schema or just need a handful of fields or a denormalized table. But with a little help from M, you’re good to go:
Table.FromRecords( { MyJsonRecord } )Will returns this:
With this move, every expansion of one of the expandable elements will keep the existing data in place:
Create one big flat table
Simply expand one element after each other to create a denormalized table
Create star schema
For multiple tables, keep this query and reference it to create you (sub-)tables. Always keep the Id-column as the key (!) to combine all the tables in your data model later. (Provided you use this in a function for multiple entities/series)
Best is to play with it, so just past this code into the advanced editor:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| (URL as text) => | |
| let | |
| MyJsonRecord = Json.Document(Web.Contents(URL)), | |
| MyJsonTable= Table.FromRecords( { MyJsonRecord } ) | |
| in | |
| MyJsonTable |
If your JSON-record has a different structure with “just” header and data in different fields, this technique will be more suitable for you: http://www.thebiccountant.com/2016/04/23/universal-json-opener-for-quandl/
Enjoy & stay queryious 🙂
This was useful – thank you. However, still I am unable to figure out how to do this with an extremely large json files containing multiple entities and multiple nestings. Is there any way to create separate tables for each json object without having to manually expand each node and save each as a dedicated query? How to include the identifier in all tables so they can be related?
Likewise, is there any way to expand-all nodes and create one single flat table without having to expand every node manually? Column headers would need to include parent node name to preserve context, especially if two nodes shared a similar attribute name (e.g. company.name vs customer.name rather than just calling both columns ‘name’). Furthermore, trying to expand for flattening is impossible in PowerQuery UI because you can’t expand more than one node at a time. Every time I expand one node it replaces the previous node’s expansion. It would again take dozens of dedicated queries and then an append query to link them all together into one set of columns.
Hi Robert,
yes, there is a lot more to explore when handling JSON. Currently not sure when I will cover this on my blog though.
With regards to the expansion, maybe this will help: https://www.thebiccountant.com/2017/08/30/how-to-open-a-complex-json-record-in-power-bi-and-power-query/
Great strategy. I was looking for something like this a while ago and now I just stumble across your blog. I can go back now and do things right. Thanks!
Great to hear!