Forum Discussion
Dynamic Nested API Calls
Yes, that's all possible.
You transform the record or whatever is returned from the first call to a table, sort and filter to your desired selections and then add a column to that table that makes the subsequent calls to pull the detail-data. This will return a column with the results from all calls that you can simply expand. No loops required.
If your first result is a (Json) record, you might find this article helpful to get you started: http://www.thebiccountant.com/2017/08/30/how-to-open-a-complex-json-record-in-power-bi-and-power-query/
Kind regards,
Imke Feldmann
http://www.thebiccountant.com/
Community ChampionYes, that's all possible.
You transform the record or whatever is returned from the first call to a table, sort and filter to your desired selections and then add a column to that table that makes the subsequent calls to pull the detail-data. This will return a column with the results from all calls that you can simply expand. No loops required.
If your first result is a (Json) record, you might find this article helpful to get you started: http://www.thebiccountant.com/2017/08/30/how-to-open-a-complex-json-record-in-power-bi-and-power-query/
Kind regards,
Imke Feldmann
http://www.thebiccountant.com/
Hi @imke,
Can you please help me understand how to make the API calls dynamically from the list obtained after the first API call.
Thanks,
Yakshana
- ImkeF
It works as I said above: Transform the list into a table and add a column, referencing the relevant items from your list (that's now your first column).
Provided your list contains the key elements that you need for further calls.
If you're struggling with that, please paste your code and pic from your list.
Hi Imke,
This is how the JSON looks like and this is my API query: http://aws.com/repositories/intropath/custom/intropath-twohops?person=Justin%20Lipton
I am trying to make the name in the end dynamic. Any idea what to do from here on ?
Many thanks!
Hello ImkeF ,
Firstly thanks for your reponse on this thread.
I am aware this is an old discussion, but hoping that you can guide me in right direction.We are building a Power BI connector to extract data from one of our systems that expose data via Rest API.
API structure as follows:
baseURI: https://api.myapp.com/api/rest/
API end point for document list: https://api.myapp.com/api/rest/documents/ => (returns a list of docIDs)
API end point for document users: https://api.myapp.com/api/rest/documents/docID/users
After getting response from List API, I am try to add a new column by invoking a custom function that accepts docID as parameter and then invoke document users API.
I get the following error: Expression.Error: Access to the resource is forbidden.
Tried changing the Privacy levels at Data source settings but no luck!
It works fine when I try to hard code any docID instead of passing as a parameter. Kindly find my code below and request you to help. Thanks!
Query1:
let
DefaultRequestHeaders = [
#"Accept" = "application/json", // column name and values only
#"Authorization" = "****",
#"client_id" = "****",
#"client_secret" = "****",
],source = Web.Contents("https://api.myapp.com/api/rest/", [ RelativePath = "documents", Headers = DefaultRequestHeaders ]),
json = Json.Document(source),
docList = json[docList],
#"Converted to Table" = Table.FromList(docList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id"}, {"id"}),
#"Invoked Custom Function" = Table.AddColumn(#"Expanded Column1", "Query2", each Query2([id]))in
#"Invoked Custom Function"
Query2:
let
GetUsers = (docId as text) as table =>
let
DefaultRequestHeaders = [
#"Accept" = "application/json", // column name and values only
#"Authorization" = "****",
#"client_id" = "****",
#"client_secret" = "****",
],
source = Web.Contents("https://api.myapp.com/api/rest/", [ RelativePath = "documents/"&docId&"/users", Headers = DefaultRequestHeaders ]),
json = Json.Document(source),
#"Converted to Table" = Record.ToTable(json),
#"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value")
in
#"Pivoted Column"
in
GetUsers
