Power Query Get Data from Web Improvements
If you ever tried to extract data from a webpage using Power Query in Excel, where the data was not structured in a tabular format and contained in
HTML table tags, you might only get the option to pull out the HTML code which was not much use.
Now, we can use Suggested Tables or Add Table Using Examples that leverage AI to easily import data from the web into Excel.
Enabling you to keep your data updated for any changes made on the source web pages.
And transfer the related M code from Power BI to Excel and vice versa as now both support Web.BrowserContents and Html.Table functions.
New - Suggested Tables
Suggested tables are auto-detected Excel
tables, that can convert webpage data into a tabular format, even though visibly the webpage might not show any tables.
For example, suppose you are an avid gamer and want to keep track of Xbox game prices to buy them when they are heavily discounted. You can pull that data out using Suggested Tables.
How to use Suggested Tables
Browse games on the Xbox website that are priced between $5 and $10 > Copy the URL: https://www.xbox.com/en-US/browse/games?Price=5To10
Open a blank Excel file > Go to Data Tab > Select "From Web" under the "Get & Transform Data" section.
A dialog box will open for you to fill in the URL > Paste the Xbox URL and press ok.
In the Navigator menu, select the table you think contains the correct data from the Suggested Tables section.
You can check the data in the preview section to decide.
In
this case, Table4 gives the original and the sale price of the games from which you easily see which games have the best discount.
Sometimes you may need to transform the data to remove any additional data you don't need.
Limitations of Suggested Tables
While Suggested Tables is a great enhancement, they do have a few limitations:
- Some web pages don't render any Suggested Tables
- At times Suggested Tables don't have the data you need, or it's not structured the way you want and may require too much transformation
- Suggested Tables don't work well with endlessly scrolling pages or those
with the "Load More" button.
Some of these limitations can be overcome using the Add Tables Using Examples option.
New - Add Tables Using Examples
Suppose you are starting an online store selling gadgets. You want to stock up on inventory based on tech reviews from websites like CNET because you think those products will have the highest demand.
You open a buying guide such as the Best Flip Phone of
2023 but you see that the products mentioned are not in a tabular format. Also, the page has a lot of text that you will have to scroll through to find out the best products to add to your store.
Now Power Query can make this job easier for you!
All you need to do is open the buying guide and train Excel using the "Add Tables Using Example" method.
How to use Add Tables Using
Examples?
Go to the Best Flip Phone of 2023 buying guide > Copy the URL: https://www.cnet.com/tech/mobile/best-flip-phone-of-2023/
Open a blank Excel file > Go to Data Tab > Select "From Web" under the "Get & Transform Data" section.
Paste the CNET URL in the From Web dialog box and press ok.
In the Navigator menu, select the Add Tables Using Examples option.
A new window will appear with two panes, where the top pane is the URL preview, and the bottom pane has a table for you to enter data.
As you type the first few letters, you will start getting suggestions. Pick the right product from the suggestions.
After you give a few examples, Excel will detect a pattern and scroll through the page to pull out similar .
Once the first column is complete, you add two more columns for price and "best for" the same way.
And just in minutes, you will have all the products mentioned in a buying guide neatly arranged in an Excel table!
Don't forget to change column names (double
click to edit them) > Press Ok
Once you are back on the
Navigator page, a new table will appear under the section Custom Tables. Click on it to load the preview.
You can either load the data as is or transform it to suit your needs.
Transforming Data
As you can see, the price column also has the supplier mentioned in it. It would make more sense to split it into separate Price and Supplier columns.
To do this, select Price column > Select
Split Column from the Home Tab> Select "By Delimiter"
In the
Split Column by Delimiter window, select a custom delimiter " at "
Select Left-Most delimiter to avoid multiple splitting points > Press Ok
Tip: Ensure that you have spaces before and after the word "at" to avoid having extra spaces after splitting the columns.
You will have two columns with price and supplier, rename them accordingly, and check that the data type is correct.
Close and load the table.
Tip: It's always a good idea to rename your queries for future reference. Here I have renamed it to Flip Phones
Replicating the Query
Once you do this for one buying guide, you can replicate the query for all the buying guides from the same website with the same webpage structure. If you only want to scrape one
more page the following steps are the quickest (if you have multiple pages to scrape, see the next section):
Go to the Power Query editor > Right-click on the query > Select Duplicate
Go to the View Tab > Open Advanced Editor > Replace URL
For example, here I replaced the Flip Phones URL with the Cheap Phones URL https://www.cnet.com/tech/mobile/best-cheap-phone/
And without doing anything, I will get a new table with a list of the best cheap
phones!
But
that's not all! You can also append multiple queries together to create one single comprehensive product list!
Tip: In case you get a privacy notice, select "Public"
Scrape
Data from Multiple Pages
Let's say you have a list of pages you want to get data from in a table called Page_URLs:
Load this table to Power Query using the From Table/Range connector:
Then load one of the pages to Power Query using the From Web connector and Add Table Using Examples we looked at in the previous step. I've called this query Products.
Open the Advanced Editor for the web query and add (URL as
text) => at the top of the query code and replace the URL in the code with "&URL&":
This will convert the query into a custom function that looks like this:
Go to the ProductList query and Add Column tab > Invoke Custom Function:
Select the query name from the Function query drop down list:
At the warning, click on 'Edit Credentials' and choose Anonymous:
Click the double arrow on the Products column:
I like to deselect 'Use original column name as prefix':
Click ok and you should see the results from all pages in one table:
Filter out any null rows from the Price column and change the data types on the Transform tab > Detect Data Type:
Check the data types are assigned correctly and you're good to Close & Load.
To get updates to the queries, right click
the query > Refresh, or via the Data tab of the ribbon > Refresh All to update all queries in one go.
Limitations of Add Tables Using Examples
- Page structure: All source web pages should have a similar page structure if you want to replicate queries or scrape multiple pages.
- Complex pages: If the webpage is too complex, Power Query might not be able to detect patterns leading to inaccurate results.
- JavaScript limitation: Power Query still cannot get data from pages that use JavaScript to load the data on the fly as filters are selected within the web page, without changing the URLs. We saw this with the first example for the Xbox games where there is a button at
the bottom of the page to 'load more'. This loads more results to the page, but Power Query cannot see these results.
- API Limits: some websites will limit the number of results returned to manage load on their site. This is not a Power Query limitation, but rather the site owner's choice.
- Forbidden Access Limitation: Some
web pages restrict access to their data:
Requirements
Currently, these features are only released for Windows users with
Microsoft 365. Microsoft has plans for a Mac release, but there is no timeline for their availability.
Apart from that you also need the following:
- Microsoft Office 365 subscription
- Install the latest Excel update from File > Account > Update Now
- Remember to respect copyright laws and
regulations when scraping data from websites
Next Steps
Ready to unlock the full potential of Power Query and transform your data handling skills? Dive into our comprehensive Power Query course and take your expertise to the next level. Sign up now and begin your journey to becoming a data wizard!
Learn With Us
