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

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

We are on the last part of adding weather information to the dashboard. But before we can add the information, we will need to group the Forecast table records. As the Forecast table shows the weather in 3 hour steps throughout each date. We will reduce the number of rows to one row of each date. I will use the maximum and minimum temperature for the day. I will average the air pressure and humidity for each day. For the weather, Wind Direction and the weather icon, we will use the median value as we cannot use the average value as these columns are text values. I’m using the median value as it is possible to have different weather during the day. My intent was to have a guide to future weather. I wanted a view of forecasted weather in a simple table view without scrolling up and down a list of weather for different dates and times. Grouping these values will also remove the unused columns from the data model. But before we can group the information, we will need to split a column into two columns to create separate date and time columns to assist with grouping.

Splitting date and time column

The Forecast table has a column called dt_txt which holds the forecast date and time in text format and is ideal for splitting into separate date and time columns. The steps below will take you through the process.

• Open the Power Query Editor by clicking on the transform data icon on the Home ribbon bar.
• Click on the Forecast query on the left-hand side of the Power Query Editor window.
• Make sure that Power BI has selected the last step of the query. If not, then click on that last step.
• Select the dt_txt column in the query window.
• Select Click on the split column icon from the transform tab ribbon bar.
• A pop-up menu will appear select By Delimiter from the list of options.
• The Split Column by Delimiter pop-up window will appear.
• Click on the dropdown arrow for the Select or enter delimiter dropdown list and select space from the list of options.
• Under Split at click the Left-most delimiter radio button. The Split Column by Delimiter should look the same as the image below.

• Click OK.

Power BI has automatically converted the two columns into date and time data types. Now we need to rename the columns.

• double click on the column header showing the column name for dt_txt.1 and change the name to Date.
• double click on the column header showing the column name for dt_txt.2 and change the name to Time.

We are now ready to group the Forecast information. The steps below will take you through the process.

Grouping the Forecast Information

• Click on the Transform tab from the list of tabs at the top of the Power Query Editor window.
• Click Group By.
• Click the Advanced option radio button.
• Click the dropdown arrow in the dropdown list above the Add grouping button.

Power BI Group By window

• Select Date from the list of column names as we want to group by date.
• In the New column name box, clear the existing text and enter Maximum.
• From the Operation dropdown list, select Max.
• From the Column dropdown list, select the temp_max column
• Click Add aggregation.

Now we will add the Minimum Temperature aggregation.

• In the New column name box, enter Minimum.
• From the Operation dropdown list, select Min.
• From the Column dropdown list, select the temp_min column
• Click Add aggregation.

we need to add the humidity aggregation.

• In the New column name box, enter Humidity.
• From the Operation dropdown list, select Average.
• From the Column dropdown list, select the humidity column
• Click Add aggregation.

The following steps will add the Pressure aggregation.

• In the New column name box, enter Pressure.
• From the Operation dropdown list, select Average.
• From the Column dropdown list, select the pressure column
• Click Add aggregation.

We will now add the Weather aggregation.

• In the New column name box, enter Weather.
• From the Operation dropdown list, select Median.
• From the Column dropdown list, select the description column
• Click Add aggregation.

Now we will add the Wind Direction aggregation.

• In the New column name box, enter Wind Direction.
• From the Operation dropdown list, select Median.
• From the Column dropdown list, select the wind direction code column
• Click Add aggregation.

Finally, we will add the Weather Icon URL aggregation.

• In the New column name box, enter Weather Icon URL.
• From the Operation dropdown list, select Median.
• From the Column dropdown list, select the weather icon url column

We will add one more column to this table to show the Humidity as a percentage. To do this, follow the steps below.

• Click the Add Column tab from the list of tabs along the top of the Power Query Editor.
• Click the Custom Column icon on the Ribbon Bar.
• Enter % Humidity In the New column name text box
• In the custom column formula text box, enter:

[Humidity] / 100

• Click OK
• Right Click the column header and select Change Type.
• Select Percentage from the list of data types in the new pop-up menu that appears.
• Change the number of decimal places from 2 to 0. You can leave the number of decimal places as 2 if you wish.
• Click the Home tab and then click Close and Apply to add the changes to the data model.

Now we are ready to add the information to the dashboard. Follow the steps below to add the table and information to the report.

Adding 6 Day Weather Forecast Information to Dashboard

• To add the table to the dashboard, select the card visual from the list of visuals in the insert menu or the build pane. If you haven’t enabled on-object interaction, then you can select the table visual from the visualizations pane.
• Expand the Size and Style options on the Format Pane at the right-hand side of the Power BI Desktop window. From the Size and Style options, change the following values.
• Change the Height from 300 to 360.
• Change the Width from 300 to 690.
• Change Horizontal from 100 to 565.
• Change Vertical from 100 to 170.

Before we can do any further formatting of the table, we need to add data to the table. So we will add all the required columns to the table, and then come back and complete the formatting.

Before we can add the columns, we need to change the data category of the Weather Icon URL column. So the column shows the actual icon, not the text string, when we drag it into the table visual. To do this, follow the steps below:

• In the Data pane, select the Weather Icon URL column from the list of columns for the Forecast table. If you do not see the list of columns for that table, click the > symbol next to the table name to expand the table.
• Once you have selected the column, click on the down arrow for the Data category dropdown list to show the list of categories. This option is in the Column Tools ribbon bar.

Data category dropdown list

• Select Image URL from the list of categories as shown in the image above.
• We will also remove the summarization from the % Humidity, Humidity, Maximum, Minimum and Pressure columns. As we do not need to total these columns. Select each column and complete the following steps.
• Click on the Column Tools tab to open it if it is not already open.
• Expand the Summarization dropdown. It will show Sum, as this is the default Power BI summarization for numeric columns.

Column Tools Summarization dropdown list

• Select Don’t summarize from the list of options.
• Repeat the above steps for the remaining columns.

Now we need to create two measures to show the temperature with the Temperature symbol after it. In my case, °C.

• navigate to the measures table which we called _All Measures when we created it earlier in the series and click New measure.
  Enter the code show below to create the Forecast Maximum measure.

Forecast Maximum = 
FORMAT(
    MAX(
        Forecast[Maximum]
    ),
    "#0" & 
    UNICHAR(176)
    &"C"
)

• Click the commit button to add the measure.
• Now click New measure to create the second measure.
• Enter the following code to create the measure.

Forecast Minimum = 
FORMAT(
    MAX(
        Forecast[Minimum]
    ),
    "#0" & 
    UNICHAR(176)
    &"C"
)

There are several more steps we need to complete adding the information to the Forecast table and to prevent this article from becoming too long, we will stop here and we will add the forecast columns to the Forecast table visual in the next part of this series.

Don’t forget to subscribe to

👉 Power BI Publication

👉 Power BI Newsletter

and join our Power BI community

👉 Power BI Masterclass