0

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"""
Marco_sbt
  • 309
  • 1
  • 12

1 Answers1

1

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}))
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • thanks for your input. Though I am not a VBA expert especially with PowerQuery (hence I recorded the Macro) therefore I sure where ti stick the code you are suggesting or how to modify the code I have posted. Can you help by any chance with some more imput? Thanks, I really appreciate @RonRosenfeld – Marco_sbt Nov 22 '20 at 11:31
  • 1
    @Marco_sbt From what you've posted (which, as I'm sure you've noted, is pretty hard to read with all the non-printing characters included), I would guess that you would replace your `#"Changed Type"` line with the lines in my code. Whether you do that in VBA or in the Power Query editor depends on where you got those lines from. – Ron Rosenfeld Nov 22 '20 at 12:06