When you set up a Word mail merge that pulls data from an Excel workbook, the connection works correctly until you refresh a Power Query in that Excel file. After the refresh, Word shows an error saying it cannot open the data source or the merge fields become unreadable. This happens because Power Query transformations replace the original table structure, breaking the link that Word relies on. This article explains the root cause of the connection failure, provides step-by-step fixes to restore the mail merge, and outlines workarounds to prevent the issue from recurring.
Key Takeaways: Preventing Word Mail Merge Disconnection After Power Query Refresh
- Mail Merge Recipients > Edit Recipient List > Data Source > Refresh: Reconnects Word to the same Excel file after a Power Query refresh restores the original table name and structure.
- Power Query > Close & Load To > Connection Only: Prevents the query output from replacing the source table, keeping the original worksheet intact for the mail merge.
- Mail Merge > Select Recipients > Use an Existing List > Browse to the Excel File: Recreates the connection from scratch when the automatic refresh fails or the data source path changes.
Why Power Query Refresh Breaks the Word Mail Merge Connection
Word mail merge uses the OLE DB data provider to connect to an Excel workbook. When you select an Excel file as the data source, Word records the file path, worksheet name, and the range of cells that contain your header row and data. The connection is static: Word expects the table structure — column names, data types, and row count — to remain unchanged each time you open the document or run the merge.
Power Query in Excel works differently. When you load a query into a worksheet, Power Query can create a new table that replaces the original data range. The replacement table often has a different internal name (for example, Query_Table1 instead of Sheet1$) and may include additional columns, removed columns, or transformed data types. After you refresh the query, Excel deletes the old table and inserts the new one. Word’s OLE DB connection still points to the old table name and structure, so it fails with an error such as “Word cannot open the data source” or “This action cannot be completed because the data source is not valid.”
The failure is not caused by a corrupted file. It is a mismatch between the static reference stored in Word and the dynamic table that Power Query generates. The same problem occurs if you rename the worksheet, move the Excel file, or change the query output location.
Steps to Reconnect Word to Excel After a Power Query Refresh
The following methods restore the mail merge connection without recreating the entire merge document. Use Method 1 first because it is the fastest. Use Method 2 if the connection is completely broken or if Word cannot find the data source.
Method 1: Refresh the Data Source in the Mail Merge Recipients Dialog
- Open the mail merge main document in Word
Make sure the document is the one that contains the merged fields. Do not close the Excel file yet. - Go to Mailings > Edit Recipient List
This opens the Mail Merge Recipients dialog box, which lists the current data source and the records. - Click the Data Source name, then click Refresh
In the Data Source section at the bottom of the dialog, select the Excel file entry. Click the Refresh button. Word re-reads the Excel file, finds the new table structure, and updates the field mappings. - Verify the merged fields
Click OK to close the dialog. Preview the merge results by clicking Mailings > Preview Results. If the fields display correct data, the connection is restored.
Method 2: Recreate the Data Source Connection
- Remove the existing broken connection
In Word, go to Mailings > Select Recipients > Use an Existing List. In the dialog that opens, select the same Excel file. Word will ask whether you want to replace the existing data source. Click Yes. - Select the correct worksheet or named range
If the Power Query output is on a different worksheet or has a named range, choose that specific sheet from the list. If you see a named range that matches the query table, select that instead of a worksheet. - Map the fields again if necessary
After selecting the new source, click Mailings > Match Fields to ensure each Word merge field is linked to the correct Excel column. This step is required if the column names changed after the Power Query transformation. - Save the mail merge main document
Use File > Save As to save a copy with the new connection. This prevents the old connection from being used the next time you open the file.
If Word Still Has Issues After Reconnecting
Word Shows “Cannot Open the Data Source” Even After Refresh
This error often means the Excel file is open in another program, or the Power Query refresh is still running. Close Excel completely. Open the mail merge document in Word, then go to Mailings > Edit Recipient List and click Refresh again. If the error persists, open the Excel file separately and check whether the query output table has a different name. In Excel, click Formulas > Name Manager and look for the table name. Note the exact name, then in Word, use Method 2 and select that named range as the data source.
Merge Fields Appear as Blank or Show {MERGEFIELD} Codes
This happens when the column header names in Excel changed after the Power Query refresh. For example, the query might rename “First Name” to “FirstName”. Word cannot map the old field name to the new column. To fix this, click Mailings > Match Fields and manually assign each Word field to the corresponding Excel column. After mapping, preview the results to confirm the data appears correctly.
Power Query Refresh Replaces the Entire Worksheet
If the Power Query is set to load into a specific worksheet and overwrite existing data, every refresh destroys the table that Word is linked to. To prevent this, edit the query in Excel: right-click the query in the Queries & Connections pane, select Properties, and under the Usage tab, uncheck “Overwrite existing data with new data.” Instead, load the query to a new worksheet each time. Then update the Word connection to point to that new worksheet after each refresh.
Power Query Load Options vs Mail Merge Compatibility
| Item | Load to Worksheet (Default) | Connection Only |
|---|---|---|
| Table structure after refresh | Replaced with new table, often with different name | No table created in worksheet |
| Mail merge compatibility | Breaks connection after first refresh | Not usable as a direct data source |
| Recommended for mail merge | No — requires manual reconnection after each refresh | No — cannot be selected in Word |
| Workaround | Use a named range that points to the query output | Load query to a separate worksheet using “Only Create Connection” then reference it with INDIRECT |
The best long-term workaround is to separate the raw data from the query output. Keep the original Excel table that Word connects to on one worksheet, and run the Power Query on a different worksheet. After the query refresh, copy the transformed data back to the original worksheet using a simple formula reference such as =Sheet2!A1 or use Power Query to load only to the data model and then use Excel formulas to pull the values into the mail merge source range. This way, the table name and structure that Word depends on never change.
You can now reconnect a broken mail merge after a Power Query refresh using the Refresh button in the Mail Merge Recipients dialog or by recreating the data source connection. To avoid future failures, configure Power Query to load to a separate worksheet and use formulas to feed the mail merge source table. For advanced setups, consider using a named range that dynamically expands with the data, which Word can recognize as a stable data source.