Connecting to Bureau of Labor Statistics, getting error

[Anonymous]

I am trying to pull PPI information from the BLS' public API, I was successful in connection once this morning, however upon refresh I got the following error: "DataSource.Error: The underlying connection was closed: Unable to connect to the remote server."

 am using a custom query modeled on another solution I found on this forum. The query is as follows: 

let
Source = {"WPU1017"},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Json.Document(Web.Contents("https://api.bls.gov/publicAPI/v2/timeseries/data/"&Text.From([Column1])))),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"status", "responseTime", "message", "Results"}, {"status", "responseTime", "message", "Results"}),
#"Expanded Results" = Table.ExpandRecordColumn(#"Expanded Custom", "Results", {"series"}, {"series"}),
#"Expanded series" = Table.ExpandListColumn(#"Expanded Results", "series"),
#"Expanded series1" = Table.ExpandRecordColumn(#"Expanded series", "series", {"seriesID", "data"}, {"seriesID", "data"}),
#"Expanded data" = Table.ExpandListColumn(#"Expanded series1", "data"),
#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"year", "period", "periodName", "value", "footnotes"}, {"year", "period", "periodName", "value", "footnotes"})
in
#"Expanded data1"

I am having issues with another web query so I don't think it has anything to do with the query itself. I have already disabled Certificate Revocation check, to no avail. 

Any thought on what could be causing this issue?

[lbendlin · Accepted solution]

Yes, sorry, my bad.   I have run your query (with some slight modifications) and cannot find any issue.  I think it may have been either a temporary server issue or an API call limit that you may have exceeded.

let
Source = #table({"Column1"},{{"WPU1017"}}),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Json.Document(Web.Contents("https://api.bls.gov/publicAPI/v2/timeseries/data/" & [Column1]))),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Results"}, {"Results"}),
    #"Expanded Results" = Table.ExpandRecordColumn(#"Expanded Custom", "Results", {"series"}, {"series"}),
    #"Expanded series" = Table.ExpandListColumn(#"Expanded Results", "series"),
    #"Expanded series1" = Table.ExpandRecordColumn(#"Expanded series", "series", {"data"}, {"data"}),
    #"Expanded data" = Table.ExpandListColumn(#"Expanded series1", "data"),
    #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"year", "period", "periodName", "latest", "value", "footnotes"}, {"year", "period", "periodName", "latest", "value", "footnotes"}),
    #"Expanded footnotes" = Table.ExpandListColumn(#"Expanded data1", "footnotes"),
    #"Expanded footnotes1" = Table.ExpandRecordColumn(#"Expanded footnotes", "footnotes", {"code", "text"}, {"code", "text"})
in
    #"Expanded footnotes1"

[lbendlin]

please provide a sample value for [Column1]

[Anonymous]

Column1 is the seriesid, WPU1017