You are Here:Home>>Computer Skills>>How to: Create a Weather Dashboard with Power BI and API

How to: Create a Weather Dashboard with Power BI and API

By | 2017-06-11T21:02:27+00:00 Jan 18, 2017|Computer Skills|

Power BI is an extraordinary tool for business analytics, giving us a new perspective for the data. Power BI is a suite of business analytics tools to analyze data and share insights. Monitor your business and get answers quickly with rich dashboards available on every device. If you don’t know it yet, pls visit this link.

The most interesting thing about Power BI, is the fact it is a live tool. There several data sources we are able to connect with, integrating with other, internal or external and make it work together. Besides a lot of different visuals to match the user needs. And if this is not enough, you can develop your own. New features and visuals are being added to Power BI all the time, so it is important to maintain a close eye on it. One of the most recent and interesting visuals added to Power BI was the ESRI ArcGIS Maps visual, which allow us to add GIS maps to dashboards, with custom layer capability. We will be covering this in a future tutorial.

Another good point on Power BI is the way we get involved with this project, since Microsoft has a proactive listen positioning, by asking community to feed them with ideas. You can participate in this platform, by either registering your idea, or supporting others.

In this tutorial i will show you how to connect to the OpenWeatherMap API, through json, and build a dashboard including current weather and forecast for a certain city.

 

We will cover the following subjects:

  • connecting to data, web and local file;
  • modeling data;
  • setting parameters to get filtered data from web and to filter columns;
  • load images to visuals (image, table);
  • relationships between tables;
  • adding calculated columns;
  • use visuals

 In order to complete this tutorial, you will need to:

[sociallocker id=4165]

  • Power BI Desktop latest version (Download)
  • An OpenWeatherMap (OWM) free account (Go here)
  • a couple of visuals which can be found here.

[/sociallocker]

Step 1 – Getting Data for cities

So, in order to use the OWM API, we will need the API Key and to know the ID of the locations we want to check the weather. Sign in at OWM website, go to API Keys and you will see it.

As we are going to work with cities ID, in order to avoid mismatching, we will need to grab all the cities the OWM covers. We can get it from the sample bulk data.

Now that we have the cities list and the API key, we will set the first source of data. Goto Get Data -> More -> Text and click connect. You should get a window like the bellow. Click Edit.

Result of loading city_list.txt

You will get in the query editor. Now we will create the first calculated column, to join the city name with the country, using DAX. In the menu, click in add column -> custom column. In the Column name write Place and in the custom col formula:

2

This simple DAX formula, will populate the new column with the city name and country code, split by a comma, such as Razvilka , RU. Click Ok.

Now, we got all the cities and their ID, which we will need to pull the data we want to receive. This is very important when we are working with thousands of lines, since the performance will be affected. So, the less data we need to load, the best.

In this case, we don’t need to model data, since the structure was load according to our needs, i.e. all fields we need in the table.

Now, we will need to create a country list, in order to use it as a filter. This will enable us later on, to filter cities, according to country, instead getting all the cities IDs. We need to remove duplicates, as this table will act as source in relationships.

3

If you check the relationships between tables, you will notice a new relationship was added, between city_list and countryCode.

You can check relationships either by using the left ribbon, on the third icon, or in the top ribbon,  Home -> Manage Relationships 11.JPG. I prefer the first option, as give me a wide picture on how things are connected and if i’m missing something. This became more visible when you have a lot of tables.

4

Step 2 – Configure and get data from API for current weather

Now we will connect to the first service data from OWM API, to get the current weather. In fact, in this tutorial we are going to use only two of the available services, but you can explore yourself for the remaining ones and enrich application. Feel free to post those in comments.

to make use of this API call, we will need to add the link to the data source adding your API Key. The complete example would be like:

http://api.openweathermap.org/data/2.5/weather?id=5128581&appid={insert here your API Key}

We can also add additional &units=metric for getting data in metric system or change for imperial. Otherwise, it will be in standard mode. For more details pls check API page.

In this case we will be using metric. So final url call would be:

http://api.openweathermap.org/data/2.5/weather?id=5128581&units=metric&appid={ your API Key goes here }

5.gif

Getting back to Power BI desktop, we go to Home -> New Sources 10-> Web 9. In the dialog box, write the above url with your API Key. Click OK.

6.gif

After loading, you will see a new table called weather?id=5128581&units=metric&appid={your API Key}.

We will need to model it. But before let’s change the table name to Curr_Weather, to get things simple.

Step 3 – model data to be imported to Power BI

As the retrieved data is in JSON format, we will need to transform it in a way we can work the data. This is, in table format. As you will notice the titles are on the left column and the data in right column. This is not good for us.

So first step is to convert into table. In the top Ribbon the button 6 should be visible. Click to convert. Next we will need to have the cells in the name column as the titles of the columns. Go to Transform -> Transpose 8.

Next step, is to use the first row as titles of the column. Click in Use First Row As Headers 7.

We have concluded the first part for modeling the data.

In the headers of the columns, you will see this button 5. This means that column data as additional data in bellow nodes. Click on each one of those columns till you have all data available. If it asked between expand and extract, choose expand.

7

And we got our first data for New York city.

Step 4 – Adding Parameters

[sociallocker id=4165]But what if we want to gt the data of other city? We will need to change the url of the source manually… Well, not exactly. April last year Power BI team added a good tool to assist in this cases. It doesn’t cover 100% the needs, but it helps a lot especially in this cases. It is called Parameters.

So, before we use this feature, we will need to create a new list, like we did for countryCode, but this time for cities ID, which we will use to make a GET request to API service.

In Query Editor, go to city_list table, on the header of ID column, right click and choose Add as a new query.

8

Now we will create our first Parameter. Go to Manage Parameters, New Parameter. In  the dialog box populate it with the following data:

  • Name field -> Country;
  • Type -> Text;
  • Suggested Values -> Query;
  • Current Value -> US;

Click OK.

First Parameters created.

9.gif

Let’s do the same for the Cities ID. By doing the same process, in the Fields use:

  • Name; City_ID;
  • Type -> Text;
  • Suggested Values -> Query -> ID;
  • Current Value -> 5128581;

10.gif

Now we have configured the two parameters, we will use the Country parameter for filter a table and the city_id parameter to get the data from API.

For the first, we will go to city_list in the query editor, click on the dropdown button dropdown_but.JPG and choose Text Filters -> Equals. In the dialog box, click on the ABC button abc_but.JPGand change to parameter parameter_but.JPGand then choose the Country parameter. You will notice data is now filtered by country, in this case, US, since is our default value.

11.gif

Let’s test it. Click close and Apply button. In the Edit Queries button, choose edit parameters. Now click in the table button in the left screen bar. check the city_list table and ID table. You will now see data is filtered according to the new country parameter value in both tables.

Now go back and change it again to US. In case you don’t see it, you can write the value. Otherwise, go back to Edit Query, countryCode table, and sort it by Ascending. Then return to edit parameters and follow the order.

So after we have used the first parameter, lets work on the second one, which will be used in the url to get the data for the desired city.

In the Edit Queries button, click data source settings. choose the API source and click Change Source. Then click advanced -> add part. Populate the fields as follows:

  • http://api.openweathermap.org/data/2.5/weather?id=
  • Click ABC button and choose City_ID;
  • &units=metric&appid=Your API Key

12

Click ok. All good? If yes proceed to the next step. Otherwise check the following solutions:

Are you receiving a warning like:

Issue 1

Formula.Firewall Query Foo references other queries, so it may not directly access a data source.

Solution

Then goto file-> Options and settings -> Options -> Privacy. Choose Always Ignore Privacy Level Settings. This will set for all files.

If you want to use it only on this exercise, then go to Options and Settings -> Current File -> Go and choose Ignore.

Use this carefully, since this policy is not advisable when using confidential data, since there might be the possibility part of data being transmitted to the target service. For more information go here.

Issue 2

If you receive a relationship matter warning.

Go to Relationships map and make sure to be as per below picture.

relationship.JPG

Now everything should be fine. If not, leave a comment and will try to assist.

 

Step 5 – Getting Forecast data from API

In this step we will follow the same procedure as for the city current weather. We will need just to use another API call. In the OWM website look for the 5 day / 3 hour forecast and check url call. It should be like:

But instead of adding the full url, we will split it as we did for the current weather with the city_id parameter.

Click Get Data -> Web -> Advanced. Then add a new part.

In the 3 fields, populate as follows:

  • http://api.openweathermap.org/data/2.5/forecast?id=
  • Change to Parameter and choose city_id
  • &units=metric&appid=your API Key goes here

forecast.JPG

We will need to model again the data, since it is in Json format. Repeating the steps as for curr_weather. Convert to table -> use first row as headlines -> extend column

Rename table to forecast.

13.gif

Now we can start building our dashboard. We will produce something identical to what is published at OWM website.

Step 6 – Creating the dashboard

Lets go to visuals gallery and download the image and text visual. In the Power BI Desktop goto visualizations and click ellipsis signal 12. Search the visuals downloads and click ok. repeat action for the second visual.

Now let’s place the visuals in the dashboard. You can use this Blank Dashboard file with titles already in place.

We will need to configure the kind of data in tables as text, numbers, dates, etc.Otherwise, we will unable to do any analytics.

Before going straight to table fields, check the API website, to understand what means each field and type of data. There are some internal data of the service which we do not require. Besides the other information, must be contextualized it in the dashboard.

So after checking the API documentation, we do know for example that:

  • dt_txt is a date and time;
  • rain.3h is a decimal number;
  • weather.description is text;
  • weather.icon is an image;

So we will need to model data on both Curr_Weather and Forecast tables. select each field and change to number or date and time depending on what is applicable.

14.gif

For the weather.icon, we will need to create a calculated column in the main view, not in the query editor mode.

What is the main difference? Because by creating a calculated column in the editor mode, will replicate data for the entire table. Data will make part of it and therefore, increase the size of the file. But if instead, we use calculated column in the main view, this will be calculated only when requested. Which will help the performance, when refreshing data.

Now, we want to show the weather icon in our dashboard. In the OWM website we understand that icons are at http://openweathermap.org/img/w/ address. And that all images are in png format. So we will need to create a column that for each icon value will add the prefix of url address and a suffix for the image format.

Select the Curr_Weather table, right click and choose to create New Column. In the Formula bar, write the following code:

icon_url = “http://openweathermap.org/img/w/”&Curr_Weather[icon]&”.png”

15

Let’s try to add it the image visual. hmmm… not loading. That’s because we have not configured this data accordingly. In order to load images into visuals, we will need to select in data category that this field is an image url.

16.gif

 Now should be working.

Step 7 – Add data fields to Visuals

Now we just need to add the data fields to visuals. If you are using the blank dashboard, you already have them in place. Otherwise, place it as you wish. Just drag and drop from visualizations tab to the dashboard.

In the forecast area, i’ve used the Line and clustered column chart and Multi-Row Card. The first will accept two different dimensions and add in the y axis left and right an auto scale, according to data values. In this case as temperature and rain values are similar, you will only see the y axis on left. When you add humidity, then you will notice on the right a different scale. In the x axis we will use the date field. In this case, by default will add it in the hierarchy format. Change it to dt_txt so it is in the continuous format.

17.gif

[/sociallocker]

In order to make labels a bit clear, change them by deleting the prefixes. Right click over the field, choose rename, change it.

If for some reason, the values showing up in the visuals are not correct, refresh data. This happens when we model fields, i.e. from text to number. After refreshing, data should be ok.

When refreshing you will see that system will load the 5 tables. Being 3 the same, id and countryCode are lists from city_list. This table is not updated frequently. So, in order to improve performance, we can say to Power BI, to not load these tables.

Goto Query Editor, select table, click in properties and uncheck “Include in report refresh” box. Repeat to the other 3 tables/lists.

Close & Apply. Now in the main view click refresh. Now only 2 tables are refreshed. Much quicker, right?

When, voilá!!! We finished the dashboard. Click on image to see live Power BI dashboard.

 

Hope you enjoyed this tutorial. Pls leave comments and links to your dashboards.

Don’t miss future tutorials and don’t forget to follow on social.

 

About the Author:

A father, a husband and a geek... Biitlog Founder, The Tech Labs founder, among other projects.

4 Comments

  1. justin March 17, 2017 at 10:52 am - Reply

    Hallo, i have been following the tutorial to get the weather sets up , but i am stuck at the level where i need to create web content and make an api call using the api key generated from openweathermap.org. i keep getting the following error message: The users was not authorized. even thus the URL used is exactly the one provided in the tutorial.

  2. Carlos Pinho March 17, 2017 at 1:03 pm - Reply

    Hi Justin,

    Can you let us know what is the url you are calling? Is like http://api.openweathermap.org/data/2.5/weather?id=5128581&units=metric&appid=yourkey

    If so, you should have no reason to get a warning like that. In case you keep getting that error, please send me your pbix file url so i can give it a look.

  3. Steve March 29, 2017 at 1:21 am - Reply

    I am having the same issue as Justin. Any additional advice? I have read that using an HTTP header is a solution, but have not successfully figured out how to do so.

  4. Carlos Pinho March 29, 2017 at 9:33 am - Reply

    Hi Steve,
    Again not sure why you are having this issue. Make sure your privacy levels are set to “Ignore Privacy Settings”. Let me know if it solves.

Leave a Reply