Power Query Excel: Combine Data from Two Tables

How-to basics · Updated November 18, 2026

In Excel for Microsoft 365 on Windows, use Power Query to combine two tables: choose Append to stack their rows, or Merge to match rows using a shared column such as an ID. Select a cell in a source table, go to Data > From Table/Range, and use the corresponding command in the Power Query Editor.

Choose Append or Merge

The right method depends on what “combine” means for your data. Append adds records from one table beneath records from another, making a longer table. Merge matches records across tables and brings selected columns together, making a wider table. These are different operations, even though both start in Power Query.

For example, imagine a January sales table and a February sales table, each with columns for Date, Product, and Amount. Append them to create one sales history. If you have a sales table with ProductID and a separate product table with ProductID, ProductName, and Category, merge them to add product details to each sale.

  • Append: combine tables with similar columns, often data from different months, locations, or files.
  • Merge: join tables using one or more corresponding columns, much like a database join or a lookup.

Power Query keeps the steps used to prepare the result. When the source data changes, you can refresh the query instead of repeating the combination manually.

Prepare the Excel tables

Start with properly structured data. Each source should have one header row, one field per column, and no merged cells in the data range. Convert each range into an Excel table by selecting a cell in it and choosing Insert > Table, or press Ctrl+T on Windows. Check My table has headers if the first row contains field names. Give each table a descriptive name from Table Design > Table Name, such as Sales_January and Sales_February.

Tables are useful because they expand when rows are added and give Power Query a clear source. Avoid blank rows or extra title lines above the headers; those can be interpreted as data. If you are merging, identify the field or fields that represent the same thing in both tables, such as CustomerID. The column names do not have to match for a merge, but the values and data types need to correspond.

It is also worth deciding what a row represents before combining anything. In a transaction table, one row might be one order line; in a customer table, one row might be one customer. A merge can produce unexpected repeated rows if the chosen key is not unique in one of the tables.

Append two tables to stack rows

Use append when the tables contain the same kind of records and you want them in one list. The column order does not need to be identical: Power Query aligns appended fields by column name. A field found in only one source still appears in the result, with null values for rows from the source that lacks that field. Null means no value is present; it is not the same as the number zero or an empty text string.

  1. Click a cell in the first Excel table and choose Data > From Table/Range. The Power Query Editor opens with that table as a query.
  2. Repeat for the second table. If it already appears in the Queries pane, you do not need to create it again.
  3. In the Power Query Editor, select Home > Append Queries > Append Queries as New to create a separate combined query and leave the original queries intact.
  4. In the Append dialog, choose Two tables, select the two queries, and click OK. If combining more than two, choose the option for three or more tables and add each query.
  5. Review the result, then select Home > Close & Load to load it to a worksheet. Use the Close & Load dropdown and choose Close & Load To if you want to select a destination or create a connection only.

Appending does not automatically remove duplicate records. If the same transaction is present in both sources, it will appear twice unless you intentionally remove duplicates or otherwise filter the result. Do not remove duplicates just because two rows look alike: first decide which columns define a genuinely duplicate record.

Merge tables to add matching details

Use merge when a row in one table needs information from another. For example, a sales query can be the left table and a product query the right table. Matching ProductID values let you bring ProductName and Category into the sales result. The matching fields are called keys. You can match on one field or select multiple fields when a single field does not uniquely identify a record.

  1. Load both Excel tables into Power Query using Data > From Table/Range.
  2. Open the query you want to keep as the main table. Choose Home > Merge Queries to merge into it, or Home > Merge Queries as New to create a new result.
  3. In the Merge dialog, select the second query from the lower list. Click the matching key column in the upper preview and then its counterpart in the lower preview. For a multi-column key, select the columns in the same order in both previews; use Ctrl-click to select additional columns.
  4. Choose a join kind, check the match indicator shown in the dialog, and click OK.
  5. In the resulting column for the second query, select the expand button in the column header. Choose the fields to add, optionally clear the option to use the original column name as a prefix, and confirm.

After expansion, check the row count and the new fields. A left outer merge keeps every row from the first, or left, table and adds matching values from the second table. If no match exists, expanded fields show null. A merge does not simply “look up one value” in all circumstances: if the right table has multiple rows with the same key, each matching combination can produce additional rows.

Understand join kinds and match behavior

The join kind controls which rows remain in the merged result. Power Query offers several common choices:

  • Left outer: all rows from the first table, plus matching rows from the second. This is a common choice when enriching a primary list.
  • Right outer: all rows from the second table, plus matching rows from the first.
  • Full outer: all rows from both tables, with matching values combined where possible.
  • Inner: only rows with a match in both tables.
  • Left anti: rows in the first table that have no match in the second.
  • Right anti: rows in the second table that have no match in the first.

Anti joins are useful for finding gaps, such as product IDs in sales that are absent from a product list. They do not return the matched records. A frequent misconception is that a merge always keeps every row from both inputs. It does not: the selected join kind determines which rows survive, so choose it deliberately.

Check keys, data types, and duplicates

Many merge problems come from values that appear identical but are not actually equal. A key might be stored as text in one table and as a number in the other. Spaces before or after a value, inconsistent capitalization in some workflows, punctuation, or different ID formats can also prevent expected matches. In the Power Query Editor, check the type icon beside each column name and set corresponding key columns to compatible types.

For identifiers that may contain leading zeros, such as 00127, use text rather than a number. Numeric conversion would turn that value into 127, losing information that may be important to the match. If values contain unwanted spaces, Power Query’s Transform tools include Format > Trim and Format > Clean for text columns. Apply cleanup consistently to both key columns and review a sample before relying on the output.

Check whether the lookup-side key is unique. If ProductID 42 occurs once in the product table, a sale with ProductID 42 normally matches one product record. If it occurs three times, that sale can match three records and appear three times after expansion. You can inspect duplicates by selecting the key column and using Home > Keep Rows > Keep Duplicates, or group the data to count occurrences. Resolve duplicates according to the meaning of the source data rather than arbitrarily deleting rows.

For an append, inspect column names and data types instead. “Order Date” and “OrderDate” are separate fields unless you rename one so the names agree. A column that contains numbers in one table and text in another may also need a deliberate type decision. Power Query may insert automatic type-conversion steps; review them, especially if a conversion produces errors or removes leading zeros.

Load the result and refresh it

Choose Home > Close & Load to send the query result to Excel, usually as a new worksheet table. If you do not need a visible worksheet result, Close & Load To lets you choose options such as loading as a connection or adding the data to the Data Model, where available. The choice depends on how you plan to use the output; a worksheet table is convenient for inspection, while a connection can be useful when the query feeds other queries or reports.

When the source tables change, refresh the result with Data > Refresh All, or right-click the loaded output and choose Refresh. A refresh reruns the saved query steps against the sources; it does not mean you must rebuild the append or merge. If you add rows inside an Excel table, the table normally expands to include them. If you put new data outside its boundaries, first expand the table or the query may not include those rows.

Keep the query steps understandable. In the Query Settings pane, each applied step can be selected to see how it changes the data. Rename queries and steps when their default names are unclear, and avoid changing the source table’s structure without checking dependent queries. Renaming or removing a source column can cause a later step that expects that field to fail on refresh.

Power Query versus copy-and-paste or lookup formulas

Copy-and-paste may be sufficient for a one-time task with a small, stable dataset, but it creates a static result that must be rebuilt when the sources change. Power Query is more suitable for repeatable cleaning and combining: it records transformations, supports refresh, and can handle multiple sources. It is not a replacement for every formula. A lookup formula can be more convenient when a worksheet needs an immediately visible result tied to a particular cell, while Power Query is often better for preparing a complete table for analysis.

Append and merge also differ from simply placing two tables side by side. Side-by-side placement assumes the rows already correspond and are in the same order. A merge uses key values to establish the relationship, so it is safer when row order differs. If there is no reliable shared key, a merge cannot infer which records belong together; creating or correcting an identifier is a data-preparation task, not something the join can guess.

Power Query is included in Excel for Microsoft 365 on Windows. Excel for Mac also includes Power Query in supported Microsoft 365 versions, but available connectors and some interface details can differ by build. The Windows ribbon paths above may not appear exactly the same on Mac; check the Data tab and the Power Query Editor commands available in your version. Excel for the web has more limited Power Query authoring and refresh capabilities than desktop Excel, so use the desktop app if a command or connector is unavailable.

Frequently Asked Questions

Can Power Query combine two tables with different column names?
Yes. For an append, columns are aligned by name, so differently named fields are treated as separate columns until you rename them to a consistent name. For a merge, the selected key columns can have different names, but their values and types must correspond.

Does Power Query remove duplicates when I append tables?
No. Append stacks the rows and retains duplicates. If duplicates are not wanted, define what counts as a duplicate and remove them in a separate step, or correct the source data.

Why does my merge create more rows than the first table?
The second table may contain more than one row for a selected key. Each matching combination can be returned. Check whether the key is unique on the lookup side and decide how duplicate records should be handled.

Why are some values missing after a merge?
The keys may not match exactly, the join kind may exclude unmatched records, or the key columns may use incompatible types. Check for extra spaces, leading zeros, inconsistent formats, and whether you expanded the intended columns.

Will a Power Query result update automatically when I edit a source table?
Usually you need to refresh the query, for example with Data > Refresh All. You can also configure refresh behavior through the query’s connection properties in desktop Excel, but a refresh is not the same as a live formula recalculation.

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.