Importing Web Data into Excel Using the New Power Query Web Connector

Overview

• Importing data from a web page into Excel can be challenging, especially when web pages lack traditional HTML tables, have clickable blocks, or require regular updates.
• Excel's Power Query Web Connector has recently received updates that make importing, transforming, and dynamically updating web data easier and more powerful.

Key Features of the New Power Query Web Connector

• Dynamic Import: Directly imports structured data (including clickable links and images) from web pages into an Excel table.
• Automatic Updates: If the web content changes (e.g., new course blocks), simply refreshing the query updates your Excel data automatically.
• Handles Unstructured Data: Even if the web page has no tables, Power Query can recognize repeating blocks of information and allow custom extraction.

Step-by-Step Process

1. Prepare & Connect

• Copy the URL of the desired web page.
• Open Excel > Data tab > From Web > Paste the URL > OK.
• Power Query connects to the web page and suggests possible tables/patterns it detects.

2. Analyzing Suggested Tables

• Review the suggested tables for relevant data (course name, category, number of lessons, etc.).
• If needed data like links or images are missing, proceed to extract data using custom examples.

3. Extracting Data Using Examples

• Use "Add Table Using Examples" in Power Query:
  • Enter sample values for desired columns (e.g., course name, category, lessons, link, image).
  • With a couple of examples, Power Query learns the pattern and applies it to extract the data dynamically.

4. Data Transformation

• Transform output as needed (e.g., remove the word "lessons" from a text field, change data types).
• Fine-tune the steps in the Power Query Editor.

5. Improving Data Display in Excel

• Direct export provides a table, but links may not be clickable, and images may not render.
• Edit the query and use the "Create Data Type" feature to bundle all course information.
  • Cards appear in the table, holding expanded info for each course.
• Add columns as needed by clicking '+' in the card to extract category, lessons, link, and image.
• Use Excel formulas (like HYPERLINK and IMAGE) to convert text to clickable links and display images.

6. Final Adjustments

• Adjust table design, column widths, and display properties as desired.
• Refresh data to verify dynamic updates (e.g., add a new course on the website and confirm it appears in Excel with correct info and images).

Benefits

• Automates previously manual, repetitive, and error-prone data collection from web pages.
• Ensures data (including links and images) in Excel stays current and interactive.
• Can be adapted for various web pages as long as they don’t rely on dynamic JavaScript rendering.

Practical Example

• Added a new course "Quick Wins with ChatGPT" on the web page.
• Upon refreshing the Excel query, the new course (along with its image and link) appeared immediately in the Excel table.

Conclusion & Call to Action

• The upgraded Power Query Web Connector is a powerful tool for anyone who works with web data in Excel.
• For more advanced learning, consider the "Master Power Query Beginner to Pro" course.
• Over 50,000 professionals have upgraded their skills through this course; links provided for enrollment.
• Don’t forget to like and subscribe for more tips and tutorials.

---

Quick Reference: Steps to Import Web Data into Excel

1. Copy web page URL.
2. Excel > Data > From Web > Paste URL.
3. Review/choose a suggested table or use "Add Table Using Examples".
4. Customize extraction by entering sample values per column.
5. Transform and clean data in Power Query.
6. Use "Create Data Type" for richer, bundled info.
7. Expand fields and use Excel formulas to enable hyperlinks and images.
8. Adjust formatting, refresh to pull in new web content automatically.

---

Useful Excel Functions

• HYPERLINK(url, [friendly_name]): Makes web links in a cell clickable.
• IMAGE(url): Displays an image from the web in a cell (supported in newer Excel versions).

---

Tip: This approach is ideal for any web page not heavily reliant on dynamic JavaScript rendering. For more complex sites, additional tools/scripts may be required.