Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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. 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.
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.
Solved! Go to Solution.
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.
Best Regards,
Dale
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!
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
Best Regards,
Dale
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
I then create a custom column by invoking the above function.
However, once I try to call the data from the API i'm given a column of errors with the following:
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.
Best Regards,
Dale
@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}.
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:
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.
2. Did you expand the lists? Why only one column left?
Best Regards,
Dale
@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.
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:
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
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
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!
Did not load on orignal post (API query)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |