Power Query Problems

How to Replace Excel Data with the Latest Email Attachment Using Power Query

Maira Mehtab Sep 30, 2026 869 views

Question details

The user wants to update an existing Excel table using data from the most recently received email attachment instead of continuously appending duplicate records.

How to Replace Excel Data with the Latest Email Attachment Using Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Updating an Excel report automatically with the newest data received via email attachments.
Observed behavior: The user needs a workflow that overwrites or replaces old data with the newest email attachment data, rather than appending it and creating duplicates in the workbook.

Before you start

Ensure you have access to your Microsoft Exchange or Outlook account credentials and that the incoming email attachments share a consistent file format and column structure.

Solution 1Recommended

Use Power Query Exchange Connector to Filter and Deduplicate Data

Connect directly to your email account via Power Query, merge the new attachment with your existing table, and filter out older duplicates to keep only the latest data.

This method involves loading both your current Excel table and your incoming email attachments into Power Query. By utilizing the 'Date Received' field and unique identifiers (like a conversation topic), you can isolate the newest attachment and discard outdated rows.

Connect to Microsoft Exchange

In Excel, go to the 'Data' tab on the ribbon, click 'Get Data', select 'From Online Services', and choose 'From Microsoft Exchange'. Enter your email credentials to connect.

Extract the Email Attachments

In the Navigator window, select the 'Mail' folder and click 'Transform Data'. In the Power Query Editor, locate the 'Attachments' column, expand it, and filter by the specific file extension (e.g., .xlsx or .csv) to find your target data.

Combine with Existing Data

Append or merge this newly extracted attachment query with your existing Excel table query so that both the old and new data reside within the same Power Query preview window.

Sort and Remove Duplicates

Select the 'Date Received' column and sort it in descending order so the newest emails appear at the top. Next, highlight the column(s) that uniquely identify your records (such as conversation topic), right-click the column header, and select 'Remove Duplicates'. This retains only the most recent attachment data.

Load the Refreshed Data Back to Excel

Click 'Close & Load To...' from the Home tab in Power Query. Choose 'Table' and select the exact location of your existing worksheet to overwrite the old data with the newly refreshed dataset.

Use Power Query Exchange Connector to Filter and Deduplicate Data

Deduplication Tip: Power Query processes the 'Remove Duplicates' command from top to bottom. By ensuring your data is sorted by date in descending order first, you guarantee that the newest record is the one kept.

Free Microsoft Office alternative

Manage Your Spreadsheets Seamlessly with WPS Office

If you are looking for a lightweight, fast, and cost-effective solution for handling complex datasets and daily spreadsheet tasks, WPS Office offers a powerful alternative to Microsoft Excel with high compatibility and an intuitive interface.

1. Download the Installer: Visit the official WPS Office website and click on the 'Free Download' button.
2. Install the Suite: Run the downloaded lightweight installer and follow the simple on-screen instructions.
3. Open Your Spreadsheets: Launch WPS Spreadsheets to start viewing, editing, and managing your .xlsx data files instantly.

Highly compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Lightweight and fast installation processFamiliar user interface for a zero-learning-curve migrationBuilt-in PDF tools and robust cloud collaboration features

microsoft office alternative - wps office

Frequently Asked Questions

Will this method delete my old data in Excel?

Yes, but intentionally. The old data in your worksheet will be overwritten by the refreshed Power Query output. By keeping the latest attachment records and loading them back to the exact same worksheet location, the old table is seamlessly replaced rather than endlessly duplicated.

Why is Power Query appending my data instead of replacing it?

If your query is set to simply 'Append Queries' without a subsequent deduplication step, it will keep adding new rows to the bottom of your table. To replace old data, you must apply the 'Remove Duplicates' function based on unique identifiers and dates before loading the data back into Excel.

Do I need the Outlook desktop application installed to connect to Exchange in Power Query?

No, you do not need the Outlook desktop client installed. You can use the 'From Microsoft Exchange' connector directly within Excel's Power Query by simply authenticating with your Exchange or Microsoft 365 email credentials.

Excel & Data Problems