Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jonnokc098
New Member

Dynamic Nested API Calls

Hello, 

 

I am wondering if it is possible to dynamically pull in data from a web API. 

 

Because of how the API is structure I am not able to pull in the data I need in one call. 

 

The top level of the API URL is structure like "http://url/api/rawData"

 

The top level is important because it includes the output of different versions available. The real data I need to access is located at the following URL level... "http://url/api/rawData/[LocationID]/[VersionID]/Data"

 

I know the location ID, but the VersionID is different and new versions are added all the time. 

 

Is there a way I can use the top level URL to get all the different LocationIDs and VersionIDs from within and then somehow loop through either all of them or say the 5 most recent (highest version ID numbers) to get the data from them? Or is it possible to somehow create a report that would allow the user to set which version(s) they are interested in and then initiate the call(s)?

 

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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-que...

 

Kind regards,

Imke Feldmann

http://www.thebiccountant.com/

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

10 REPLIES 10
ImkeF
Super User
Super User

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-que...

 

Kind regards,

Imke Feldmann

http://www.thebiccountant.com/

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

 

 

Anonymous
Not applicable

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

 

 

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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

 

 

 

Anonymous
Not applicable

Hi Imke,

 

Capture.PNG

 

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!

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/

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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="&[...""))

 

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!

Pls try this:

 

Json.Document(Web.Contents("http://awsonejefgdb1d.aws.jefco.com:10035/repositories/intropath/custom/intropath-twohops", [Query = [person=[Name.1]&" "&[Name.2]]]))

 

or this:

 

Json.Document(Web.Contents("http://awsonejefgdb1d.aws.jefco.com:10035/repositories/intropath/custom/intropath-twohops", [Query = [person=[Name.1]&"%20"&[Name.2]]]))

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

The first query worked..Genius!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.