Forum Discussion
Unable to Query Historical NOAA Weather Data from Weather Stations
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
- I’ve made the request for a token : https://www.ncdc.noaa.gov/cdo-web/token
- 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 |
- Based on the advice from the NOAA website, I created a URL that should be able to pull this data with these 5 codes:
- 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
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.
4 Replies
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
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.
