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/
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