cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Import tables from multiple websites in one query?

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?

1 ACCEPTED SOLUTION

Hi nobodyukno,

 

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

 

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

 1.PNG2.PNG

You can refer to PBIX here:

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

 

Regards,

Jimmy Tao

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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"

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
Anonymous
Not applicable

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

Hi nobodyukno,

 

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

 

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

 1.PNG2.PNG

You can refer to PBIX here:

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

 

Regards,

Jimmy Tao

Anonymous
Not applicable

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.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.