Getting data from the web is possible with Power Query in both Excel and Power BI. However, Excel is a little behind in Power Query features, therefore in this post I’m going to show you a new feature available in Power BI Desktop using Power Query to get data from the web by example.
In time we should also see this feature available in Power Query for Excel.
Getting data using ‘Web by Example’ is super handy for data on web pages that aren’t structured in HTML tables, or where the structure is messy, like this book catalogue site, WorldCat:
From this site I want to extract a list of books by, or about author H J Eysenck. The website allows me to enter a search term that filters the data. You can see in the image above it has returned 21,610 results with the first page showing just 10.
Here is the URL if you want to take a look at the page: https://www.worldcat.org/search?q=Eysenck&fq=&dblist=638&start=1&qt=previous_page
The first challenge is getting the following data from the web page:
- Book Title
- Author
- Type
- Publisher
It’s a challenge because the data on the page is not formatted in a proper HTML table that Power Query can easily find.
Download Power BI Desktop File
Enter your email address below to download the sample workbook.
Power Query Get Data from Web by Example
Step 1: Connect to the web page
Let’s start in Power BI Desktop - Home tab > Get Data > Web:
Step 2: Enter the URL
At the ‘From Web’ dialog box, enter the web page URL:
Tip: At the privacy setting choose ‘Anonymous’.
Step 3: Provide Examples
At the Navigator dialog box select ‘Add table using examples’:
Then in the bottom half of the window enter in the column labels you want and provide examples until Power Query detects the pattern:
Step 4: Edit or Load
The navigator dialog box will now have a Custom Table and you can click ‘Load’ if you’re ready to load it to your model, or ‘Edit’ to perform further transformations to the data in the Power Query editor:
The result is a table containing the columns you specified:
I know what you’re thinking…and no, you can’t copy the M code from the Advanced Editor in Power BI Desktop and paste it into Excel because Excel doesn’t yet recognise the functions Web.BrowserContents and Html.Table. Excel’s Power Query typically sees updates a few months after they are ‘generally available’ in Power BI Desktop.
The Second Challenge – Getting Data from Multiple Web Pages
Remember the first web page only returned 10 results. There were a further 21,600 results available, spread over a further 2,160 pages! In the next tutorial here we’ll look at how we can automate pagination to get all results.
Learn Power Query
If you’d like to learn more of Power Query’s wonders, please consider my Power Query Course. Or check out what Power BI has to offer, in my Power BI Course.
Include Grand Totals in Pivot Charts
Matthew
HI Mynda, do you have any advice on how I might Add A Tabe Using Examples from a page like this one so that I can use Power Query to establish parcel delivery status on a courier service traching site?
https://www.nzpost.co.nz/tools/tracking/item/2425345222547011AKL013AS
Also, if I had a list of tracking references, how might Power Query work its way down the list, returning a status result for each one?
Thanks always for the blog, the site – everything,
Matthew
Mynda Treacy
Hi Matthew,
Getting data from the web using examples is currently only available in Power BI. I tested the link and it works well to extract the tracking history. In my Power Query course I cover how to scrape data from multiple web pages based on a list of values, in your case it would be tracking IDs.
Mynda
Matthew Simpson
Thanks Mynda for your prompt reply. I’ll have another crack in PowerBI (now updated). Will also have a long look at the PQ course. I’m following along with the new series Philip has begun on using PQ in API, but suspect it is above my paygrade.
Best wishes always,
M
Mynda Treacy
Great! I forgot that I had written about getting data from multiple web pages with Power Query here. I still recommend taking the course as there’s a lot more to Power Query than meets the eye 🙂
Sarah
Hi, I don’t have the option of add tables from examples in my power bi, is there something I need to do?
Mynda Treacy
Hi Sarah, download the latest update of Power BI Desktop. There’s a new release every month! Mynda
Jerry Norbury
Any news on when this is coming for Excel?
Mynda Treacy
No news, sorry!
lenz
Thank You! Very Helpful
Mynda Treacy
Glad you liked it, Lenz 🙂
dan
Can Powerbi extract data from webpages that use Powerbi to display their data. Currently I cant find anything on that topi. I have tried to do it and it wont display the data generated by Powerbi.
Mynda Treacy
Hi Dan,
No, you can’t get data from a web page with a Power BI embedded report, sorry.
Mynda
Mohammad Raza
Thanks Mynda. Actually, with the new “suggested tables” feature currently in preview, you can now get all of this information automatically without having to give examples. Though the suggested tables do contain some extra columns that you may want to filter out.
Mynda Treacy
Yes, the suggested tables are handy too 🙂
Julian
Hi Mynda,
Thanks for your valuable information. However, I still got problems to add Html.Table function onto “Add table using examples”. Could you please implement that function into your pbix file? I would highly appreciated.
Best regards,
Julian
Julian
Dear Mynda,
Could you guide me furthermore how to get the url behind the book title?
Best regards,
Julian Chen
Mynda Treacy
Hi Julian,
You would need to use Html.Table as explained here.
Mynda
chandra polim
Dear Mynda,
why my excel doesn’t show the “Add table using example” button?
what should i do?
Regards,
Chandra
Mynda Treacy
It’s not available in Excel yet, as explained at the top of the post.
Alex
Another enormously cool demo, Mynda!
Thanks for sharing it.
Mynda Treacy
Thanks, Alex! Glad you like it.
Kathi Mills
Awesome! I can’t wait for part 2. My big challenge is getting data from a website with login credentials. Will you be addressing that issue? Pretty please!
Mynda Treacy
Thanks, Kathi! When connecting to a web page requiring a login choose ‘Basic’ authentication and you’ll be able to enter your username and password. That said, there seems to be a bug with this at the moment so it may be hit and miss.
Matthew
This is a great example with very helpful explanation.
Mynda Treacy
Cheers, Matthew 🙂