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

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

In the last part of this series, we loaded the 5-day weather forecast information into the Power BI report and added the air pollution table to the report. In this part, we will add new custom columns to create human readable dates and times. The OpenWeatherMap API returns the date in a format similar to the Unix epoch time format. Follow the steps below to create a custom column and get a human readable date.

• Before you start, you will need to know how many hours your time zone is offset from UTC. You will need this to show the timestamp of the OpenWeatherMap API data in your local time zone. This will be useful if you wish to show this information on your report. I use it for easy checking that the data has refreshed correctly. You can get the time zone information from the time zone column in the Current Weather query. OpenWeatherMap shows the time zone information in minutes you can divide this value by 3600 to get the hours offset.
• Open the current weather query by clicking on it in the Power Query Editor.
• Now click on add column from the list of tools at the top of the screen.
• Go to the General section on the left of the screen and click on “custom column”.
• In the pop-up window that appears, change the name of the column from Custom to date
• Now enter the following into the formula box. Replace <+/- TimeZone Hour Offset from UTC> with the value for your time zone UTC offset.

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 
  <+/- TimeZone Hour Offset from UTC>, 0, [dt])

Example formula for new date column, using a 7 Hour UTC offset.

• Now navigate to the column you have just created.
• Select the column, so we can change the data type.
• Right click on the field name Select Change Type from the list of tools at the top of the screen.
• Select date from the list of data types to convert the column type to a date.

We need to repeat the above steps for the sys.sunrise and sys.sunset columns. For the sys.sunrise column, I suggest calling the new column sunrise and for the sys.sunset column. I suggest calling the new column sunset. You will need to change the column used from dt to sys.sunrise or sys.sunset as needed.

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 
  <+/- TimeZone Offset from UTC>, 0, [sys.sunrise])

  #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 
    <+/- TimeZone Offset from UTC>, 0, [sys.sunset])

For the sunrise and sunset columns, change the data type to time, as we are only interested in what time the sun will rise and set.

Once you have completed the creation of human readable date/time columns, you can delete the original columns from the query as we will not need to use them in our dashboard.

Now repeat the steps you have just completed to create human readable date columns that we will use in the forecast section of the report. If you have followed the series so far, then you will need to add custom columns and use the formula to refer to the following column names.

• dt
• city.sunrise
• city.sunset

Provide suitable names for the custom column name. I suggest using date, sunrise, and sunset for the three column names.

Here I show the example formula for city.sunrise.

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 
  <+/- TimeZone Offset from UTC>, 0, [city.sunrise])

Once you have added the custom columns, you can now remove the original columns, as they are no longer needed. After you have removed the original columns, rename the custom column for date from date.1 to date.

If you wish, you can add a human readable date column to the Air Quality table.

We have now nearly completed cleaning the data and are nearly ready can now start building the dashboard. I will complete the data preparation in part four.

Don’t forget to subscribe to

👉 Power BI Publication

👉 Power BI Newsletter

and join our Power BI community

👉 Power BI Masterclass