Forum Discussion

morganmcqueen26's avatar
morganmcqueen26
New Member
4 years ago
Solved

Data from BLS Server online - how to get into powerbi

I'm trying to link BLS Data Viewer  to powerbi via the web option.  It works great but only pulls data from 2020 to 2022 and I need it to pull from 2019 to 2022.  I'm not sure how to change this para...
Need Help
Tips and Tricks
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

     

     

    let
	url = "https://api.bls.gov/publicAPI/v1/timeseries/data/",
	body = "{""seriesid"":[""WPU10250239""],
            ""startyear"":""2009"", 
            ""endyear"":""2012""
            }",
	response = Json.Document(Web.Contents(url,[Headers=[#"Content-Type"="application/json"],Content=Text.ToBinary(body)])),
    data = Record.ToTable(response){3}[Value][series]{0}[data],
    #"Expanded Column1" = Table.ExpandRecordColumn(Table.FromList(data, Splitter.SplitByNothing()), "Column1",{"year", "period", "periodName", "value", "footnotes"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"footnotes"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"year", Order.Ascending}})
in
    #"Sorted Rows"

     

     

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

 

 

let
	url = "https://api.bls.gov/publicAPI/v1/timeseries/data/",
	body = "{""seriesid"":[""WPU10250239""],
            ""startyear"":""2009"", 
            ""endyear"":""2012""
            }",
	response = Json.Document(Web.Contents(url,[Headers=[#"Content-Type"="application/json"],Content=Text.ToBinary(body)])),
    data = Record.ToTable(response){3}[Value][series]{0}[data],
    #"Expanded Column1" = Table.ExpandRecordColumn(Table.FromList(data, Splitter.SplitByNothing()), "Column1",{"year", "period", "periodName", "value", "footnotes"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"footnotes"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"year", Order.Ascending}})
in
    #"Sorted Rows"

 

 

 

kmsmith1108's avatar
kmsmith1108
Frequent Visitor
2 years ago

I tried this solution and it worked perfectly for a few hours.   

 

Initially, all I did was Get Data and Close and Apply, then a simple rinse and repeat with additional SeriesIDs and varying time spans.  What was interesting is that I was never prompted to enter my API Key from BLS.gov.

 

Now, the next day, every attempt to refresh or "Close and apply" returns the same error message.

 

"The field 'series' of the record wasn't found."

 

I can see all the data in the Power Query Editor, but I am not able to Close and Apply and there is no field labeled "Series".

 

Are you able to help.