Forum Discussion
Load all records and lists from json file
One query can only return one table, unfortunately. The easiest thing to do here is to create one query that connects to your JSON file, and then instead of clicking on each record or list right-click on them and select "Add As New Query". This will create a new query for each of the records or lists, but with just one connection back to the original JSON file, so it's easy to point Power BI at a different file if you need to.
If you want all the data from the JSON file in a single table in a single query, you can click the "Expand" icon (which looks like two arrows, one pointing left, one pointing right) in the column header. You can also write some M code to automatically expand all the expandable columns in a table, similar to what I wrote about here: https://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/
HTH,
Chris
- Thanks a lot, the "Add as new query" let me add all the tables in one query! At least for this file and got me one big step forward. Unfortunately there is no "Expand" icon in either column header, but will some different M code possibly also give me the possibility of expanding all the Records and Lists with the same result as in your blog? My aim is to build a small content pack for a bunch of JSON files structured the same way.
I guess the question here is do you want one big table with all the data from your JSON file in, or do you want multiple tables, one for each record or table from the JSON file? The "Add As New Query" creates multiple queries, and therefore multiple tables because 1 query=1 table. This is usually what you want to do, because a JSON file often contains many different types of data that you want to separate out into separate tables in your Power BI model.
The M code example I showed, and any solution that you could write for your JSON file, would end up with one big table and one query where all the data was mixed up together - not something you usually want to do. Unfortunately there is no way in M code to automatically create separate, new queries from each of the tables or records in a JSON document in the way you are able to do with the "Add As New Query" option.
Chris
- I agree. One big table in this example would be a mess and not the solution I'm heading for. As M won't help me in creating separate new queries, I guess the optimal solution is to compile the JSON file differently, perhaps with the one big nicely structured table as a solution. Thanks for your great answers! Marianne
