Load all records and lists from json file

[MarianneElver]

I have a JSON file containing professional data with a number of records and lists, and I only seem to be able to load one table at a time, for each query. It looks a bit like this when loading the file: medias Record breadcrumbs Record basic Record educations List portfolio Record tags List All "Records" and "Lists" are marked in yellow and are clickable. If I open "Records" or "Lists", I see a new list with "Records" in yellow. Clicking on one of them finally let me see the data inside. But then, this is only a small corner of the whole file. Is there any way to unfold all Records and end up with several tables through one query?

[Cubist]

Since this is the top google result, and not updated, note Power BI has a default JSON document connector you can use that to import JSON data.

To use JSON from a file in Power BI

1. Choose Get Data > choose More... > choose "JSON" > choose the JSON file
2. You should see a "List" of "Records" in Power BI, don't panic
3. Select the list, choose Convert to Table
4. On the individual column headers, look for a splitter icon.  Choose split to expand fields.

[fyrworx]

I am new to Power BI, M and JSON, trying to figure this out. We have a pipedrive API and managed to connect to it. However the JSON file needs to be expanded, to do any sort of visuals?

My two questions are, why would you want one record per query? Not sure on the benefits of this? And, second question, being new to M, how would I manipulate the code to use the source from a web api? I have tried changing some pieces of the code:

let
//Define function taking two parameters - a table and an optional column number
Source = (Json.Document(Web.Contents("https://api.pipedrive.com/v1/deals?api_token=xxxxxxxxxxxxxxxxxxxxxxxx")) as table, optional ColumnNumber as number) =>

Any help will be very highly appreciated, thanks in advance.

[cwebb]

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

[MarianneElver]

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.

[cwebb]

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

[MarianneElver]

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