cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChrisIntl Frequent Visitor
Frequent Visitor

Looping through Parameters for API calling

Hello, 

 

I am trying to call weather API data for specific locations but cannot find a way to perforom multiple calls for a single query. I would like for my query to step (loop) through the data and return a full response. 

 

1. I begin with my name and location data. Aiport IATA codes and Lat/Long coordinates (both text)2.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2. I then create a parameter for the custom URL/API query. I've tried using the 'suggested values' option as query, but hit the same issue with BI only referencing one value in a query.

1.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3. Query from web with custom URL, at this point I would like my parameter to Loop through the many values of the Coordinates I had inputted as list. 

 

 

 

 

 

 

 

 

 

 

At this point I recieve a table with appropriate data for One location, this table updates ifI  I change the value of my parameter, but my intention is to call all the locations. 

 

Any help would be great. I have already looked into some M-query possabilities. However, if i can fix this within BI it would easier. 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: Looping through Parameters for API calling

Hi @ChrisIntl,

 

That's because the return type of Web and Json are different. You can't replace the function directly. I would suggest you create a single query and copy the code to the function [url]. Please refer to the snapshot below.

Looping-through-Parameters-for-API-calling3

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ChrisIntl Frequent Visitor
Frequent Visitor

Re: Looping through Parameters for API calling

1.JPG

 

Literally overcomplicated it, this function once invoked will return a record of data for every URL in the Loop, storing them in the custom column. Posting for future solutions. @v-jiascu-msft thank you again Sir for your time and help!

11 REPLIES 11
ChrisIntl Frequent Visitor
Frequent Visitor

Re: Looping through Parameters for API calling

3.PNGDid not load on orignal post (API query)

v-jiascu-msft Super Contributor
Super Contributor

Re: Looping through Parameters for API calling

Hi @ChrisIntl,

 

Please download the demo from the attachment. I would suggest you create a function to call the APIs then combine the results together. 

(url as text) =>
let
    Source = Web.Page(Web.Contents(url)),
    Data0 = Source{0}[Data]
in
    Data0

 

Looping-through-Parameters-for-API-calling

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ChrisIntl Frequent Visitor
Frequent Visitor

Re: Looping through Parameters for API calling

hello @v-jiascu-msft

 

I think that is the right track for sure, I am pulling a JSON object so the code reflects the following 1.PNG

 

 

 

I then create a custom column by invoking the above function.  

2.PNG

 

 

However, once I try to call the data from the API i'm given a column of errors with the following: 

4.PNG

 

 

Is there a way to trouble-shoot around this, it is defiently reaching and calling from the API. I'm worried that BI cannot interpret the data. Thank you again for your help. 

v-jiascu-msft Super Contributor
Super Contributor

Re: Looping through Parameters for API calling

Hi @ChrisIntl,

 

That's because the return type of Web and Json are different. You can't replace the function directly. I would suggest you create a single query and copy the code to the function [url]. Please refer to the snapshot below.

Looping-through-Parameters-for-API-calling3

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ChrisIntl Frequent Visitor
Frequent Visitor

Re: Looping through Parameters for API calling

@v-jiascu-msft/Dale, 

 

I just want to say thank you in advance for your patience, this is my very first BI project. 

 

1. I have updated my function to reflect the correct data types, and reference them in a variable {Docs}.

1.1.PNG1.2.PNG

 

2. I then invoke the custom function to call a list for each row of all 46 data types deminshioned, and get the option to drill down into that list: 

2.PNG3.PNG

3. However, even though the values are in the cells, BI will send me the following expression error (regardless of data type (number, text, date, etc.):

 

Expression.Error: We cannot apply field access to the type Number*.
Details:
Value=1900*
Key=Docs

 

* = values and data types change depending on called info. 

 

I'm assuming the crux of the issue is the Field Access given by BI. how do I work around this? Thank you again Dale. 

v-jiascu-msft Super Contributor
Super Contributor

Re: Looping through Parameters for API calling

It's my pleasure, @ChrisIntl,

 

We succeed to call the APIs when we get the lists. 

1. What's the value of the list? You can click the blank area of that cell.

Looping-through-Parameters-for-API-calling4

2. Did you expand the lists? Why only one column left?

Looping-through-Parameters-for-API-calling5

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ChrisIntl Frequent Visitor
Frequent Visitor

Re: Looping through Parameters for API calling

@v-jiascu-msft, so yes. the API is called and a list is created. There should be 46 responses per location (i.e. coordinates X,Y respond with : wind speed, temp, cloud cover, etc. ). It's storing the values for these responses but showing them as an error column in the list.

1.PNG2.PNG

 

From List for location 1, i get the above error column, which has the data but in an error state. 

 

If I expand, I just recieve a table with an error column with the expanded values, below:

3.PNG

v-jiascu-msft Super Contributor
Super Contributor

Re: Looping through Parameters for API calling

Hi @ChrisIntl,

 

We have to troubleshoot the code with data. I have some suggestions. 

Do all the queries have the same structure? I think you did too much in the function. Please reduce the code and keep it simple and try again. Please refer to my previous posts. We can modify the function after it works. 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ChrisIntl Frequent Visitor
Frequent Visitor

Re: Looping through Parameters for API calling

@v-jiascu-msft, I cut back the code to call 2 data points. however i'm having the same issue:`.PNGcode in condensed function2.PNGdata set after invoke3.PNGdrill down error

 

 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 363 members 4,141 guests
Please welcome our newest community members: