How to Use Word Mail Merge With a Power Query-Sourced Excel Table

You have an Excel table that you cleaned and reshaped using Power Query, and now you want to use that data in a Word mail merge. When you try to connect Word to the Power Query table, the data source might not appear, or the merge fields show blanks or errors. This happens because Power Query creates a connection, not a static table, and Word cannot read the query results directly. This article explains how to convert a Power Query output into a stable data source that Word can recognize, set up the merge, and avoid common pitfalls with dynamic data.

Why Word Cannot Read a Power Query Output Directly

Power Query in Excel loads data into the workbook as a table, but the table is linked to the query definition. When Word opens the Excel file to read the data source, it only sees the worksheet and the table object. If the table is not fully loaded or if the query has not been applied, Word sees an empty range or a connection name instead of rows and columns.

The root cause is that Word uses the older Data Source Object (DSO) method to read Excel data, which does not support Power Query connections. Word expects a standard Excel range or a named range with static data. To make the merge work, you must first ensure the Power Query output is a fully materialized table on a worksheet.

What Happens When You Try the Direct Connection

If you go to Mailings > Select Recipients > Use an Existing List and pick the Excel file, Word shows a dialog asking you to select a table or named range. You will see the worksheet name, but if the Power Query table is not loaded, the range appears empty. Selecting it results in a merge with zero records, or Word displays an error saying the data source cannot be opened.

Steps to Prepare the Power Query Table for Mail Merge

Follow these steps to convert your Power Query output into a stable table that Word can read. Perform all steps in Excel first, then switch to Word.

Step 1: Load the Power Query Result as a Table

1. Open the Excel workbook containing the Power Query query
   Click the Data tab on the ribbon. In the Queries & Connections group, locate your query in the Queries pane on the right side of the window.
2. Edit the query if needed
   Double-click the query name to open the Power Query Editor. Make any final adjustments to columns, filters, or data types. When finished, click Close & Load in the upper-left corner.
3. Choose the load destination
   In the Import Data dialog, select Table and choose New worksheet or Existing worksheet. For mail merge, a new worksheet is recommended to avoid overwriting other data. Click OK.
4. Verify the table appears on the worksheet
   Excel creates a new worksheet with your data formatted as a table. The table has a default name like Table_QueryName. You can rename it on the Table Design tab if desired.

Step 2: Save and Close the Excel File

1. Save the workbook
   Press Ctrl+S or click File > Save. Use a file name that is easy to find later, such as MailMergeData.xlsx.
2. Close Excel
   Word reads the Excel file more reliably when the file is not open in another application. Close Excel completely before proceeding.

Step 3: Start the Mail Merge in Word

1. Open your main document in Word
   This is the letter, label, or envelope template you want to merge with the data.
2. Go to Mailings > Select Recipients > Use an Existing List
   Browse to the Excel file you saved in Step 2. Select it and click Open.
3. Choose the correct table
   Word displays the Select Table dialog. You will see the worksheet name and the table name. Select the table name that corresponds to your Power Query output. Check the box First row of data contains column headers if your table has headers. Click OK.
4. Insert merge fields
   Place your cursor where you want data to appear. Click Insert Merge Field and choose the appropriate field from the list. Repeat for all fields.
5. Preview the results
   Click Preview Results in the Mailings tab. Use the arrow buttons to scroll through records. If data appears correctly, proceed to Finish & Merge.

What to Do When the Data Source Changes

Power Query is designed to refresh data from external sources. If you update the source data and refresh the query, the table in Excel changes. Word does not automatically detect these changes. You must refresh the Excel table first, then update the Word connection.

Refresh the Power Query Table in Excel

1. Open the Excel file
   Double-click the workbook that contains the Power Query table.
2. Refresh the query
   On the Data tab, click Refresh All in the Queries & Connections group. The table updates with the latest data.
3. Save and close Excel
   Press Ctrl+S and close the workbook.

Update the Mail Merge in Word

1. Open the Word merge document
   If it is already open, go to Mailings > Select Recipients. Choose the same Excel file again. Word refreshes the data from the saved file.
2. Re-preview the merge
   Click Preview Results to confirm the new data appears correctly.

Common Problems and Solutions

Word Shows an Error: Word Cannot Open the Data Source

This error occurs when the Excel file is still open in Excel, or when the Power Query table has not been loaded as a static table. Close Excel completely. If the error persists, open the Excel file, go to the Data tab, and confirm that the table has data. Save the file and try again.

Merge Fields Display Blank Values

Blank values indicate that Word cannot match the field names to the column headers. Open the Excel file and check that the column headers in the first row exactly match the merge field names in Word. Remove extra spaces in header names. Save and re-select the data source.

Only One Record Appears in the Merge

This happens when Word selects a named range that contains only one row. In the Select Table dialog, choose the table name rather than the worksheet name. The table name includes all rows. If you still see only one record, open the Excel file and verify that the table extends to all data rows.

Word Mail Merge With Static Table vs Power Query Table

Item	Static Excel Table	Power Query Table (Loaded as Table)
Data source setup	Typed or pasted data directly in Excel	Data imported and transformed via Power Query, then loaded to a worksheet
Word connection method	Mailings > Select Recipients > Excel file	Same method, but must select the table name, not the connection
Data refresh process	Manually update cells in Excel	Refresh query in Excel, then re-select data source in Word
Risk of broken merge	Low if structure stays the same	Medium – column renames or deletions in Power Query break field mapping
Best use case	Small, static datasets	Large or regularly updated datasets from databases or web sources

Using a Power Query-sourced table for mail merge requires one extra preparation step compared to a static table. The benefit is that you can automate data cleaning and updates without manual rework. Always keep the column structure stable in Power Query to avoid broken field mappings in Word. If you need to add or remove columns, update the merge fields in Word after refreshing the data.