How to create a weather forecast report in Power BI

In my previous article we described the process to integrate historical weather data into a Power BI Report. In this article we are going to extend those ideas to create a weather forecast report. Here is a sample of what we are going to produce.

Weather Forecast Report in Power BI

The report retrieves the weather forecast data for multiple locations. The user can switch between locations to view the forecast for each location in detail. The report displays the 15-day weather forecast including daily high and low temperature, wind speed and gust, and rainfall and snowfall. Other weather forecast variables are available but we are going to limit ourselves to this set for this example.

Populating the weather forecast data

The first step in creating our report is to populate the Power BI report with weather forecast data from a weather service capable of providing a direct query connection to Power BI. We will be using the Visual Crossing Weather Data platform to provide the weather forecast data as their Weather API service provides an easy way to read the weather forecast for multiple locations in a single service call. Visual Crossing also offer an easy way to build data queries directly in the browser so it makes the process easy.

Weather Forecast in the Browser

We will not go into the full detail on how to create a query within the Weather Data platform as it fully describe in the previous article. The high level steps are:

1. Open the weather data services page (create a free trial account if necessary)
2. Enter in your list of locations
3. Run the weather forecast directly in the browser to see the data.
4. Use the query URL builder to create a URL that we can use to retrieve the same weather forecast data directly in Power BI.

The final query URL will look like:

https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/weatherdata/forecast?&aggregateHours=24&contentType=csv&unitGroup=us&key=YOUR_API_KEY&locations=Miami%2C%20FL%7CNew%20York%2C%20NY%7CLos%20Angeles%2CCA

The forecast query is simpler than the historical weather data because there are fewer parameters. We simply need the list of locations and whether the result should be a daily summary forecast or a detailed hourly weather forecast.

The aggregateHours parameter specifies the length of the forecast period — hourly, twice daily or daily. We are looking for a daily weather forecast summary so we use the value of 24 hours. Changing this value to 1 would create an hourly weather forecast.

The locations parameter provides the list of locations for which to look up the weather forecast.The locations parameter can include multiple locations. This is simply performed by concatenating location addresses with the pipe (‘|’) character (if you built the query via the query page then this will be done for you).

Just as with the weather history data connection, we need to create a parameterized web query connection with Power BI Desktop so that we have a refreshable query for the weather forecast data into Power BI.

We need just three parameters in the Power BI query:

Note how the location parameter value has been populated with multiple locations.

The Power Query URL definition includes these parameters in the query definition:

The preview shows that the multiple location based weather forecast is available:

When we import data using CSV format such as this, we initially rely on Power Query to automatically identify the column data types (text, number etc). Some weather variables only have data for a small number of the days and so Power Query may not correctly interpret the values as number.

A good example of this is snowfall. The first 200 rows of the query may well not include snowfall values so Power Query will not guess the column type as numeric. We need to double check the column data types that Power Query guesses and manually correct any data types that were guessed incorrectly. If columns don’t show in Power BI as measures, that is a good hint that the data import did not assign the expected column data type.

Building the weather forecast dashboard

Now that we have the data, we can start building the dashboard. There are a couple of elements:

Location slicer

The location slicer allows the user to select the location to filter the remainder of the visualizations. We have driven it from the Address field

Temperature forecast

The temperature forecast is a stacked area chart to illustrate the daily high and low temperatures. By stacking the area like this we provide the user with an easy way to determine not only the temperature extremes but also the spread of temperatures throughout the day.

We use a stacked area because the stacking gives a clearer indication of the two temperature values. This stack is created by adding a new derived measure to fields. This new measure is the difference between the maximum and minimum temperatures:

Max Temp Delta = sum(WeatherForecast[Max Temperature])-sum(WeatherForecast[Min Temperature])

By stacking this delta measure on top of the minimum temperature we get a clear separation of the minimum and maximum temperatures.

Wind and wind gust forecast

The stacked technique is used again to display the wind speed and any additional wind gust. The light pink shows the wind speed (generally defined as the average speed over an amount of time) whereas the dark pink highlights the wind gusts (the wind speed over a short period of time). Again we use a delta to be able to stack the colors like this:

Wind Gust Delta = sum(WeatherForecast[Wind Gust])-sum(WeatherForecast[Wind Speed])

Precipitation and Snow Forecast

The final chart shows the precipitation and snow forecast.

The green values indicate the liquid water amount of any precipitation when melted (rain, snow etc). The dark blue indicate the predicted snow amounts. Therefore when there is only a green value, this indicates rain. When there is a snow forecast there is both a blue bar indicating the amount of snow and a green bar indicating how much liquid water would have fallen if this were rain.

In this chart, both values are derived directly from the weather forecast Precipitation and Snow fields.

Refreshing the weather forecast automatically in Power BI

Now we have initial weather forecast report so let’s publish it to the web version of Power BI. First we publish to the web version from Power BI desktop:

We have published to “My workspace”. If we look into the datasets, we can see the newly published WeatherForecastDashboard dataset:

The last thing necessary is for the weather forecast data to refresh automatically so that whenever we open the report, the weather forecast is updated.

If we open the settings for this dataset, we can set up a Scheduled refresh so that our weather forecast data is always up to date:

Setting the Scheduled refresh in Power BI

Here we have set up a daily refresh so that the data updates every day at 7am. The weather forecast will be updated just in time for the start of the work day.

Next steps

We have now shown two weather data reports and dashboards in Power BI — an historical weather data report and a multi-location weather forecast report. This is just the start of what could be achieved using the weather data.

Existing business data could be joined to the historical weather data to identify weather based trends. Weather forecast data could be combined with business rules to highlight any weather forecast that will impact the running of the business — either for the good or bad.

There is also a great deal more weather data available than the information we used in these initial reports. Would you like to drill down to the hourly weather forecast data? Would you like to understand how the climate has changed for a location? Using weather data within Power BI opens up a rich set of weather data investigation opportunities.

Do you have questions on how to use weather data within Power BI? Please let me know in the comments below!