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.
Visual Crossing Weather enables you to import historical weather data into Power BI. The data can be imported as CSV or JSON depending on your preference. It is possible to import the data using a live weather api that refreshes against the latest weather data. Alternatively, you can schedule a bulk request to execute on the Visual Crossing cloud and be available for import the data into Power BI afterwards. Scheduled requests are particularly useful with data sets with many locations or long historical queries.
We’ll walk through the process step-by-step showing you how to construct the query in the Weather Query Builder and then use the weather API query URL to import the weather data results into Power BI.
Step 1 – construct a historical data query on the Weather Data Services Query Builder
We’ll start by going to the Weather Data Query Page. We can retrieve live data directly into the browser for free on this page. We can also construct the Weather API query or download weather data using this page.
To be able to import past weather data into Power BI we will need to copy the Weather API query. For this we will need to create a free account so we have an API key. For full details on creating queries within the query builder and signing up for an API key, please see our full article on how can I download weather data as a csv file.
As we are focusing on pulling past weather data, we insert a data range into our query to request data from historical days. Date ranges can be entered by choosing specific dates or using dynamic date ranges such as ‘last30days’.
You can choose to use either daily data or hourly data.
Step 2 – create a Weather API query to pull live data into Power BI.
We could now download the historical data as a CSV file by pressing the “Download” button for manual import into various analysis tools. Downloading the data as a CSV file can by easily imported into Power BI.
However, we’ll switch to the API view to generate the query URL. Using a query URL will allow us to directly import our weather history data into Power BI. This query will allow Power BI to fetch live data and refresh the data so that our weather data is always up to date.
To switch to the API view, click on the ‘API’ button in the toolbar:
Make sure you are logged into your account at this point so that ‘YOUR_API_KEY’ is replaced with your own personal API key.
Notice that the ‘contentType’ parameter is set to JSON for this query. This is the default value but we also support retrieving CSV formatted data. Both of these can be used for importing live queries into Power BI.
For this example, we will use CSV format as it allows us to focus on the integration and data techniques used within Power BI. JSON format is a powerful format that enables additional functionality that you can consider after successfully using Power BI with CSV data.
To switch to CSV format, simply change the format drop down box. In addition JSON supports only a single data set section (such as daily or daily data). Choose daily data for this example:
Finally, copy this URL into your clipboard. You can test that the URL returns the correct data by pasting the URL directly into a browser:
Step 3 – import the data into Power BI
When we start Power BI, we are presented with a blank document.
Click on ‘Get data from another source’ to popup the connector dialog. From this dialog, click on ‘Other’ then ‘Web’.
Alternatively, you can access the Get Data -> Web from the toolbar by clicking ‘Get Data’ and then web.
Power BI will open a prompt asking for the URL from which to fetch the data. Since we already have the query URL copied, we can paste it directly into the URL box and click the OK button to submit the query.
In certain cases, such as when first accessing the weather API, Power BI may show an authentication popup box:
I this case, choose the ‘Anonymous’ option on the left and hit ‘connect’
Step 4 – review the data preview and modify data types as necessary
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 weather query returned many weather elements including temperature, precipitation and wind speed. For a full list of elements, please see the Weather API documentation.
As we chose CSV as our data format type, Power BI makes educated guesses on the format of the data including the data type of each of the column elements. Review that the file origin is marked as ‘UTF-8’, the delimiter is marked as ‘Comma’. Also review the data types assigned. You can modify them manually if the automatic detection is not correct.
Once you have reviewed the data, hit ‘Load’ to retrieve the data into Power BI for analysis.
Step 5 – perform analysis in Power BI
When we click on the Load button, Power BI will fetch the entire 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 make and adjust plans such as staffing levels, supply levels, optimal operating hours, and more.
Using a scheduled query rather than a live query
In the above steps, we integrated a live weather API request into the Power BI document. In some cases, it is preferable to import data from a scheduled dataset rather than a live weather API query. The data format will be the same and so integration is just as easy. Scheduled queries are particularly useful when you need data for many locations as the schedule will run the data for all the locations. For more information on using scheduled queries, please see How to schedule weather queries.
Questions or need help?
If you have a question or need help, please post on our actively monitored forum for the fastest replies. You can also contact us via our support site or drop us an email at support@visualcrossing.com.