Bringing in api data from Bureau of Labor statistics

[asubr]

I am trying to use the 'One or More Series with Optional Parameters' from https://www.bls.gov/developers/api_signature_v2.htm#parameters with a power bi query bringing in multiple years for multiple series id. Thanks for your help.

[lbendlin]

Sounds good. What have you tried and where are you stuck? what data range are you aiming for?

Usually you supply a contents payload to the Web.Contents() call to make it a POST rather than a GET.

[asubr]

Hi @lbendlin 

I applied this query from another post which gives back 3 years and wanted to change it to getting 10 years.

I would like it to return the series_title.

some times hits too many requests. Do I need to do a GetData in addition to this query?

let
Source = {"CUSR0000SA0","CUSR0000SAF1","CUSR0000SAF11","CUSR0000SAF111","CUSR0000SAF112","CUSR0000SAF1121","CUSR0000SAF113","CUSR0000SAF1131","CUSR0000SAF114","CUSR0000SAF115","CUSR0000SEFC","CUSR0000SEFD","CUSR0000SEFF","CUSR0000SEFG","CUSR0000SEFH","CUSR0000SEFJ","CUSR0000SEFJ01","CUSR0000SEFJ02","CUSR0000SEFK","CUSR0000SEFL","CUSR0000SEFM","CUSR0000SEFR","CUSR0000SEFS","CUSR0000SEFV","CUSR0000SEFV01","CUSR0000SEFV03","CUSR0000SEFV05"},
#"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])&"?registrationkey=key&catalog=true&startyear=2010&endyear=2020&calculations=true&annualaverage=true&aspects=true"))),
Custom = #"Added Custom"{0}[Custom],
message = Custom[message]
in
message

Thanks so much

[asubr]

@lbendlin  I had this for GetData as the first query and the 2nd query following it.

(sessionid as text) as table =>
let
Source = Json.Document(Web.Contents("https://api.bls.gov/publicAPI/v2/timeseries/data/" & sessionid & "?registrationkey=key&catalog=true&startyear=2010&endyear=2020&calculations=true&annualaverage=true&aspects=true")),

#"Converted to Table" = Record.ToTable(Source)
in
#"Converted to Table"

[lbendlin]

You said you wanted to bring in a couple of series.  According to the documentation you need to supply these as a JSON payload, not as part of the URL.

[asubr]

How do you send it as a payload?

[lbendlin]

by specifying the Content part of the options package.

Web.Contents - PowerQuery M | Microsoft Docs

• Content: Specifying this value changes the web request from a GET to a POST, using the value of the option as the content of the POST.

[asubr]

HTTP Type:	POST
URL:	https://api.bls.gov/publicAPI/v2/timeseries/data/
URL with parameters (JSON):	https://api.bls.gov/publicAPI/v2/timeseries/data/ ?registrationkey=000f4e000f204473bb565256e8eee73e& catalog=true&startyear=2010&endyear=2014&calculations=true &annualaverage=true&aspects=true
URL for Excel output with parameters:	https://api.bls.gov/publicAPI/v2/timeseries/data/ .xlsx?registrationkey=000f4e000f204473bb565256e8eee73e& catalog=true&startyear=2010&endyear=2014&calculations=true&annualaverage=true&aspects=true
Payload:	JSON Payload: {"seriesid":["Series1",..., "SeriesN"], "startyear":"yearX", "endyear":"yearY", "catalog":true|false, "calculations":true|false, "annualaverage":true|false, "aspects":true|false, "registrationkey":"995f4e779f204473aa565256e8afe73e" }
Example Payload:	{"seriesid":["WMU00140201020000001300002500", "WMU00140201020000001300002500"], "startyear":"2018", "endyear":"2018", "catalog":true, "calculations":true, "annualaverage":true, "aspects":true, "registrationkey":"000f4e000f204473bb565256e8eee73e" } It seems like the years have to passed if you look at the url with parameters (JSON) part? How do i change the query? Do i still need the 2 parts - GetData and the query as well?