How to Load Weather Data into Power BI
In this article we will create a simple Power BI report that includes weather history data. The focus will be on creating a new weather data query within Power BI and then creating parameters for the query. From here we will create a simple report and discuss some of the common features used displaying weather data.
Creating the Weather Data Query
We will start by creating the Weather Data Query within Power BI. This will provide the daily historical weather records for a selected location for a range of dates.
To set up the query we will use the Power BI desktop tool. Our source of weather will be Visual Crossing Weather Data. This weather data provider offers web based queries including weather history data and 15-day weather forecast.
In our case we would like to create a query that retrieves the daily historical weather records for a single location for a specified data range. In a subsequent step we will add parameters within our Power BI query to set values for the location, dates, units of measurement etc.
The first step is to create the web query request URL. Visual Crossing offers a convenient tool for creating such as web query using the Weather Data Services page. We can use this weather data page to create a historical weather data definition request and then we can view the data in the browser to ensure that is what we were looking for. Once we are satisfied with the data in the browser, we can create a web based URL version of the request that we can use for retrieving the data using a Power BI Web Query.
The same techniques can be used to import data into Microsoft Excel
Here’s the result of creating a request for the historical weather data for the year 2019 in Miami, Florida, USA:
In this case we have restricted the data to the daily summaries (maximum and temperatures, total rainfall, etc). Hourly data is available if necessary.
We can see the data in the browser is exactly the data set we are hoping to integrate into our Power BI report so we can now create the URL query to retrieve the same data. We hit on “Query API” in the tool to bring up the query:
The query can be formatted to use various transmission techniques including a GET or POST request or even an ODATA request. All of these are suitable for Power BI data queries but we are going to focus on the simple GET request.
The raw request looks like:
https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/weatherdata/history?goal=history&aggregateHours=24&startDateTime=2019-01-01T00%3A00%3A00&endDateTime=2019-12-31T00%3A00%3A00&collectStationContributions=false&maxStations=-1&maxDistance=-1&includeNormals=false&shortColumnNames=false&sendAsDatasource=false&allowAsynch=false&contentType=csv&unitGroup=us&key=KPYNQ6AWUDJE9AFERDCHJHSXK&locations=Miami%2C%20FLLet’s look at the details of this request. To keep things simple, let’s remove the optional parameters and also we decode some of the non-alphanumeric characters such as the ‘:’. Finally adding some formatting so each parameter is on a single line makes things a little easier to read.
https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/weatherdata/history?
&aggregateHours=24
&startDateTime=2019-01-01T00:00:00
&endDateTime=2019-12-31T00:00:00
&contentType=csv
&unitGroup=us
&key=YOUR_API_KEY
&locations=Miami,FLThe main parameters of this request are:
aggregatehours is the hours for each history period so aggregatehours=24 is a daily time summary for the historical records. Setting this to a value of one would result in hourly records.
startDateTime and endDateTime are the start and end times for the data we would like. We have specified a start time of January 1st to December 31st.
contentType is the format of the data we will receive. In this case we’ve left the format a Comma Separated Values (CSV) which is perfect for importing into Power BI (and Excel etc).
unitGroup is the units of measurement we would like for the data we receive. For example ‘us’ sets temperature to degrees Fahrenheit and miles for distance whereas ‘metric; sets temperature to Celsius and distance to kilometers.
key is the API key that will be populated automatically for you be the Weather Services page.
locations is the list of locations that you would like weather data for. In this case we’ve specified Miami, FL. We could specify multiple locations by separating them by a ‘| ‘ (pipe) character. We could also use latitude and longitude values.
If you would like a full list of available parameters and their description, check out the Weather API documentation.
Adding the Power BI Desktop Web Query
We can now enter the parameter into Power BI Desktop to retrieve the weather data.
To create the basic query, select ‘Get Data’ from the Home ribbon bar and choose ‘Web’.
For the first query, we’ll add the static request directly from the Weather Data Query Builder page into the text definition. We’ll then add parameters in the next section.
As soon as we hit ‘OK’, Power BI retrieves the data and shows a preview:
We can see that Power BI has done a great job of interpreting the CSV data retrieved from the weather data service. We can hit ‘Load’ to load the data into Power BI. After a moment, the fields from the historical weather data records are populated ready for us to start creating our report.
Our first weather data chart — temperature by day
Before we move on to adding parameters to our query, let’s add the first chart to our report.
Here is the chart definition.
We chose a line chart using the Maximum and Minimum Temperature fields for values (y-axis). The Date time field was used for the x-axis. Note that we changed the default aggregation of the temperature values from sum to average:
We did this because “Sum of Temperature” means very little if we change the day level data to week, month, quarter of year. Temperatures are normally summarized by the average (i.e. arithmetic mean), maximum or minimum function.
For example, I might be interested in the normal High temperature for January (the average), the highest temperature during January (the maximum) or the lowest temperature during January (the minimum). It is unlikely that we want to know that all the temperatures in January sum up to 2000F!
We’ll look at some more features of the report charts when we return to the report editing after completing the query parameters.
Adding parameters to the Data Query
In the previous section, we created a fixed weather data query to retrieve the historical weather data by day for Miami, Florida in 2019. To make this really useful, we need to add some parameters to the query so we can change the location, year or any other part of the query.
To do this, we open the existing query in Power Query from within Power BI Desktop:
From the New Ribbon, select ‘Manage Parameters’ and create a new parameter:
Our first parameter will allow the user to set the location for the weather data.
We specify the name of the parameter (location), set the type to ‘text’ and that the parameter is a free form text entry. Finally we add a default value of ‘New York, NY’.
When we hit OK, nothing much happens. We must use this parameter in our query to cause the data to refresh with the parameter value.
To do this, we edit our query using the ‘Data source settings’ ribbon button. When we open the current query we see our basic, hard coded URL:
If we switch the type from ‘Basic’ to ‘Advanced’, we can start using our parameters:
We can now see that the URL is going to be built up from a series of parts. We take &locations=Miami,FL from the first URL and split it between new two parts — the hard coded ‘&locations=’ and the parameter value.
First, we add a new part, leaving the type as text. In the value we add ‘&locations=’
We then add another part and change the type to parameter and choose our existing ‘location’ parameter.
If after pressing OK we then press ‘Refresh Preview’, the web query will be re-run with the parameter default value of ‘New York, NY’.
We can then use the parameter to update the location dynamically. Therefore if we can enter ‘Paris, France’
When we press OK and then ‘Refresh Query’, we see the weather history for Paris, France.
In total we split the original web query URL into five parameters. The location, key, unit group and date range. This will allow the user to change the location and date range of the data they are interested in.
We now have a good set of historical weather data. Let’s create a basic report to show this off.
Creating the Power BI report
In our simple Historical Weather Data report we have a couple of elements:
The location (“Miami, FL”) is displayed as a Card. This allows us to display dynamic data values from the weather data.
We then have a set of four KPIs to highlight some interesting weather facts for the selected time period — the maximum and minimum temperatures, the total amount of rain and finally the number of days which experienced rainfall.
The KPI definitions are very simple:
We use the Maximum of the Maximum Temperature as the indicator and then the date time year as the trend. We don’t add a target. We then modify the font size and colors.
The minimum temperature, rainfall amount and rain days KPIs are added in a similar way.
We have already looked at the temperature values chart in the previous section. We can create a wind-pressure chart in a similar way using a Line and Column chart
In this chart, the line displays the pressure and the bars display the temperature:
In the final row we have two elements showing the rainfall and a map showing the location that we have used for the data.
The year to date rainfall chart which shows how the total rain fell throughout the year. This approach clearly shows the total rainfall for the year (60 inches in this case) and also the days when a lot of rain fell (where the chart climbs steeply) and times when there was not much rain (where the chart stays flat).
In this case we can see that a lot of rain falls in Miami during the summer months.
The cumulative rainfall is a simple Stacked Column Chart with one element. Rather than showing the rain fall for each day, we want to show the cumulative amount from the start of the year. To do this we create a new measure that defines the year to date value:
The easiest way to do this is via a quick measure which helps guide us to the correct fields of the Year to date measure:
We then use the new measure in our chart
The final element of our report is the map chart that shows the location of the requested weather data.
The map uses the longitude and latitude values returned as part of the weather data query to show the point on the map:
And there we have our simple Historical Weather Data Report in Power BI Desktop. The last stage is to publish this to the weather so we can enjoy the report in our favorite web browser.
Publishing the report to Power BI on the Web
To publish the report, hit the ‘Publish’ button:
In this case we will publish the report to our own work space.
Changing parameter values in the Power BI
The final stage is to select a different location. To do this we have to modify the parameter values that we specified on the query. The parameters can be modified in the data set, found under ‘My workspace’
Modifying the settings for the data set brings the parameter set:
We can modify the location here. Let’s change it to London, UK
After apply the new query parameter value, we can return to the data set list in and hit refresh. We then see the new data in our report
We have seen in this article that we can easily add weather data to our Power BI reports and dashboards. We did this by finding a great weather API source that can be accessed from the Power BI Web Query. From there, creating a great looking historical weather data report was straight forward.
What about a weather forecast? In my next article, we’ll use the techniques used here to retrieve the weather forecast and create a great looking weather map!
