Posted on by Visual Crossing

Automate recent weather history data retrieval using Microsoft Power Query

In our previous articles about integrating Weather History data into Power BI and Microsoft Excel, we have used the Visual Crossing Weather API to retrieve historical weather data. In this article we discuss how to make those weather data queries automatically read the latest weather conditions using Microsoft Power Query.

If you would like to see the full example query in action within Microsoft Power BI, download our sample Power BI report.

The weather data service request

A typical weather history service request looks like the following request:

https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/weatherdata/history?aggregateHours=24&shortColumnNames=false&contentType=csv&unitGroup=uk&key=YOUR_KEY&locations=Hereford,UK&startDateTime=2020-04-01T00:00:00&endDateTime=2020-04-15T00:00:00

For more information on how this request has been created, see our Weather API documentation.

In the above request, the date range for our historical weather data will be fixed. The weather data will start on April 1st and finish on April 15th 2020. But what happens if we want our Power BI Report or Excel worksheet to automatically refresh and reflect the last week or month of weather observations rather than a fixed date?

We can easily achieve this by using Microsoft Power Query. Power Query is used behind the scenes in both of the articles above. Now, to make the query dynamic, we will modify the query and create dynamic start and end dates.

Power Query is also used in our extensive Weather Workbooks series. If you are looking to see how error handling and data transformations can be made on the weather, check out that series.

Adding dynamic dates to the query

In the query above, the dates are fixed. We will need to make the query dynamic:

https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/weatherdata/history?aggregateHours=24&shortColumnNames=false&contentType=csv&unitGroup=uk&key=KPYNQ6AWUDJE9AFERDCHJHSXK&locations=Hereford,UK" 
& "&startDateTime=" & StartDateText & "&endDateTime=" & EndDateText

Note how the last line joins together two parameters ‘StartDateText’ and ‘EndDateText’. We will populate these parameters with the text.

The logic to populate the two parameters will be as follows:

  1. Look up the current date and time.
  2. Round the current date and time to the following midnight.
  3. Find the start date by subtracting 14 days from the end date found in step 2.
  4. Format the start and end dates found in steps 1-3 for insertion into the weather API request.
  5. Insert the formatted start and end dates into the weather API request.

Step 1 – Look up the current date and time

To look up the current date and time we can use the Power Query DateTime.FixedLocalNow method.

 Now=DateTime.Date(DateTime.FixedLocalNow()),

Step 2 – Round the current date and time to the following midnight

The Weather API requests are inclusive of the start date and exclusive of the end date. Therefore to include the current date, we need to have the end date end at midnight of the following day.

We can do that by creating a new date time that contains only the date parts of the ‘Now’ date time. This creates a day time value of midnight on the current date. From here, we can add one day to the date to create our end date.

MidnightNow=#datetime(Date.Year(Now), Date.Month(Now), Date.Day(Now),0,0,0), //midnight on today's date
EndDate=Date.AddDays(MidnightNow,1) //midnight on tomorrow's date

Step 3 – Find the start date by subtracting 14 days from the end date

Now we can find the start date by subtracting as many dates as we would like from the end date. In our case we’ll look back 14 days. Note that we add a negative number to subtract days.

StartDate=Date.AddDays(EndDate,-14),

Step 4 – Format the start and end dates

Finally we need to convert the start and end date times to text values that we can include in our weather API call. The API uses a format similar to the ISO 8601 date time format:

StartDateText=DateTime.ToText(StartDate, "yyyy-MM-ddTHH:mm:ss"), 
EndDateText=DateTime.ToText(EndDate, "yyyy-MM-ddTHH:mm:ss"),

Step 5 – Insert the formatted start and end dates into the weather API request

We can now insert the start and end dates into the Weather API request URL:

https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/weatherdata/history?
aggregateHours=24&shortColumnNames=false&contentType=csv&unitGroup=uk&key=KPYNQ6AWUDJE9AFERDCHJHSXK&locations=Hereford,UK" 
& "&startDateTime=" & StartDateText & "&endDateTime=" & EndDateText

The remainder of the Power Query code can remain the same. This code will request the CSV data, extract the headers and configure the data types.

The full power query code

Here is the full Power Query code to look up the historical weather data. Note that must enter a valid API key for this to function correctly.

let

    Now=DateTime.Date(DateTime.FixedLocalNow()),

    MidnightNow=#datetime(Date.Year(Now), Date.Month(Now), Date.Day(Now),0,0,0),

    EndDate=Date.AddDays(MidnightNow,1),

    StartDate=Date.AddDays(EndDate,-14),

    StartDateText=DateTime.ToText(StartDate, "yyyy-MM-ddTHH:mm:ss"),

    EndDateText=DateTime.ToText(EndDate, "yyyy-MM-ddTHH:mm:ss"),

    WeatherAPIURL="https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/weatherdata/history?aggregateHours=24&shortColumnNames=false&contentType=csv&unitGroup=uk&key=YOUR_KEY" & "&locations=Hereford,UK" & "&startDateTime=" & StartDateText & "&endDateTime=" & EndDateText,

    Source = Csv.Document(Web.Contents(WeatherAPIURL),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),

    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Address", type text}, {"Date time", type date}, {"Minimum Temperature", type number}, {"Maximum Temperature", type number}, {"Temperature", type number}, {"Dew Point", type number}, {"Relative Humidity", type number}, {"Heat Index", type text}, {"Wind Speed", type number}, {"Wind Gust", type number}, {"Wind Direction", type number}, {"Wind Chill", type number}, {"Precipitation", type number}, {"Precipitation Cover", type number}, {"Snow Depth", type text}, {"Visibility", type number}, {"Cloud Cover", type number}, {"Sea Level Pressure", type number}, {"Weather Type", type text}, {"Latitude", type number}, {"Longitude", type number}, {"Resolved Address", type text}, {"Name", type text}, {"Info", type text}, {"Conditions", type text}})

in

    #"Changed Type"

If you would like to see the full example query in action within Microsoft Power BI, download our sample Power BI report.