Bureau of Labor Statistics issues transforming table

[mdinius]

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!

[mdinius]

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"