Import Multiple Pages from Web with One Query in Excel

hello and welcome to this video tutorial from computer Garga comm and in this video we are going to look at how we can import multiple pages from the web with one query into our Excel spreadsheet so the previous video I did a couple of days ago was about importing data from the web nice and simple but in this example in this website I would like to import multiple pages so this is Box Office Mojo and we have the weekly box office and you can see along the top we have links for years lots of them we'll keep it simpler in this example and maybe just focus on the last five but this table we can see of the weekly box office data I would like not just for 2019 but 20 18 17 16 and 15 and with the potential to update because this is going to update obviously every week so let me start by taking a copy of the URL at the top and in our Excel spreadsheet I'm going to begin by building up a little URL table so if i zoom in on this spreadsheet and I'm going to paste that URL I just copied into cell b2 and let's quickly set up a little table of year URL I'll make those bold and the first year in that list is 2019 if we're going to look backwards and I might just type at 2018 underneath that select those two and drag it down so that we've got the last five could easily go further now we've got this link in cell b2 at the moment I'm going to get rid of that I'm going to click the clear button and clear those hyperlinks because that's going to frustrate me and then I'll just use my format painter button to quickly get rid of the look as well and if I go inside b2 we're going to turn that into a quick little formula to concatenate that first bit of the stream and where we've got 2019 I'm going to change that to a reference to the year and then concatenate that back to that last bit of string so I'm creating a URL the important thing here when we're doing imported from multiple pages like this using this technique is that there is some consistency in the URLs and we can see here that the URLs are exactly the same just we have that year in there and if I can change the year to whatever I want then we've got ourselves a URL so if I press ENTER and copy that down and here we go we have now the URL for each of those years just gonna wide that column and put this into a table format as table let's choose that lovely one okay and it's give it a name we shall call it URLs excellent got herself a table of URLs now let's import a first bunch of data so I'm going to take a copy of that first URL but any URL would work for this and data tab from web this is going to connect for us and I will paste with control V the URL and click OK this will now connect to that web page and show us a preview asking us what data we want and table 0 as I click on it you see the preview on the right the one I'm after so I'm going to click transform data at the bottom so that it takes me into the power query editor and I could make some modifications to this if I wanted one modification I might be interested in making would be the table name on the far right so I'm going to change that to movies nice simple table name for now everything else I'm quite happy with not going to focus too much on the editing at this table but if you're a familiar of power query you'll know that the sheer volume of what we could do I'm going to just close and load this to the existing worksheets gonna put it as a table just next to the information I have in a URL table and if I click OK there is a quick simple import at the moment from just one of those pages let me zoom out a bit on this now and you can see the query on the right 26 rows really really good stuff but what we want to do is edit that so that it can do all of the pages that we asked of it so I'm going to right mouse click on the query on the right and choose edit and we're going to turn that query into a function so I'm going to click on the advanced editor button at the top so that we can see the steps from our query and I'm going to write something above this let statement so I press ENTER a couple of times move into that space and I'm going to tap an open bracket type URL set parameter I want to use close bracket as table equals greater than and you can see at the bottom no syntax errors have been detected doesn't necessarily mean it's to work but it's happy with what I've written so what we have converted this into a function one last step here is you can see the web contents and in these brackets we've got the URL that we built from the spreadsheet let's delete all of that and type URL so the parameter are created as a table we're gonna put in those brackets and we're gonna feed it the URLs from a spreadsheet table but click done it's now a function prompts me to enter the parameter so I could type a year right now and that should go and fetch the data for that year but I don't just want one year I want to feed the older ones from the table let me just change the name of this query not that it's hugely important doesn't really affect it working I don't know to put FX in front of it to indicate that this is a function and I'm going to press my close and load button and it will now load it is a connection only see on the right hand side it's now a connection only it's a function and that information table was right it disappeared okay now I'm clicking inside my table because I now want to load that as a query data from table range query editor opens got the year got the URL now I want to click on my URL column and after that I'm going to add a custom column here's our custom column step this give it a name going to call it fetch movies excellent and a formula nice and easy equals FX movies I'm referencing the function Open bracket URL close bracket feed that function to URL now when say URL here because I'll Noah mentioned it a few times we're now talking about the column on the table the information net column click OK information for data privacy let's continue and walk through these steps ignore the privacy level that save this any minute here we go we have ourselves a column fetch movies now remember in a function as table so these are tables for each year if I click the button at the top to expand this and remove the checkbox to keep the original column name as prefix very unusual for people to need that very few instances which columns do we want I'm going to leave it as all of them but you can easily exclude or add whichever columns you want from that table right now if I click OK Wow look at that every single year now once again we have the opportunity to make more changes and maybe one of them is to get rid of this URL I don't care about you oh I needed it in order to fetch the data but I don't need it in the finished result I do want the year though because in the box office mojo data they have the week but I don't mention the year so another good thing about us building that table to begin with is that and now have the year in our data and I'm quite happy having them as separate columns you can even combine them right now if you wanted to that is exactly what I want now so I'm really happy I'm just going to go and load that in close and load to existing worksheet let's put it where the other one was click ok it's loading them up on the right hand side there we go 235 rows of data from different pages of that website and if I open this and ran it next week week after the week after this will continue to do its job just as a brief demonstration of that because I don't want to wait a week in order to refresh this I'm going to enter 2014 maybe want one extra year there's a press enter table expands formula runs down it data tap refresh all here we go look at it loaded on the right hand side 287 rows of data let's scroll down where's my 2014 oh there it is or a 2014 data so here we have a query important data from multiple pages it did use that consistency of the URL which is important you never really know unless you have control over it that the structure of these web pages and the structure of the URLs but as long as there is a consistency we can import these pretty easily using power query thank you for watching I hope you find this video useful please check out some of our other video tutorials on our YouTube channel and come check us out at computer Garga com you

Channel: Computergaga

Views: 80,471

Rating: 4.9734073 out of 5

Keywords: Microsoft Excel, Power Query, Import from Web, Computergaga, Web Scaping, Excel Tips, excel power query tutorial, import from web to excel, import from website to excel, alan murray, import multiple pages from web, import multiple pages from web with one query in excel, import multiple web pages into excel

Id: STjBoS1rQuQ

Length: 12min 40sec (760 seconds)

Published: Fri Jul 12 2019