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/
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
Community ChampionIt 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!
- ImkeF
Sure: You add a column with this formula:
"http://awsonejefgdb1d.aws.jefco.com:10035/repositories/intropath/custom/intropath-twohops?person=" & Text.BetweenDelimiters([Column1], """", """")
Then remove the rows with errors.
If you want to refresh this in the service, you have to adjust you code a bit like described here:
http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
Hi Imke,
This is my dynamic query. I am trying to refresh this in Power BI service.
Json.Document(Web.Contents("http://awsonejefgdb1d.aws.jefco.com:10035/repositories/intropath/custom/intropath-twohops?person="&[Name.1]&"%20"&[Name.2]&""))
As per your article this is what I did. Its not working. Not sure where I got wrong.
Json.Document(Web.Contents("http://awsonejefgdb1d.aws.jefco.com:10035/repositories/intropath/custom/intropath-twohops?",
[Query = [person=""&[Name.1]&"%20"&[Name.2]&""]])))Many thanks!
Hi @imke
I managed to get the list from the first API call and created a parameter with the list of values. Then created a function to invoke the parameters.
Then created a invoke custom function which returned the data in the table format.
But when I expand the table and apply the changes, I get this error:
[Expression.Error] We cannot convert the value null to type Text.
Please let me know what needs to be changed.
Thanks