Unable to Query Historical NOAA Weather Data from Weather Stations

[pwares]

Hi Everyone,

For a project at work, I am trying to pull historical data from various weather stations from the NOAA and I would like to put it into a Power BI Database. My main goal is to pull data from the major cities in the Northeast USA, and to show the daily average temperatures for the last several years (let’s say a data series from 2010 to present).

I looked on the relevant NOAA website, and it was a bit overwhelming for how to pull this into Power BI: https://www.ncdc.noaa.gov/cdo-web/webservices/v2

1. I’ve made the request for a token : https://www.ncdc.noaa.gov/cdo-web/token
2. I’ve identified a sample list of weather stations that I’d like to pull data from: I just picked the airports in the 5 largest cities in the NE USA. These are the Weather Station Codes:

GHCND:USW00014732

GHCND:USW00013739

GHCND:USW00014739

GHCND:USW00094823

GHCND:USW00014765

1. Based on the advice from the NOAA website, I created a URL that should be able to pull this data with these 5 codes:

https://www.ncdc.noaa.gov/cdo-web/api/v2/datasets?stationid=GHCND:USW00014732&stationid=GHCND:USW000...

1. I went into Power BI Desktop > Get Data > Web > Basic > entered the URL

I then typed in the URL under basic, but then I encountered an error:

For whatever reason, Power BI didn’t ask for a Token to authenticate me. Is there some simple way for me to tell Power BI that I have a Token where I can pull this data from the NOAA? The token should act as an authentication, however I wasn't prompted to enter one.

Thanks so much for your help!

Peter

[PhilipTreacy · Accepted solution]

Hello @pwares

The token must be provided in the request header. I tried this and it works, just enter your own token at the top. Sample PBIX file here.

let

api_url = "https://www.ncdc.noaa.gov/cdo-web/api/v2/datasets?stationid=GHCND:USW00014732&stationid=GHCND:USW00013739&stationid=GHCND:USW00014739&stationid=GHCND:USW00094823&stationid=GHCND:USW00014765",
    token = "XXXXXXXXX",

Source = Json.Document(Web.Contents(api_url,
   [ 
     Headers = [token = token ,#"Content-Type"="application/json"]
   ]
   )
   ),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{1}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"uid", "mindate", "maxdate", "name", "datacoverage", "id"}, {"uid", "mindate", "maxdate", "name", "datacoverage", "id"})
in
    #"Expanded Column1"

Best regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking Thumbs Up.

[maltonji]

The code provided by PhilipTreacy only gives a summary for me not daily weather data

[pwares]

Excellent, thanks so much for the help.  I plugged this into Power BI Advanced Editor and was able to see the data. This is really helpful.

[v-jayw-msft]

Hi @pwares ,

Are you saying that the below verification window does not pop up?

If so you could go to Options and Settings -> Data source settings, find the datasource and clear the permission and try again.

Best Regards,

Jay