Microsoft Power Query makes it easy to automatically import recent historical weather data into Microsoft Excel or Power BI.
Using the Visual Crossing Timeline Weather API, you can request a dynamic period such as the last 30 days rather than calculating and updating start and end dates yourself. Each time Power Query refreshes the data, the Weather API automatically returns the latest weather history.
Create a recent historical weather API request
The Timeline Weather API retrieves historical weather, current conditions, and forecast weather using the same API endpoint.
For example, the following request retrieves the most recent 30 days of daily weather for Hereford, UK:
https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/Hereford,UK/last30days?unitGroup=uk&include=days&key=YOUR_API_KEY&contentType=csv
Replace YOUR_API_KEY with your Visual Crossing Weather API key. You can sign up for a key for free.
The important part of this request is:
/last30days
last30days is a dynamic date period. The dates represented by this value automatically change over time, so there is no need for Power Query to calculate the start and end dates.
Other dynamic periods are available, including today, yesterday, and lastyear.
Because we are requesting CSV output, we also specify:
include=days
This tells the Timeline Weather API to return one daily weather record for each date.
Import the weather data using Power Query
In Power BI or Excel, create a new blank Power Query and open the Advanced Editor.
The following query retrieves the latest 30 days of daily historical weather:
let
WeatherAPIURL =
"https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/Hereford,UK/last30days"
& "?unitGroup=uk"
& "&include=days"
& "&key=YOUR_API_KEY"
& "&contentType=csv",
Source = Csv.Document(
Web.Contents(WeatherAPIURL),
[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]
),
#"Promoted Headers" =
Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"
Whenever Excel or Power BI refreshes this query, the API request is run again and the most recent 30 days of weather data are returned.
Change the location
To retrieve weather for another location, replace:
Hereford,UK
with another address, city, postal code, or latitude and longitude.
For example:
London,UK
or:
38.9697,-77.385
For dynamically generated locations, the location value should be URL encoded before it is added to the API request.
Request hourly weather instead
To retrieve hourly historical weather rather than daily summaries, change:
include=days
to:
include=hours
For example:
https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/Hereford,UK/last30days?unitGroup=uk&include=hours&key=YOUR_API_KEY&contentType=csv
Hourly data contains one row for each hour rather than one row per day and therefore returns considerably more data.
Using a custom rolling date range
Dynamic periods are the simplest solution when you need a standard period such as the most recent 30 days.
If you need a custom period, Power Query can instead calculate the start and end dates and insert those dates into the Timeline Weather API URL.
Timeline date ranges use the following format:
/timeline/[location]/[start-date]/[end-date]
For example:
https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/Hereford,UK/2026-08-01/2026-08-14?unitGroup=uk&include=days&key=YOUR_API_KEY&contentType=csv
The following Power Query example automatically requests a rolling 14-day period:
let
Today = Date.From(DateTime.FixedLocalNow()),
StartDate = Date.AddDays(Today, -13),
StartDateText = Date.ToText(StartDate, "yyyy-MM-dd"),
EndDateText = Date.ToText(Today, "yyyy-MM-dd"),
WeatherAPIURL =
"https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/Hereford,UK/"
& StartDateText
& "/"
& EndDateText
& "?unitGroup=uk"
& "&include=days"
& "&key=YOUR_API_KEY"
& "&contentType=csv",
Source = Csv.Document(
Web.Contents(WeatherAPIURL),
[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]
),
#"Promoted Headers" =
Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"
Unlike the older Weather API, Timeline date ranges are inclusive of both the start and end dates. Therefore a 14-day range ending today starts 13 days before today.
Customizing the weather data
The Timeline Weather API lets you further customize the request, including:
- Measurement units using
unitGroup - Daily or hourly data using
include - Individual weather fields using
elements - CSV, JSON, or Flat JSON output
- Fixed or dynamic date ranges
For example, to retrieve only the date, minimum temperature, maximum temperature, average temperature, and precipitation:
https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/Hereford,UK/last30days?unitGroup=uk&include=days&elements=datetime,tempmin,tempmax,temp,precip&key=YOUR_API_KEY&contentType=csv
Reducing the requested data to the elements your workbook actually uses can reduce response size and simplify later Power Query transformations.
Next steps
You can use the same approach in both Microsoft Excel and Power BI. Once the query is configured, refreshing the workbook or report automatically retrieves the latest weather history from the Visual Crossing Timeline Weather API.
Use the Visual Crossing Weather Data Query Builder to create and test your API request before adding it to Power Query.