Lost with REST API

[sniffer]

I've read the forum pages here and just cannot seem to get started correctly with REST APIs.  I am certainly a beginner and being humbled by trying to learn this.

I'm trying to download 10 day weather forecasts from Visual Crossing's site (https://www.visualcrossing.com/) for locations (longitude/latitude) that I have in a separate Sharepoint list.  The list is updated daily since I've applied a filter to only list locations where projects will be started within the next 14 days.  So, I'd like to create a visual table that shows me the upcoming weather forecast for my upcoming projects.

I'm imported the table into Power BI and have tried to "Get Data" from Visual Crossing (VC).  I can retreieve data through Power BI's web API but am unable to setup the system to pull multiple locations from VC based on the locations specified in my table. I'm just lost how to get this all setup and have been unable to find basic step-by-step instructions to do this.  Here are some basic questions to help my understand the systems and their interactions...

Ultimately what I'm after is creating a table in PowerBI similar to what is shown here with location data provided by my table.  

I really appreciate any one's time to help coach me through this.  Thank you.

[lbendlin]

That weather API does not have a free option so it will be difficult to help you with testing.  What have you tried so far? Can you show a sanitized version of your Power Query code?

[sniffer]

Thanks for the help.  I have obtained free data from the site after setting up an account.  I've followed the directions explained here... https://www.visualcrossing.com/resources/documentation/weather-data-tutorials/how-to-load-weather-da...

There is also a good article here:  https://www.visualcrossing.com/resources/documentation/weather-api/weather-api-documentation/

So from this code I was able to access the data from the site and pull it into PowerBI.  But, you'll note that the data consists of locations that I had to input into the VC site. 

The code is below...

let
Source = Csv.Document(Web.Contents("https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/weatherdata/forecast?aggr... API KEY]&dataElements=default&sourceDatasourceTable=%7B%22name%22%3A%22WxSourceData1%22%2C%22id%22%3A%22WxSourceData1%22%2C%22isPrimary%22%3Atrue%2C%22analyzeLevels%22%3Afalse%2C%22rowDateTimeColumnIndex%22%3A-1%2C%22defaultDateTimeFormat%22%3A%22yyyy-M-d'T'H%3Am%3As%22%2C%22columns%22%3A%5B%7B%22isKey%22%3Atrue%2C%22name%22%3A%22ID%22%2C%22id%22%3A%22id%22%2C%22type%22%3A%22string%22%7D%2C%7B%22isKey%22%3Afalse%2C%22name%22%3A%22Name%22%2C%22id%22%3A%22name%22%2C%22type%22%3A%22string%22%7D%2C%7B%22isKey%22%3Afalse%2C%22name%22%3A%22Address%22%2C%22id%22%3A%22address%22%2C%22type%22%3A%22string%22%7D%2C%7B%22isKey%22%3Afalse%2C%22name%22%3A%22Date%20time%22%2C%22id%22%3A%22datetime%22%2C%22type%22%3A%22string%22%7D%5D%2C%22rows%22%3A%5B%5B%22test%201%22%2C%22test%201%22%2C%22-89.86828337%2C%2044.42401466%22%2C%228%2F13%2F2021%22%5D%2C%5B%22test%202%22%2C%22test%202%22%2C%22-89.86828337%2C%2044.42401466%22%2C%228%2F20%2F2021%22%5D%2C%5B%22test%203%22%2C%22test%203%22%2C%22-88.9103032%2C%2043.95782807%22%2C%228%2F18%2F2021%22%5D%2C%5B%22test%204%22%2C%22test%204%22%2C%22-88.06473964%2C%2043.18668316%22%2C%228%2F12%2F2021%22%5D%2C%5B%22test%205%22%2C%22test%205%22%2C%22-88.06473964%2C%2043.18668316%22%2C%228%2F20%2F2021%22%5D%5D%2C%22layerDataContext%22%3A%7B%22FieldJoins%22%3A%22%7B%7D%22%2C%22useGeoJsonGeometry%22%3A%22true%22%2C%22attributeId%22%3A%22id%22%2C%22shapeType%22%3A%221%22%2C%22onDemandTileGeneration%22%3A%22true%22%2C%22joinLayerColumns%22%3A%22address%22%2C%22contextType%22%3A%226%22%2C%22attributeName%22%3A%22%22%2C%22FieldValues%22%3A%22%7B%7D%22%2C%22addressFields%22%3A%22address%22%7D%7D"),[Delimiter=",", Columns=17, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Date time", type date}, {"Maximum Temperature", type number}, {"Minimum Temperature", type number}, {"Temperature", type number}, {"Wind Chill", type number}, {"Heat Index", type text}, {"Chance Precipitation (%)", type number}, {"Precipitation", type number}, {"Snow", type number}, {"Snow Depth", type number}, {"Wind Speed", type number}, {"Wind Gust", type number}, {"Visibility", type number}, {"Cloud Cover", type number}, {"Relative Humidity", type number}, {"Conditions", type text}})
in
#"Changed Type"

------------------------

So I can successfully import the data into the model but I have to manually enter the location data into their site.  I would like to push the location data to the weather app and have the system provide weather data based on these locations.  

Again, thanks for the help.

[lbendlin]

Here is the usual approach for that.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc1LDoAgEAPQu7DGYX4O5SyE+19DDMboSpdNXtve04ZGCCjMasrJnVydxSNmQhEryipp5E+q/KSgJmxsekqjttdZ5LpG8ZYcXq2FLyqIgMn1r7/p/T8O", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Longitude = _t, Latitude = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Raw", each Web.Contents("https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/" & [Latitude] & "%2C" & [Longitude] & "/" & Date.ToText([Date],"yyyy-M-d") & "?unitGroup=us&key=" & APIKey))
in
    #"Added Custom"

That results in 

and you can then JSON parse the binary blobs to pick the information you want to extract for each location and date.

NOTES:

1. this is a dynamic URL so cannot easily be used in the Power BI Service

2. The free user has a daily budget of 1000 points. One API query costs 24 points.  You'll run out of road quickly when testing this.

[sniffer]

I think I'm making progress since I was able to copy the code you offered into the advanced editor and was able to obtain this information...  

You didn't say I hat to hit fx on the formula bar to receive the table you had in your example.  🙂  Here's what I got...

The data I have for which I'd like to look up the weather data is found in a table titled "Project Locations" with two field "Latitude" and "Longitude".  Since the weather report will provide the next 15 days, I do not need to use the Start Date field I previously mentioned.  

So, how does this code reference the lat/long fields in my table, and requests weather data for these locations?  Is there another step I'm missing?  

Much thanks!

[lbendlin]

Create a sample query in the API web page without the dates, and then reconstruct the URL in the way I showed in my sample code. Don't forget to include the API key.