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
Anonymous
Not applicable

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

Hi @Anonymous,

 

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.

View solution in original post

Anonymous
Not applicable

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!

View solution in original post

11 REPLIES 11
v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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. 

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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

It's my pleasure, @Anonymous,

 

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.
Anonymous
Not applicable

@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

Hi @Anonymous,

 

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.
Anonymous
Not applicable

@v-jiascu-msft, I cut back the code to call 2 data points. however i'm having the same issue:code in condensed functioncode in condensed functiondata set after invokedata set after invokedrill down errordrill down error

 

 

Hi @Anonymous,

 

I'm almost sure it's something wrong with your function "url3". I'm afraid you can't extract data like that. Can you share a dummy sample that can show up the structure of the data? Please mask the sensitive parts first.

 

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.
Anonymous
Not applicable

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!

Anonymous
Not applicable

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

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.