Forum Discussion
Lost with REST API
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-data-into-information-builders-ibi-webfocus-using-the-rest-api/
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?aggregateHours=24&combinationMethod=aggregate&sendAsDatasource=true&contentType=csv&unitGroup=us&locationMode=array&key=[MY 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.
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.
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!
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.