Import tables from multiple websites in one query?

[Anonymous]

So I have the following code to pull in a table from a website:

let
    Source = Web.Page(Web.Contents("https://www.baseball-reference.com/play-index/batter_vs_pitcher.cgi?batter=arenano01")),
    Data1 = Source{1}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data1,{{"Name", type text}, {"PA", Int64.Type}, {"AB", Int64.Type}, {"H", Int64.Type}, {"2B", Int64.Type}, {"3B", Int64.Type}, {"HR", Int64.Type}, {"RBI", Int64.Type}, {"BB", Int64.Type}, {"SO", Int64.Type}, {"BA", type number}, {"OBP", type number}, {"SLG", type number}, {"OPS", type number}, {"SH", Int64.Type}, {"SF", Int64.Type}, {"IBB", Int64.Type}, {"HBP", Int64.Type}, {"GDP", Int64.Type}, {"missG", type text}})
in
    #"Changed Type"

If I wanted to do the same thing for say 20-30 exactly similar webpages but they have different data, how could I do this on one query and append the results together rather than having 30 different queries all doing the same thing. 

Is it possible to do this in Power BI or should I resort to my VBA skills to pull in all te data into Excel?

[Greg_Deckler]

I wrote a blog article a long time ago called "Merge Queries with M" or something like that. Essentially, you just need to do something like:

let
    Source1 = Web.Page(Web.Contents("https://www.baseball-reference.com/play-index/batter_vs_pitcher.cgi?batter=arenano01")),
    Data1 = Source1{1}[Data],
    #"Changed Type1" = Table.TransformColumnTypes(Data1,{{"Name", type text}, {"PA", Int64.Type}, {"AB", Int64.Type}, {"H", Int64.Type}, {"2B", Int64.Type}, {"3B", Int64.Type}, {"HR", Int64.Type}, {"RBI", Int64.Type}, {"BB", Int64.Type}, {"SO", Int64.Type}, {"BA", type number}, {"OBP", type number}, {"SLG", type number}, {"OPS", type number}, {"SH", Int64.Type}, {"SF", Int64.Type}, {"IBB", Int64.Type}, {"HBP", Int64.Type}, {"GDP", Int64.Type}, {"missG", type text}})

    Source2 = Web.Page(Web.Contents("https://www.baseball-reference.com/play-index/batter_vs_pitcher.cgi?batter=arenano01")),
    Data2 = Source2{1}[Data],
    #"Changed Type2" = Table.TransformColumnTypes(Data2,{{"Name", type text}, {"PA", Int64.Type}, {"AB", Int64.Type}, {"H", Int64.Type}, {"2B", Int64.Type}, {"3B", Int64.Type}, {"HR", Int64.Type}, {"RBI", Int64.Type}, {"BB", Int64.Type}, {"SO", Int64.Type}, {"BA", type number}, {"OBP", type number}, {"SLG", type number}, {"OPS", type number}, {"SH", Int64.Type}, {"SF", Int64.Type}, {"IBB", Int64.Type}, {"HBP", Int64.Type}, {"GDP", Int64.Type}, {"missG", type text}})

   #"TableAppend" = Table.Append(Source1, Source2)

in
    #"TableAppend"

[Anonymous]

Thanks for the reply. I get an error stating that the name "Table.Append" was not recognized. Excuse my ignornace but I'm not too familiar with M code.

Thanks

[v-yuta-msft · Accepted solution]

Hi nobodyukno,

To achieve your requirement, you can use Table.Combine instead.

#"TableCombined" = Table.Combine({#"Changed Type1", #"Changed Type2"})

 

You can refer to PBIX here:

https://www.dropbox.com/s/hkv1dspegfp87sx/For%20nobodyukno.pbix?dl=0

Regards,

Jimmy Tao

[Anonymous]

Thank you that worked!

I have another M coding question as I'm used to VBA where I can declare variables. 

Now that with the above code I can bring multiple sets of data into one table, is there anyway I can distinguish the sets of data by adding a column? Is it possible to add a column to each query and have that column enter a variable from the code above (at worst enter a sequential number and I can setup mapping to further identify)? If it's possible to assign a variable in M code, then I would want to set the variable up as the last bit of text from the URL, so "arenano" in the example above. Then it would put that variable in a new column for each query and then combine together at the end with the one column havin the identifer for each query pull.

Thanks again for the help.