Unlock Weather Insights: How You Can Use Power BI with OpenWeatherMap — Part 1
Introduction
I wanted to harness the versatility of Power BI; I set out to craft a dynamic weather dashboard using an API. After thorough exploration, I zeroed in on the OpenWeatherMap API for its provision of a Free API, allowing seamless access to both current and forecasted weather data. With a cap of 60 calls per minute and 1 million calls per month for the free API, these limitations perfectly aligned with my project requirements. This article serves as a comprehensive guide, leading you through the step-by-step process of acquiring an OpenWeatherMap API key and seamlessly integrating current weather data into Power BI.
Setting Up Your OpenWeatherMap Account
Open the OpenWeatherMap website https://openweathermap.org/ you will need to create an account if you don’t already have one. To sign in or create an account, you must click on the sign in option from the menu at the top of the page.
The sign in page will show a Sign In to Your Account box
If you’re a new user, click on “Create an Account” to start the registration.
You must fill in the required information, and tick the applicable boxes below the username and password area, complete the “I’m not a Robot” reCAPTCHA, and click “Create Account.” Your account will be ready, setting the stage for creating your own API key for the Power BI weather dashboard.
Creating an OpenWeatherMap API Key
Upon logging into OpenWeatherMap, click on your account name, which is to the right of the “For Business Button,” triggering a dropdown menu.
Select “My API keys.” You’ll see the default API key generated by OpenWeatherMap. For this tutorial, we’ll use the default API Key. Copy the API key by selecting the characters in the API Key box, holding down the left mouse button, dragging the cursor to the right, and pressing CTRL+C (For PC Users, for Mac Users press Command+C). This saves the key into the clipboard for later use in Power BI.
Getting Coordinates of the Location for Weather Data
To specify the location for weather data, we must provide OpenWeatherMap with a location to show the weather. This tutorial employs longitude and latitude. Google can find these coordinates. For example, entering the town’s name followed by “coordinates” in a Google search will yield the required data.
If the coordinates include a ‘W,’ prefix the longitude coordinates with a minus sign in the Web URL.
Example: The coordinates for Reading, UK are 51.4551° N, 0.9787° W, so the longitude coordinate used will be -0.9787.
Connecting Power BI to OpenWeatherMap
We will now connect Power BI to OpenWeatherMap to retrieve the current weather data. But before we start, I will discuss what information we will retrieve. For this tutorial, we will retrieve the information in a JSON document. OpenWeatherMap can return information in JSON or XML. XML will return several tables in Power BI and have the benefit of descriptive text for wind speed and direction. But using calculated columns in Power BI, we can replicate this information using the information from the JSON document.
In Power BI, select a new report, click on the “Get Data” option, and choose “Web” from the Common data sources menu. This opens the From Web box to enter the URL for data retrieval.
URL Example:
http://api.openweathermap.org/data/2.5/weather?
lat=<Your Town latitude>&
lon=<Your town longitude>&
APPID=<Paste in your API Key using CTRL+V or Command+V &
units=Metric
(If you wish to use Imperial measurements change Metric to Imperial)Replace <Your Town Latitude>, <Your Town Longitude>, and <Paste in your API Key using CTRL+V or Command+V> with your information. Change “Metric” to “Imperial” for Fahrenheit temperature and miles per hour wind speed.
After you have entered the information, click OK to start the data retrieval process..
Data Loading and Cleaning in Power Query
After a brief delay, the data will load into Power Query for cleaning. Ensure there are no columns saying “table” or “list.” The data in this case was clean, and Power BI expanded all the tables. The information is nearly ready for use, but some steps are necessary.
Check the data, tidy up column names, and remove unnecessary columns. Rename the query to “Current Weather” for clarity. You can remove the coordinate columns (coord.lon and coord.lat) as we will not need them for analysis. Select both columns, right-click, and choose “Remove Columns” from the pop-up menu. The list of Applied Steps on the right-hand side of the query window will show an addition to the list of steps.
Column Name Changes:
- main.temp — rename to temp.
- main.feels_like — rename to feels_like.
- main.temp_min — rename to temp_min.
- main.temp_max — rename to temp_max.
- main.pressure — rename to pressure.
- main.humidity — rename to humidity.
- wind.speed — rename to speed.
- wind.deg — rename to degrees.
Double-click on the column header for the column you want to rename and type in the new name.
This meticulous process sets the stage for creating a polished dashboard in Power BI.
In the next part of this series, we’ll delve deeper into transforming this data into a visually appealing and insightful weather dashboard using Power BI.
Stay tuned for Part 2, where we’ll bring this weather data to life in a compelling Power BI dashboard.
Don’t forget to subscribe to
and join our Power BI community
