In this article, I’m going to show you how to easily load historical weather data for any worldwide location into Microsoft Power BI for further analysis. We’ll walk through the process step-by-step showing you how to construct the query in Visual Crossing Weather web interface and then use the query URL to import the weather history results into Power BI.
If you prefer to follow along using a video as well, open a browser on the matching YouTube video here: https://youtu.be/YJQE5wULfuM
The estimated time to complete this exercise is about 5 minutes.
We’ll start by going to the Visual Crossing Weather Data page. We’ll then click on the link near the top of the page to go to weather data download page.
Once at the log-in page, we will need to sign into our Visual Crossing Weather account. If you don’t already have an account simply click on the orange button on the right-hand side of the login page. Your free trial account will give you instant access to historical weather data for any location around the globe.
For this example, we’ll select the option to manually enter a location to use for our historical weather query. However, we could instead load a sheet of addresses or paste in a list as plain text if we have a location list already available.
Instead of typing an address, we’ll tell the system to use our current location by clicking on the “Populate from your location” link below the location text box. However, we could instead manually type any address, a city name, or a postal code. Optionally, we can also give the location a friendly name so that we can identify this location more easily in the output data.
Next, we need to choose the query type. Since we are interested in weather reports from the past, we’ll select the Historical Data option. However, in this panel there are other options as well such as weather forecast queries, historical data summary reports, and hourly data. These are among the many options covered by our other tutorials and videos.
When we select the Historical Data option, the panel opens for us to set the parameters for our historical weather data query. The primary option that we need to set is the Date Range. We use the calendar selectors for the start and end points to select a range that we are interested in analyzing. The left-hand side calendar selects the starting date and the right-hand side calendar selects the ending date. We can select a month, a few months, an entire year, or more depending upon our needs and account level.
When we run the query the default view is the weather calendar. This view provides a simple overview of the result data. It is most useful for comparing data from multiple locations side-by-side.
To see more details we can change to the grid view by clicking the “Raw Grid” button near the top of the page. This view shows a single row for each day of the requested range and at the location we selected earlier. You can now see the various weather metrics that are provided in the output data. These include common values such as temperature, precipitation, and wind as well as less common value such as heat index, cloud cover, and wind gusts. For more information on the details regarding our weather metrics, please see our weather data documentation.
We could now download the data as a CSV by pressing the “Download all data” button for manual import into various analysis tools, but instead, we’ll switch to the API view to generate the query URL. Using a query URL will allow us to directly import our historical weather data into Power BI. In addition, this query will allow Power BI to fetch live data, refresh the data, and even dynamically modify the query parameters. Since the default query URL output is CSV, which Power BI can read directly, we can simply press the “Copy full query” button to copy the query URL to the clipboard.
We can now use this URL to load the weather query results directly into Power BI. After we load the Power BI interface, we’ll load the historical weather data by selecting the Web option from the Get Data menu. This will start the process of importing data via the URL that we just copied.
Power BI will open a prompt asking for the URL from which to fetch the data. Since we already copied the query URL from the Visual Crossing Weather page earlier, we can paste the query URL directly into the URL box and click the OK button to submit the query.
Power BI will now run the weather data query and show us a sample of the results in a preview window. We can see that our historical weather data query returned many weather metrics including temperature precipitation and wind speed. These metrics are reported for every day during the historical period that we requested.
When we click on the Load button, Power BI will fetch the entire forecast dataset and make it available for analysis. Once loaded, we can immediately begin using our weather data in our Power BI analyses. We can not only analyze the data directly, we can correlate weather with existing business data to identify actionable patterns and trends.
In the above requests, the historical weather data has been retrieved for a fixed date time range. If you would like the date range to automatically update based on the current data, read how to Automate recent weather history data retrieval using Microsoft Power Query.
If you would like to learn more about using Visual Crossing Weather options such as weather forecasts, multiple location import, and use within other analysis tools please see our other tutorials.