You are Here:Home>>Business>>BI>>Creating a Weather Dashboard with Power BI and API

Creating a Weather Dashboard with Power BI and API

Carlos Pinho
By | 2017-09-07T12:35:50+00:00 Jan 18, 2017|BI|

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

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 allows adding 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 the community to feed them with ideas. You can participate in this platform, by either registering your idea or supporting others.

There are other BI visual analytic tools in the market and some are great as Power BI, but most of them do not offer such a large capacity to develop great dashboard with their free versions. This why we are using Power BI, has there are lots of features which can be used to build amazing dashboards.

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:


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 below. 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:

= [nm]&","&[countryCode]

Adding Custom Column

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 the country, instead of getting all the cities IDs. We need to remove duplicates, as this table will act as the source in relationships.

Adding List

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.

Power BI Editing Relationships

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:{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:{ your API Key goes here }

Open Weather Map API

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.

Getting Data from API

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.

List into Table

And we got our first data for New York city.

Step 4 – Adding Parameters

But what if we want to gt the data of another 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 to Add as a new query.

Column to List

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.

Power BI Parameter

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;


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.


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:

  • Click ABC button and choose City_ID;
  • &units=metric&appid=Your API Key


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.


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 for 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.


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: API Key goes here

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:

  • Change to Parameter and choose city_id
  • &units=metric&appid=your API Key goes here


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.


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.


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 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 = “”&Curr_Weather[icon]&”.png”


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.


 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.


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 have finalized our Weather Dashboard with Power BI and API from OpenWeatherMap.


Have you enjoyed this tutorial? If so, supports us by sharing this article or Become a Patron!

About the Author:

Carlos Pinho
A father, a husband and a geek... Carlos was the founder of projects like The Tech Labs and Flash Enabled Blog. He is the founder of TekTuts He is passionate about technologies. Their main skills are in analytics, transport & logistics, business administration. He also writes about programming resources, trends, strategy and web development.