Take this scenario.
Your writing a PowerBI report to pull data from a REST Api. The Api doesn't provide a search route which returns all the rows and data you require in one call. You can retrieve a list of ID's for the object type you require but a separate call is required to get the individual details per row. Or you may have an existing table with the IDs stored, and require additional details which is provided by an API call per row.
I was asked about this scenario by a colleague, and it is definitely able to be solved with PowerBI. I will urge caution though as if you have large recordset on the base table, and you plan to do an API call per row, that will be slow and result in a large number of calls to the API in a short duration which could be flagged as an attack by some monitoring systems.
Demo API
For this blog I have provided a demo NodeJS API that you can download and run in VSCode, but you could also use any public API, such as my SQL Versions API (details on that will follow at a later time).
To run the API, follow the instructions in
README.md.
Demo Data
The demo API has some static sample data. A collection of Course Instructors, which has a mapping to the Course collection and also a mapping to a Person collection. The complete sample data was taken from an old .Net app sample (
https://msdn.microsoft.com/library/bb399731(v=vs.100).aspx).
Here is a snippet of the structure and data:
CourseInstructor = [
{
"CourseID": "1045",
"PersonID": "5"
},
...
];
Course = [
{
"CourseID": "1045",
"Title": "Calculus",
"Credits": "4",
"DepartmentID": "7"
},
...
];
Person = [
{
"PersonID": "1",
"LastName": "Abercrombie",
"FirstName": "Kim",
"HireDate": "1995-03-11 00:00:00.000",
"EnrollmentDate": null
},
...
];
Lets get start
1. Get the base source
For this walk through lets start with the CourseInstructor data. This is a list of CoursID's and PersonIDs, but no other details. We will then use the individual API calls to build the completed table.
In the Demo API the API route is http://localhost:3000/api/courseinstructor.
In PowerBI select
Get Data
In the form type "web" to narrow the results and locate the
In the next screen enter the API route (e.g. http://localhost:3000/api/courseinstructor). This should be a HTTPS uri for security.
If your API needs Authorization, or a Token, then you should set the appropriate headers. I have an example in one completed solution at the end.
2. Convert to table
The results will first be shown in a list as PowerBI will use the Json.Document() function to retrieve the data.
Click the "To Table" button in the menu ribbon.
In the To Table dialog you should be able to leave the defaults. However if the API JSON data isn't very structured, in that each document has different elements and size, then you may need to experiment with the error handling options.
3. Expand the columns
The results will now be a typical referenced table which you need to expand the columns on. Click on the expand columns button.
Select the required columns. This will depend on your API data, in the demo data cases we want both columns as we will add additional API routes to get that data.
My tip is to untick the "Use original column name as prefix" option. Or if you want to use that make sure you have renamed the column to a meaningful name prior to this step. You can also always add a meaningful name by editing the step settings afterwards, but before you proceed with other steps.
The results should now be expanded as required.
4. Expand the data with additional API calls per row
Now for the fun part. We now have rows of data which contain our ID(s). To expand this data we need to make additional API calls for each row. To achieve this we add a custom column, and dynamically build the API Uri.
Click the Add Column tab in the ribbon, and click Custom Column.
Give the column a meaning full name (e.g. CourseCollection).
Here is the tricky part, we have to dynamically build the API Uri. To do this we can use the Text.Combine() function to concatenate the uri and add the column value where the parameter should be. For our demo data the completed Uri will be http://localhost:3000/course/[CourseID]
Adjust the following expression as required:
= Json.Document(
Web.Contents(
Text.Combine({"http://localhost:3000/api/course/",[CourseID]})
)
)
If you need to provide headers for authorization then use the parameters of the web.Contents() function, for example:
= Json.Document(
Web.Contents(
Text.Combine({"http://localhost:3000/api/course/",[CourseID]}), [Headers=[#"Z-API-Token"=""]])))
NOTE: If prompted regarding the data privacy, click continue and set as appropriate.
IMPORTANT: The process of updating the table may take some time depending on the number of rows and speed of the API.
The results should now include the custom column (e.g. CourseCollection).
This table needs to be expanded, the same as earlier by clicking on the expand columns button and then select the desired columns to include. In my demo data case, I am only interested in the Title column.
Your table should now include the data from the additional API call.
Continue to model the data as you require, or additional API calls. In the case of the demo data we will repeat these steps but for the Person column and the API Uri route http://localhost:3000/person/[PersonID]
The completed table will look like:
The query steps will be similar to the following:
The complete M Query for creating the query will be similar to this. This is available from the Advanced Editor of a query, and is a working query for the demo API.
let
Source = Json.Document(Web.Contents("http://localhost:3000/api/courseinstructor")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"CourseID", "PersonID"}, {"CourseID", "PersonID"}),
#"Added CourseCollection" = Table.AddColumn(#"Expanded Column1", "CourseCollection", each Json.Document(
Web.Contents(
Text.Combine({"http://localhost:3000/api/course/",[CourseID]})
)
)),
#"Expanded CourseCollection" = Table.ExpandRecordColumn(#"Added CourseCollection", "CourseCollection", {"Title"}, {"Title"}),
#"Added PersonCollection" = Table.AddColumn(#"Expanded CourseCollection", "PersonCollection", each Json.Document(
Web.Contents(
Text.Combine({"http://localhost:3000/api/person/",[PersonID]})
)
)),
#"Expanded PersonCollection" = Table.ExpandRecordColumn(#"Added PersonCollection", "PersonCollection", {"LastName", "FirstName"}, {"LastName", "FirstName"})
in
#"Expanded PersonCollection"
Demo Files and Source
I have provided a completed PBIX in the github repository.
Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.
