Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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"
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"
please provide a sample value for [Column1]
Column1 is the seriesid, WPU1017
User | Count |
---|---|
18 | |
5 | |
2 | |
1 | |
1 |
User | Count |
---|---|
23 | |
4 | |
3 | |
2 | |
1 |