Data from BLS Server online - how to get into powerbi

[morganmcqueen26]

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 parameter since the website only loads 2020 to 2022 unless you manually change it on the filter on the page.  Please help its urgent!

[Vijay_A_Verma · Accepted solution]

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]

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.