Merge Two Excel Files in Power Query: Step-by-Step

How-to basics · Updated November 20, 2026

To merge two Excel files in Power Query, import each file as a query, choose Home > Merge Queries or Merge Queries as New, select the matching columns, choose a join type, and expand the resulting column. This joins related records side by side; if you mean stacking one file’s rows beneath another’s, use Append Queries instead.

What You’ll Need

  • Excel for Microsoft 365 on Windows and the two Excel workbooks you want to combine. Keep the files in a location you can access, and close them before importing if possible.
  • A column in each workbook that identifies the same records, such as Customer ID, Product Code, or Order Number. The column names can differ, but the values and data types should correspond.
  • Structured, consistent source data. Excel tables are convenient, but Power Query can also import a worksheet or a named range from a workbook.
  • A decision about the result you need: keep only matching records, keep all records from one file, or keep records from both files.

Power Query is built into Excel for Microsoft 365 on Windows. The instructions below use the Windows desktop menus. Excel for Mac has Power Query features, but available connectors and editing commands can differ by version; if you do not see the merge controls, use the Windows desktop version. Excel for the web does not provide the same full Power Query authoring experience.

Step-by-Step

  1. Check and prepare both workbooks. Open each source file and confirm the data has a single header row, no merged cells within the data, and no completely blank rows interrupting the records. Give each column a clear heading. If practical, select each dataset and press Ctrl+T to format it as an Excel table, making sure My table has headers is selected. Save and close the files. Tables make the source easier to identify and are less likely than a fixed range to miss new rows.
  2. Import the first workbook. In the workbook where you want the combined result, go to Data > Get Data > From File > From Excel Workbook. Browse to the first file and select Import. In Navigator, select the table, worksheet, or named range containing the records. Preview it to make sure you chose the right data, then select Transform Data. Power Query Editor opens with the selected data as a query.
  3. Import the second workbook as another query. In Power Query Editor, choose Home > New Source > File > Excel Workbook, select the second file, and choose the appropriate table or sheet in Navigator. Select Transform Data. You should now have two source queries in the Queries pane. Rename them to recognizable names, such as Customers and Orders, by right-clicking each query and choosing Rename. Clear names make the merge dialog easier to follow.
  4. Check the matching columns before merging. In both queries, locate the field that represents the same key. For example, Customer ID in one file might match Account Number in the other. Confirm both columns have compatible data types: text should match text, and whole numbers should match whole numbers. Use the data-type icon beside the heading to inspect or change a type. Check that IDs have consistent spaces, leading zeros, and capitalization. Do not convert an ID to a number if leading zeros are meaningful.
  5. Open the merge dialog. Select the first query in the Queries pane, then choose Home > Merge Queries > Merge Queries as New. This creates a separate result query and leaves the original queries available for reuse. Choose Merge Queries instead if you deliberately want to add the match to the selected query. In the dialog, choose the second query from the lower table list. Click the matching key column in the top preview and then its counterpart in the bottom preview. A small number appears by each selected column.
  6. Choose the join type and confirm. For a common lookup, choose Left Outer: all rows from the first table and matching rows from the second. Choose Inner if you want only records with a match in both files. Full Outer keeps all rows from both; Right Outer keeps all rows from the second and matching rows from the first. The anti joins return rows that do not match on one side. Check the dialog’s match count when available, and select OK. Power Query adds a column containing nested tables from the second query.
  7. Expand the matching data. In the new column, select the expand button in its heading, shown as two arrows. Select the fields you want to bring into the result, then select OK. You generally do not need to expand the key again if it is already present. Clear Use original column name as prefix if you prefer shorter headings; otherwise, the source query name is added to expanded field names to help distinguish them. Review the preview for sensible values and column names.
  8. Load the result and refresh it later. Rename the merged query, then choose Home > Close & Load to return the result to Excel. To choose where it goes, use Close & Load To and select a worksheet or the Data Model as appropriate. Save the destination workbook. When source data changes, open Data > Refresh All to update the query output. If files move or are renamed, edit the source step in Power Query before refreshing.

Example: Suppose Customers contains Customer ID and Region, while Orders contains Customer ID, Order Date, and Amount. Merge Customers with Orders on Customer ID using Left Outer, then expand Order Date and Amount. The result has the customer fields alongside each matching order. If a customer has several orders, that customer’s information appears on several rows, one per matching order. If instead the goal is to put two monthly order lists together, where both files have the same columns, append the queries rather than merging them.

Common Mistakes

  • Choosing the wrong operation. Merge matches records using one or more key columns and adds fields from the other query. Append stacks rows. If both files have the same column layout and represent separate periods, Append Queries is usually the right choice.
  • Joining on a field that is not unique. A merge does not automatically pick one matching record. If the second query has three rows for the same ID, a matching first-table row can appear three times after expansion. Check whether repeated keys are expected, and remove duplicates only if the business meaning allows it.
  • Using mismatched key types or formats. An ID stored as text in one file and as a number in the other may not match. Leading zeros, extra spaces, punctuation, and inconsistent capitalization can also cause unexpected unmatched rows.
  • Selecting the wrong join type. Inner joins discard nonmatching rows. Left Outer joins retain all rows from the top query, not necessarily all rows from both files. Decide which records must survive before you confirm the dialog.
  • Expanding every field without reviewing names. Duplicate headings can become confusing, and unnecessary columns make the output harder to use. Expand only what the report needs and keep a useful prefix when two fields have similar names.

Tips

  • Use stable IDs rather than names as keys when possible. Names can be spelled differently or shared by multiple people; a customer or product ID is usually more reliable.
  • For a composite key, select multiple columns in each preview while holding Ctrl, and select them in the same order in both tables. Every selected field participates in the match. For instance, a product and warehouse pair can identify stock records more precisely than product alone.
  • Make source data Excel tables and use descriptive table and query names. When new rows are added inside a source table, a refresh is more likely to include them than if the query points to a fixed worksheet range.
  • Keep the original queries separate and use Merge Queries as New when you want a clean, reusable output. You can then adjust the merge without losing the imported source steps.
  • Before loading, inspect the preview for nulls in expanded columns, unexpected duplicate rows, and the final row count. A null usually means the key had no match under the selected join; it does not necessarily mean the source cell was blank.
  • If a source file is shared with others, use a stable folder path and make sure workbook users have access to it. A query that works on your computer may fail for someone whose copy of the file is stored elsewhere.

Troubleshooting

The merge returns few or no matches: Compare the key columns in both previews. Confirm their data types, remove leading or trailing spaces, and check for inconsistent punctuation or leading zeros. If one column contains text with mixed capitalization, standardize it in both queries before merging. Recheck that you selected the intended columns rather than similarly named fields.

The result has more rows than expected: Look for duplicate keys in the query that you expanded. A many-to-many match can multiply rows. Filter or group the source to understand the repeats, then decide whether to correct the source, use a more precise composite key, or keep the multiple matches because they represent real records.

Some rows disappeared: Review the join type and which query is shown in the top part of the merge dialog. Inner keeps only matches. Left Outer keeps every top-query row but not unmatched rows from the lower query. Change the join if the report needs a different set of records, then inspect the row count again.

Refresh says the file or table cannot be found: Check that the source workbook still exists at the saved location and that the table or sheet has not been renamed. In Excel, open Data > Queries & Connections, right-click the query, and choose Edit to inspect its source steps. Update the source location if necessary, then refresh. If someone else will use the workbook, confirm they can access the same path.

The merged column is missing or looks wrong: Verify that you selected the intended second query and clicked its corresponding key. If the nested-table column appears, the merge itself has been created; select its expand button and choose the fields to display. If the columns still do not align, cancel or remove the merge step and repeat it carefully.

Frequently Asked Questions

What is the difference between Merge and Append in Power Query?

Merge joins tables using matching values in key columns and brings fields from one table into the other. Append places rows from one query below rows from another. Use Merge for related details, such as customer information on orders; use Append for similar monthly or regional lists.

Do the matching columns need to have the same name?

No. They can have different headings as long as they identify corresponding values. For example, Customer ID can match Account Number. Their data types and underlying values must still be compatible, and you must select the correct pair in the merge dialog.

Can I merge workbooks that have different column layouts?

Yes. A merge only needs a compatible key in each query. The rest of the columns can differ. After the merge, expand only the fields you need from the second workbook. If the goal is to stack records, however, append works best when the columns are aligned; Power Query can fill absent fields with nulls.

Will the merged result update when I change the source files?

Yes, refresh the query using Data > Refresh All. Power Query reads the sources again from their saved locations. The files must remain accessible, and structural changes such as renamed sheets, tables, or columns may require you to edit the query.

Why do I see repeated rows after expanding?

There is likely more than one match for a key in the second query, so Power Query returns a row for each match. Check whether those repeated keys are valid. If not, clean or deduplicate the source, or choose a key that identifies each record more precisely.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.