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
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:
//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.
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/
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.
Check out the News & Announcements to learn more.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.
Mark your calendars and join us for our next Power BI Dev Camp!
Check out our new Discover Your Career Path blog post series and get all the details.