Power Query Problems

How to Extract HTML Tables from Emails into Excel with Power Query

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to extract identically formatted HTML tables from hundreds of emails into Excel, but the data is importing into a single text cell or column instead of a structured table.

Product: Excel
Device & OS: not provided
Scenario: Importing multiple emails containing HTML tables to extract structured data for spreadsheet analysis.
Observed behavior: Instead of splitting into proper rows and columns, Power Query imports the entire HTML table from the email body into a single text cell or column.

Before you start

Ensure you have the necessary permissions to access the Exchange email account and that all target emails are grouped in a specific mail folder for easier querying.

Solution 1Recommended

Extract and Parse HTML Tables via Exchange Online Connector

Use the Microsoft Exchange Online connector in Power Query to pull email bodies, then parse the HTML text to structure the table data.

When importing email bodies, Power Query initially retrieves the content as plain text or raw HTML strings. To extract structured tables and prevent data from cramming into a single column, you must explicitly parse the HTML content using Power Query transformations.

Connect to Exchange Online

Open Excel, go to the Data tab, select 'Get Data', navigate to 'From Online Services', and click 'From Microsoft Exchange Online'. Enter your email credentials to establish the connection.

Select the Mail Folder

In the Navigator window, locate your mailbox and check the specific folder containing the emails with the HTML tables. Click 'Transform Data' to open the Power Query Editor.

Expand the Email Body

In the Power Query Editor, locate the 'Body' column. Click the expand icon in the column header to reveal the 'TextBody' and 'HtmlBody' fields. Select 'HtmlBody' to retrieve the raw HTML code of the emails.

Parse the HTML Content

To convert the HTML code into a table, add a custom column using an HTML parsing function, or use the 'Split Column by Delimiter' feature. For the delimiter, enter specific HTML tags (like <tr> for rows or <td> for columns) to separate the data manually.

Load the Structured Data

Once the data is split into proper rows and columns, expand the newly parsed table records. Remove any unnecessary metadata columns, then click 'Close & Load' to output the structured table directly into your Excel spreadsheet.

Advanced HTML Parsing: If standard splitting doesn't yield a clean table, you may need to use advanced M-code, specifically the Web.Page() function, inside a custom column to accurately parse complex HTML structures.

Free Microsoft Office alternative

Manage Your Spreadsheet Data with WPS Office

While highly complex M-code scripts for Microsoft Exchange are specific to the MS Excel ecosystem, WPS Office provides a robust, lightweight, and highly compatible alternative for everyday data processing, table formatting, and advanced spreadsheet management without the heavy subscription costs.

1. Download and Install: Visit the official WPS website to download and install WPS Office on your device.
2. Open Your Spreadsheets: Launch WPS Spreadsheet and easily open your existing .xlsx or .csv files.
3. Analyze Your Data: Utilize built-in features like Pivot Tables, filtering, and text-to-columns to manage your extracted email data effortlessly.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Seamlessly open and edit spreadsheet data extracted from email reports.Lightweight installation and incredibly fast loading speeds.Built-in advanced data processing tools and pivot tables.Completely free to use for core daily spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my imported email table appear in a single cell?

By default, Power Query reads the email body as a single block of raw text or HTML. Without explicit parsing instructions, it doesn't recognize standard HTML table tags to split the data into proper rows and columns.

Can I use 'Split Column' to separate my HTML data?

Yes. If standard parsing isn't working, you can use the 'Split Column by Delimiter' feature in Power Query. By entering specific HTML tags like <tr> for rows or <td> for columns as custom delimiters, you can force the data into distinct columns.

Does this extraction method work for Outlook desktop attachments?

The Microsoft Exchange Online connector pulls data directly from the exchange server, targeting the email body itself. If your tables are in attachments (like CSVs or other Excel files), you will need to expand the 'Attachments' column in Power Query instead of the 'Body' column.

Excel & Data Problems