Unlock Weather Insights: How You Can Use Power BI with OpenWeatherMap — Part 4

Sample image from the weather dashboard we will create. Background image in dashboard Photo by Tim Foster on Unsplash

We are nearly ready to visualise the dashboard. But before we do, I want to add some custom columns to the Current Weather and forecast queries to help with the dashboard. I will add a column giving the wind direction using words as the data just gives us the direction in degrees. I will also add a column giving us the wind direction using compass points, such as N, NE, ENE and ESE. I will also add a column to give a description for wind speed using the Beaufort scale. And we will add a final custom column to display the icon for the weather.

The following steps will show you how to add the wind direction to the Current Weather query. If you wish, you can repeat these steps in the forecast query, but I will only use the compass points because of space on the screen.

Steps to add the wind direction column.

1. Open the power query editor.
2. select the Current Weather query.
3. From the toolbar at the top click Add Column.
4. Click Conditional Column in the General section.
5. In the new column name, enter wind direction description.
6. Select degrees from the column name option in the If line.
7. In the Operator box, select is greater than or equal to.
8. Enter 348.75 in the value box.
9. In the Output box, enter North.

Now, we have included the condition that any wind direction of 348.75 or more shows that the wind is blowing from the north. We will need to repeat these steps for the other directions. Here are the screenshots for this dialog box showing the remaining compass points. Click Add Clause to add another condition.

Screenshot of wind direction conditional column showing from North to West — SouthWest.

Screenshot of wind direction conditional column showing from West — SouthWest to SouthEast.

Screenshot of wind direction conditional column showing from East — SouthEast to North

The last operator should be is greater than or equal to. Remember to put null in the else box. As we don’t want to show anything in the event of an incorrect value.

Now we need to repeat the above steps for the compass points to be used in the forecast query. The steps to add the wind direction compass points are:

Steps to add the wind direction compass points column.

1. Select the forecast query.
2. From the toolbar at the top click Add Column.
3. Click Conditional Column in the General section.
4. In the new column name, enter wind direction code.
5. Select degrees from the column name option in the If line.
6. In the Operator box, select is greater than or equal to.
7. Enter 348.75 in the value box.
8. In the Output box, enter N.

We have now determined that when the wind direction is 348.75 or more; it falls under the category of blowing from the N compass point. We will need to repeat these steps for the other points of the compass. Here are the screenshots for this dialog box showing the remaining conditions.

Screenshot of wind direction conditional column showing from N to WSW

Screenshot of wind direction conditional column showing from WSW to SE

Screenshot of wind direction conditional column showing from ESE to N

When you have completed the conditions click OK to save the changes. Now you should change the data type to text. To do this, right-click on the column name of the column you have just created. Select Change Type from the pop-up menu that appears, now select text from the list of data types.

We will now add the Beaufort scale description of the wind speed to the Current Weather query. To do this we will add another conditional column to the query.

Steps to add the Beaufort scale wind speed column

For my dashboard I am using metric units. If you are using imperial units, then you will need to convert metres per second to miles per hour. The steps are:

1. Select the Current Weather query.
2. From the toolbar at the top click Add Column.
3. Click Conditional Column in the General section.
4. In the new column name, enter wind speed description.
5. Select speed from the column name option in the If line.
6. In the Operator box, select is greater than or equal to.
7. Enter 32 in the value box.
8. In the Output box, enter Hurricane.

We have now added the condition that any wind speed of 32 m/s or more is a hurricane. We will need to repeat these steps for the other wind speeds. Here are the screenshots for this dialog box showing the remaining wind speeds. Click Add Clause to add another condition.

Screenshot of wind speed conditional column showing from Hurricane to Moderate Gale

Screenshot of wind speed conditional column showing from Strong Breeze to Light Air

When you have completed the conditions click OK to save the changes. Now you should change the data type to text. To do this, right-click on the column name of the column you have just created. Select Change Type from the pop-up menu that appears, now select text from the list of data types.

Adding weather icon URL column

The final custom column we will need is the URL to display the icon for the weather. We will add this column to both the Current Weather and Forecast tables. The steps to create this column are:

1. Select the Current Weather query.
2. From the toolbar at the top click Add Column.
3. Click Custom Column in the General section.
4. Enter weather image url. As the new column name.
5. In the custom column formula box, enter the following

"https://openweathermap.org/img/w/" & [weather.icon] &".png"

5. Click OK to save the column.

Weather Image URL Custom Column box with the formula for the URL

Now repeat the above steps for the forecast query.

We have now completed all the data cleaning and preparation for the dashboard. In the next part of this series, I will provide the code for a table with the images from Unsplash that I use in my dashboard. You can use this table for your report if you wish.