Import Data From Web - Flexible Column Names

[jon_sim]

Hi all,

I'm pretty new to all this. I am working on a sheet that imports data from the web. So just for reference, I followed this tutorial:

I'm basically pulling in football data from the web and the "URL" tables that have been created change by a vlookup function with the idea that it would then pull in another teams data when the web URL is changed and the data refreshed. So far so good. It all works fine the first time round and then when it changes I get the following error:

"[Expression. Error] The column 'For Bournemouth Date' of the table wasn't found."

I have managed to trace it back to the code in the advanced editor below:

(URL) as table =>

let
    Source = Web.Page(Web.Contents(URL)),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"For Bournemouth Date", type date}, {"For Bournemouth Time", type time}, {"For Bournemouth Round", type text}, {"For Bournemouth Day", type text}, {"For Bournemouth Venue", type text}, {"For Bournemouth Result", type text}, {"For Bournemouth GF", Int64.Type}, {"For Bournemouth GA", Int64.Type}, {"For Bournemouth Opponent", type text}, {"For Bournemouth Att", Int64.Type}, {"Pass Types Live", Int64.Type}, {"Pass Types Dead", Int64.Type}, {"Pass Types FK", Int64.Type}, {"Pass Types TB", Int64.Type}, {"Pass Types Sw", Int64.Type}, {"Pass Types Crs", Int64.Type}, {"Pass Types TI", Int64.Type}, {"Pass Types CK", Int64.Type}, {"Corner Kicks In", Int64.Type}, {"Corner Kicks Out", Int64.Type}, {"Corner Kicks Str", Int64.Type}, {"Outcomes Cmp", Int64.Type}, {"Outcomes Off", Int64.Type}, {"Outcomes Blocks", Int64.Type}, {"Match Report", type text}})
in
    #"Changed Type"

Obviously the error occurs when I change it to say Bristol City because the code it is looking on the web for Bournemouth. Could anyone help me so that "For Bournemouth Date" (and all the others) pull in the data with whatever the column header is from the web source rather than looking for what is between the quotation marks?


Thanks in advance for any comments!

 

[smozgur]

Welcome to the MrExcel Message Board!

If the column order and types are the same for each URL, which is supposed to be unless different URLs return data in different column structures, then you can do the type conversion by using the column indexes instead of their names. Take a look at the following sample code (simply copy and paste into a blank query in advanced editor - don't worry about the Source, I just used sample data):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEYkN9I30jAyMTENPQysBAKVYnWskIyEsCS5rCJY1gksZAXjIQGyFJGoMlYwE=", BinaryEncoding.Base64), Compression.Deflate)), {"a", "b", "c", "d"}),
    ColNames = Table.ColumnNames(Source),
    Result = Table.TransformColumnTypes(Source, {
        {ColNames{0}, Int64.Type},
        {ColNames{1}, type text},
        {ColNames{2}, type datetime},
        {ColNames{3}, type time}
    })
in
    Result

More automated implementation of the same logic instead of writing the transformation list manually could be the following.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEYkN9I30jAyMTENPQysBAKVYnWskIyEsCS5rCJY1gksZAXjIQGyFJGoMlYwE=", BinaryEncoding.Base64), Compression.Deflate)), {"a", "b", "c", "d"}),
    Types = {Int64.Type, type text, type datetime, type time},
    Transformation = List.Accumulate(
        {0..Table.ColumnCount(Source) - 1},
        {},
        (s, c) => s & {{Table.ColumnNames(Source){c}, Types{c}}}
    ),
    Result = Table.TransformColumnTypes(Source, Transformation)
in
    Result

See, I know the columns and corresponding column types. Therefore, I can create the Types list by using those types. Of course it must be in the same order with the corresponding columns. Then I use the List.Accumulate function to generate the Transformation list by using the column names in the source data and the Types list that I manually created to be used in the Table.TransformColumnTypes function (second parameter).

I know it looks like a very indirect way of doing this, but I often use this method. If we want to apply the same logic into your data structure in your code:

(URL) as table =>
let
    Source = Web.Page(Web.Contents(URL)),
    Data0 = Source{0}[Data],
    Types = {type date, type time, type text, type text, type text, type text, Int64.Type, Int64.Type, type text, Int64.Type, Int64.Type, Int64.Type, Int64.Type, Int64.Type, Int64.Type, Int64.Type, Int64.Type, Int64.Type, Int64.Type, Int64.Type, Int64.Type, Int64.Type, Int64.Type, Int64.Type, type text},
    Transformation = List.Accumulate(
        {0..Table.ColumnCount(Data0) - 1},
        {},
        (s, c) => s & {{Table.ColumnNames(Data0){c}, Types{c}}}
    ),
    Result = Table.TransformColumnTypes(Data0, Transformation)
in
    Result