Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.