How to: Build a Weather Dashboard with API and Parameters 01-18-2017 08:44 AM - last edited 01-18-2017 09:24 AM

[cpinho]

Hi, just created this dashboard connecting local data and 2 API calls for current weather and 5 days forecast.

This is my first public dashboard and tutorial. Hope you enjoy.

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

eyJrIjoiMTM1ZTVlNjItNmRiNC00NjQzLTg2NWQtNDM1ZmYwYzM5YTllIiwidCI6ImQ1MWRlNTk2LTQ1YTAtNGFhNS1hMGQ4LTQ3ZDFjOTU4YWEzMiIsImMiOjh9

[cferraz_hemav]

Hi friends!

I'd like to have a report that uses lat and long from one source and uses these parameters to perform one query to the weather provider. In addition the source of lat-long could vary from one week to another so I want this to be dynamic. Is there a way to do this?
Let's say we have this table as an input:

element_id | element_name | long | lat

48087 PORTELLA31086 0.5484911794190 42.2340858391
48088 OBAGA310811 0.5452424355600 42.2364303825
48089 PAUL310812 0.5429963973033 42.2362877305
48090 BOGA Y ROCA310814 0.5564795968214 42.2347207445
48091 MASIA310818 0.5518679290601 42.2373370319

Thanks!

[cpinho]

If you want to store historical data, than you need to do this outside PBI. You use services as Flow or IFTT (several weather providers are available). You store the data in a excel or txt, and use it as a source to your pbi file too.

[markive]

Hi @cferraz_hemav ,

I'm not sure you will be able to configure an API request to a weather API to be made dynamically as a Direct Connect datasource in PowerBI.

Therefore the way I would do it is to create a Microsoft Flow or Logic App to loop through your dataset of lat/lng and then get the weather data and save it to a datasource like a Google Sheet or database. Then you will just join this extra datasource into your model. You will also have historical data for all these coordinates. You will just need to make sure yo don't hit API limits.

[cosminc]

Hi

can you help me please? i'm a little bit stuked

on the step with the import of api in Power BI i have an issue, please see the attachement.

it apprears after the step Get Data-Web-and paste the link http://api.openweathermap.org/data/2.5/weather?id=5128581&units=metric&appid={key id}

Thanks a lot!

Cosmin

[cpinho]

Hello cosminc

You should chose the web API , so that the key you are parsing on the URL is authentication you are going to use.

Let me know if it helps.

Best

Carlos

[cosminc]

Hi Carlos

first of all sorry that i give you feedback so late, i was busy with other projects.

the API key wasn't put right by me, the support from the site gave me the right syntax.

now i want to take data - historical (last 2 weeks)+5 day forecast+current day

do you have any idea how to make import pictures - from what columns and how to to this in power bi

i want to obtain for last 2 weeks+current day+3 days forecast - a picture for each day with sun/rain like on tv:) and bellow to put daily temperature and precipitations (% form 100% chances) or maybe you have a suggestion

thanks

Cosmin

[cosminc]

I found on topic an example and understood how to solve my issues.

the main issue is that i need historical data last 2 weeks and on openweathermap.com are not free

[musicbydannyd]

It's pretty humid in NY! 😉

[cpinho]

Indeed! over 100% 😉

Thank you for enjoying.

[musicbydannyd]

Would you be interested in swapping one of my weather .pbix file (using wunderground.com) for yours?

[musicbydannyd]

Your blog is restricted at my company, so I'm posting my weather query file here for all to enjoy! 🙂 Sorry for the extra (bonus?) tables as this was a part of a golf rounds report.

To use weather in your area (I beleive it's US only), replace 'KGAEATON6' in the query text with the station ID from your local station... find it at www.wunderground.com (I beleive it's US only) by typing in your city/state or zip code.

[cpinho]

Hi 

I've sent you via email the pbix file. Hope it helps. In the meantime, checking your file. It seems is returning some errors. Believe is due to relationships. Although hadn't yet time to check it closely. Will get back to you ASAP.

Best

Carlos

[asanghi]

Whenever I enter the data source (e.g., http://api.openweathermap.org/data/2.5/forecast/city?id=4922388&APPID={xyz}) I get a prompt with 'Access web content The user was not authorized'? Do you know why? I entered my api key correctly. Screenshot attached. 

[cpinho]

Hi asanghi,

can you publish you pbix file pls?

[Robbief]

Could you Publish the PBIX file for us to re-use?

[cpinho]

Hi,

Sure. Here it is.

Let me know.

Best

Carlos Pinho

biitlog.com

[karinaruess]

Hey, do you hace the city list ?

Im trying to build the dashboard for my country but the city list isnt available anymore, it would help me alot if you still have the complete city list!

thanks

[cpinho]

Hi

I guess the URL has changed, but it still accessible. Please go to http://bulk.openweathermap.org/sample/ and choose the citylist JSON archive. 

Hope it helps.

Best

Carlos

[cosminc]

Hi,

did you how to store the weather data and to create a historical base?

Many thanks,

Cosmin

[payal4]

Hello Carlos Pinho,

After changing the data source as API source, I'am getting an error "We couldnt authenticate with the credentials provided".

I have changed the privacy settings also for the current file.

Please help