Trying to connect to Bureau of Labor Statistics, but stuck on the URL query

[Anonymous]

I've actually got the connection going, but I need to provide multiple (what the Burear of Statistics calls) "sessionids". I can provide 1 session ID and get the data, which is great, but I need more than one, which is what their API has.

Here's my query right now:

let
    Source = Json.Document(Web.Contents("https://api.bls.gov/publicAPI/v2/timeseries/data/LAUCN281070000000003?registrationkey=abc123def345ghi678")),
    Results = Source[Results]
in
    Results

The highlighted portion is the "sessionid", but I need to provide multiple sessionid's. It can't be a field in the URL (i.e. &sessionid=LAUCN281070000000003,ABCD271728372818,ETC273282828847 and so on). It has to be a part of the main URL (pardon my novice lingo).

It all seems pretty straightforward in the API information for BLS (Burear of Labor Statistics) here: https://www.bls.gov/developers/api_signature_v2.htm#parameters

But I've tried everything I can think of (i.e. "https://api.bls.gov/publicAPI/v2/timeseries/data/&"LAUCN281070000000003,ABCD271728372818,ETC273282828847"&?registrationkey=abc123def345ghi678

All to no avail.

I know this is rather specific, but any advice would be greatly appreciated...

Thanks!

[Anonymous · Accepted solution]

Thank you @v-yuezhe-msft,

It looks like it's almost working, but not quite. I believe it has to do with the way the data is navigated/extracted in the query (I'm not sure if I'm explaining that correctly).

What's happening is that I'm getting an error:

Expression.Error: We cannot convert a value of type Record to type Table.
Details:
    Value=Record
    Type=Type

When I connected to this data providing just 1 seriesid (which worked), the navigation is different than converting to a table in the beginning. I actually expand the list out first. Here's the query of it working with just 1 series id:

let
    Source = Json.Document(Web.Contents("https://api.bls.gov/publicAPI/v2/timeseries/data/LAUCN281070000000003?registrationkey=a7f99f6f55de46...")),
    Results = Source[Results],
    series = Results[series],
    series1 = series{0},
    data = series1[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"year", "period", "periodName", "value", "footnotes"}, {"Column1.year", "Column1.period", "Column1.periodName", "Column1.value", "Column1.footnotes"}),
    #"Expanded Column1.footnotes" = Table.ExpandListColumn(#"Expanded Column1", "Column1.footnotes"),
    #"Expanded Column1.footnotes1" = Table.ExpandRecordColumn(#"Expanded Column1.footnotes", "Column1.footnotes", {"code", "text"}, {"Column1.footnotes.code", "Column1.footnotes.text"})
in
    #"Expanded Column1.footnotes1"

I'm not sure how to change the code you provided where it creates a table, and instead use the navigation to expand the list, but I'm not sure how to do that.

I'm actually providing the real API above because this is public data from the BLS. The seriesid's I'm trying to work with are: LAUCN281070000000003 and CEU0800000003

The API information for the BLS that I'm referencing is here: https://www.bls.gov/developers/api_signature_v2.htm#multiple

You should actually be able to connect to this and see what I'm working with using all the info above...

Thanks again for the reply and any further help you can provide!

[v-yuezhe-msft]

@Anonymous,

Firstly, add a new blank query in Power BI Desktop, cope the following code in Advanced Editor of the query.

(sessionid as text) as table => 
let
    Source = Json.Document(Web.Contents("https://api.bls.gov/publicAPI/v2/timeseries/data/" & sessionid & "?registrationkey=abc123def345ghi678")),

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

Secondly, rename the function to GetData.

Thirdly, add another blank query, copy the following code in Advanced Editor of the query.

let
    Source = {"LAUCN281070000000003","ABCD271728372818","ETC273282828847"},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "SessionID"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each GetData([SessionID])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Value"}, {"Custom.Value"})
in
    #"Expanded Custom"

Regards,
Lydia

[knotpc]

Your solution saved me hours. Thanks so much. 

[Anonymous · Accepted solution]

Thank you @v-yuezhe-msft,

It looks like it's almost working, but not quite. I believe it has to do with the way the data is navigated/extracted in the query (I'm not sure if I'm explaining that correctly).

What's happening is that I'm getting an error:

Expression.Error: We cannot convert a value of type Record to type Table.
Details:
    Value=Record
    Type=Type

When I connected to this data providing just 1 seriesid (which worked), the navigation is different than converting to a table in the beginning. I actually expand the list out first. Here's the query of it working with just 1 series id:

let
    Source = Json.Document(Web.Contents("https://api.bls.gov/publicAPI/v2/timeseries/data/LAUCN281070000000003?registrationkey=a7f99f6f55de46...")),
    Results = Source[Results],
    series = Results[series],
    series1 = series{0},
    data = series1[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"year", "period", "periodName", "value", "footnotes"}, {"Column1.year", "Column1.period", "Column1.periodName", "Column1.value", "Column1.footnotes"}),
    #"Expanded Column1.footnotes" = Table.ExpandListColumn(#"Expanded Column1", "Column1.footnotes"),
    #"Expanded Column1.footnotes1" = Table.ExpandRecordColumn(#"Expanded Column1.footnotes", "Column1.footnotes", {"code", "text"}, {"Column1.footnotes.code", "Column1.footnotes.text"})
in
    #"Expanded Column1.footnotes1"

I'm not sure how to change the code you provided where it creates a table, and instead use the navigation to expand the list, but I'm not sure how to do that.

I'm actually providing the real API above because this is public data from the BLS. The seriesid's I'm trying to work with are: LAUCN281070000000003 and CEU0800000003

The API information for the BLS that I'm referencing is here: https://www.bls.gov/developers/api_signature_v2.htm#multiple

You should actually be able to connect to this and see what I'm working with using all the info above...

Thanks again for the reply and any further help you can provide!

[Anonymous]

how do i bring in more years worth of data?

[Tgalvin]

Append this to the end of your registration ID, it will add these parameters to your query. 

"&startyear=2002&endyear=2019"

[Syndicate_Admin]

Thanks Tgalvin . The code worked well, however, I am not sure why the startyear is not working . Showing expression error

[khoudary]

Thanks Tgalvin . The code worked well, however, I am not sure why the startyear is not working . Showing expression error

[Anonymous · Accepted solution]

Hi @v-yuezhe-msft,

Actually, I'm happy to say that with your help and some previous help I received from @ImkeF I was able to figure this out. It just took combining both of your ideas. FYI: Here's the final query:

let
 Source = {"LAUCN281070000000003","CEU0800000003"},
#"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=abc123def4..."))),
    #"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"