I use the web query tool to import a table in the spreadsheet. Given I wanted to query the site several time, I recorded a macro to get the web query into VBA format and then use it in a loop to download the table I need.
By doing so I noticed that excel web query does something strange (to me obviously). From the data source it does a transformation for the column header by hardcoding the date. This won't be a problem if the header will be static, but given they will change this will be a problem if I leave the code as it stands.
I was wondering if there was a way to generalize in VBA the data transformation done by the web query so that it will import the data and the date correctly
You can find the code of the web query below
"let" & Chr(13) & "" & Chr(10) & " Source =
Web.Page(Web.Contents(""https://fred.stlouisfed.org/release/tables?
rid=53&eid=783269#snid=783270""))," & Chr(13) & "" & Chr(10) &
" Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) &
" #""Changed Type"" = Table.TransformColumnTypes(Data0,{{"""", type text},
{""Q3 2020"", type number}, {""Q2 2020"", type number}, {""Q3 2019"", type
number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "
#""Changed Type"""
You can determine the current column names, and create the transformation list yourself.
eg:
colNames = Table.ColumnNames(Data0),
colTypes = {type text, type number, type number, type number},
#"Changed Type" = Table.TransformColumnTypes(Data0,List.Zip({colNames,colTypes}))