Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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!

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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!

View solution in original post

Anonymous
Not applicable

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"

View solution in original post

8 REPLIES 8
v-yuezhe-msft
Employee
Employee

@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"

1.JPG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Your solution saved me hours. Thanks so much. 

Anonymous
Not applicable

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
Not applicable

how do i bring in more years worth of data?

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

 

"&startyear=2002&endyear=2019"

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

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

Anonymous
Not applicable

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"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors