We will need to edit this code to the following. This will allow us to edit the code that Excel has created to extract the data from this URL.
Go to the View tab and press the Advanced Editor button. Now we can edit our query to turn it into a query function. This will be the name we call to use our query function later on. A preview of our selected data will appear.A list of tables available to import from the webpage will be listed.If you’re working with Excel 2013 or earlier via the add-in, then this will be found under the Power Query tab.Įxcel will connect with the page and the Navigator dialog box will open. Head to the Data tab in the ribbon and press the From Web button under the Get & Transform section. This way we can apply the query to each URL in a list of all the URL’s. We will then turn this into a function query where the input is an event page URL. Create a Query Functionįirst, we will create a query to extract the data on one page. If the number of pages was any larger, you might be better off using another tool like Python, but we’re going to be using Power Query. This is the data I want to get, but from all 400+ events listed in the past event section. If you look at a few more events, you’ll notice the structure is the exact same and they all have a results table. If you click on one of the events you’ll see a results table. If you visit the Wikipedia page for UFC events there’s a table of Past Events. I’m a big MMA fan, so the example we’re going to look at is getting a list of all UFC results from Wikipedia. In this post we’re going to take a look at how we can pull data from a series of similar pages.
Remember, if you’re not using Excel 2016 or later, then you’ll need to install the power query add-in. This is great for getting data from a webpage that is updated frequently as you will be able easily refresh your query to pull the new data. Within a couple of minutes you can build a query that will pull data from a webpage and transform it into the desired format. Excel’s Power Query (or Get & Transform since Excel 2016) is a great tool for building queries to get data from the web.