Bureau of Labor Statistics issues transforming table

mdinius — Thu, 16 Mar 2023 16:07:21 GMT

I've been searching high and low and can't seem to figure out how to transform the data correctly. I've tested the theory in excel, but it doesn't look like there's a direct link between the "Applied Steps" transforming from Excel versus building into the query directly.

Here is the data set I'm working with: https://data.bls.gov/timeseries/WPU057303

I've tried the "GetData" approach as many have listed.

(sessionid as text) as table => let Source = Json.Document(Web.Contents("https://api.bls.gov/publicAPI/v2/timeseries/data/" & sessionid & "?registrationkey=abcdefg")), #"Converted to Table" = Record.ToTable(Source) in #"Converted to Table"

Here is the second query:

let Source = #table({"Column1"},{{"WPU057303"}}), #"Added Custom" = Table.AddColumn(Source, "Custom", each Json.Document(Web.Contents("https://api.bls.gov/publicAPI/v2/timeseries/data/" & [Column1]))), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Results"}, {"Results"}), #"Expanded Results" = Table.ExpandRecordColumn(#"Expanded Custom", "Results", {"series"}, {"series"}), #"Expanded series" = Table.ExpandListColumn(#"Expanded Results", "series"), #"Expanded series1" = Table.ExpandRecordColumn(#"Expanded series", "series", {"data"}, {"data"}), #"Expanded data" = Table.ExpandListColumn(#"Expanded series1", "data"), #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"year", "period", "periodName", "latest", "value", "footnotes"}, {"year", "period", "periodName", "latest", "value", "footnotes"}), #"Expanded footnotes" = Table.ExpandListColumn(#"Expanded data1", "footnotes"), #"Expanded footnotes1" = Table.ExpandRecordColumn(#"Expanded footnotes", "footnotes", {"code", "text"}, {"code", "text"}), #"Sorted Rows" = Table.Sort(#"Expanded footnotes1",{{"year", Order.Ascending}}) in #"Sorted Rows"

Which yields an incomplete data set:

These are the steps taken importing the excel file and transforming:

let Source = Excel.Workbook(File.Contents("File.xlsx"), null, true), #"BLS Data Series_Sheet" = Source{[Item="BLS Data Series",Kind="Sheet"]}[Data], #"Removed Top Rows" = Table.Skip(#"BLS Data Series_Sheet",11), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Year", Int64.Type}, {"Jan", type number}, {"Feb", type number}, {"Mar", type number}, {"Apr", type number}, {"May", type number}, {"Jun", type number}, {"Jul", type number}, {"Aug", type number}, {"Sep", type number}, {"Oct", type number}, {"Nov", type number}, {"Dec", type number}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Year"}, "Attribute", "Value"), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Year", type text}}, "en-US"),{"Year", "Attribute"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"), #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged", "Date"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Series ID", each "WPU057303"), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Series ID", "Date", "Value"}) in #"Reordered Columns"

which gives me this result:

I'm able to establish the connection via API, but I don't quite understand how to transform the data to at least the native table to then start performing the translation to a list. By native table, I'm describing this:

Appreciate the help!

Re: Bureau of Labor Statistics issues transforming table

mdinius — Thu, 16 Mar 2023 20:58:26 GMT

I've made some positive progress on this and better understand how to expand the data out, but I'm having some other issues. Is there a limit on the number of years that can be pulled from BLS? I set my start date as 1990, but it only pulled 20 years of data. If I change it to 1980, then it pulls through 1999, etc. Is the "GetData" function limiting my ability to pull more than 20 years?

Here is my current query and output:

let Source = {"WPU057303"}, #"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 Table" = Table.ExpandTableColumn(#"Added Custom","Custom",{"Value"}), #"Removed Top Rows" = Table.Skip(#"Expanded Table",3), #"Expanded Record" = Table.ExpandRecordColumn(#"Removed Top Rows","Value",{"series"}), #"Expanded List" = Table.ExpandListColumn(#"Expanded Record","series"), #"Expanded series" = Table.ExpandRecordColumn(#"Expanded List","series",{"seriesID","data"}), #"Expanded data" = Table.ExpandListColumn(#"Expanded series","data"), #"Expanded Records" = Table.ExpandRecordColumn(#"Expanded data","data",{"year","period","periodName","value"}) in #"Expanded Records"

(sessionid as text) as table => let Source = Json.Document(Web.Contents("https://api.bls.gov/publicAPI/v2/timeseries/data/" & sessionid & "?registrationkey=abcdefg123&startyear=1980&endyear=2022")), #"Converted to Table" = Record.ToTable(Source) in #"Converted to Table"

topic Re: Bureau of Labor Statistics issues transforming table in Developer

Bureau of Labor Statistics issues transforming table

Re: Bureau of Labor Statistics issues transforming table