Power Query Union Tables: How to Append Queries

To union tables in Power Query, use Append Queries: it stacks the rows from one query beneath the rows from another, matching columns by their names. In Excel for Microsoft 365 on Windows, open the queries in Power Query Editor, choose Home > Append Queries (or Append Queries as New), select the tables, and confirm. Appending keeps duplicate rows; it does not deduplicate them.

What a union means in Power Query

“Union” usually means combining records from multiple tables into one longer table. For example, imagine separate January and February sales tables, each with columns for Date, Product, and Amount. A union puts the January rows and February rows together in one result, rather than adding February’s fields beside January’s. In Power Query, the operation for stacking those rows is called Append.

Append is closest to SQL’s UNION ALL, not necessarily SQL’s UNION: it retains repeated rows. If both monthly tables contain the same sale record, that record appears twice in the appended result unless you deliberately remove duplicates afterward. This matters when duplicate-looking records are legitimate, such as two separate transactions with identical dates, products, and amounts.

Power Query aligns fields using column names, not their left-to-right positions. If one table has columns named Date, Product, and Amount in a different order from another, the values still go under the matching names. A column present in only one input is included in the result; rows from an input without that column receive a null value in it.

How Append Queries works

Power Query treats each input as a query that returns a table. Append takes the rows from the selected query results and combines them into a new table-shaped result. You can append two queries or choose the option for three or more. The inputs do not have to be Excel worksheets: they may be queries created from tables, text files, databases, or other supported sources.

Column names determine how fields line up, so matching names are important. “Customer ID” and “CustomerID,” for instance, are different names. The resulting table can therefore contain both columns, with nulls where a particular source did not supply that exact field. Power Query also carries data types as part of the query steps. If corresponding columns have incompatible values or types, you may need to set a consistent type or handle conversion errors explicitly.

Append usually creates a new step in the query rather than physically copying values into a worksheet immediately. The appended result is available in the editor, where you can inspect it and add transformations. When you load the query, Excel writes the result to a worksheet table or another destination available in your setup. On refresh, Power Query reruns the steps against the source data.

Append two or more Excel tables

For a typical workbook, first make each source range an Excel table. Select a cell in a range and choose Insert > Table, or use Home > Format as Table. Confirm that the range has headers if it does. Give each table a clear name on the Table Design tab, such as Sales_January and Sales_February. Table names make it easier to identify the sources later.

Select a cell in the first table and choose Data > From Table/Range. If prompted, confirm that the table has headers. Power Query Editor opens; choose Home > Close & Load To or close and load as a connection if you want to prepare queries before creating the final output.
Repeat for the other source tables. Each source should appear as a query in the Queries pane. If a query already exists, you do not need to import it again.
In Power Query Editor, choose Home > Append Queries > Append Queries as New to preserve the source queries and create a separate combined query. Choose Two tables for two inputs, or Three or more tables when appropriate.
Select the queries to append, check that they are the intended inputs, and choose OK. Inspect the preview, especially the column names, row count, and any nulls or errors.
Rename the new query to something meaningful, such as AllSales. Choose Home > Close & Load, or use Close & Load To to select a destination. Refresh the result after changing source tables.

The exact import label can vary slightly with Excel builds and the source type. In the Power Query Editor, the append command is on the Home tab. On Excel for Mac, Power Query features and supported sources can differ by Microsoft 365 version, and ribbon labels or editor options may vary. Check that your Mac build exposes the relevant query-editing and append commands; the Windows steps above describe the standard Microsoft 365 workflow.

Prepare columns before combining tables

Spend a moment standardizing headers and types before appending. This prevents a result that technically combines the data but is awkward to analyze. Useful checks include:

Use consistent headers. Rename variations such as “Qty” and “Quantity” to one shared name if they describe the same field.
Promote the correct header row. If a source has a title or blank row above its actual headings, remove or skip those rows before promoting headers.
Set compatible data types. Dates should be dates, amounts should be numeric or currency-appropriate numeric values, and identifiers that may contain leading zeros are often best treated as text.
Check for extra fields. A source-specific column is allowed, but decide whether it belongs in the combined output or should be removed.
Look for whitespace and spelling differences. Trailing spaces or inconsistent spelling in a header can create separate columns even when they look almost identical.

For example, if one table stores postal codes as numbers and another stores them as text, automatic type detection may not give you the desired result. Postal codes are labels, not quantities to calculate, and converting them to numbers can remove leading zeros. Set the type deliberately in each source query, or standardize it in the appended query where appropriate. Also distinguish a true null (no value) from text such as “N/A”; they behave differently in filters and calculations.

Append versus Merge

Append and Merge solve different problems. Append adds rows, making a taller table. Merge joins columns by matching a key, such as CustomerID, and is useful when you want to bring a customer name or region onto transaction records. In Power Query Editor, the merge command is Home > Merge Queries; it asks you to select matching columns and a join kind.

Use Append for monthly files with similar fields, regional sales lists with the same structure, or separate lists of the same kind of entity. Use Merge when the rows in one table need related details from another table. For example, appending a product catalog to a sales table would usually be wrong because the rows represent different kinds of records. Merging the catalog into sales using ProductID may be the right approach if you need product descriptions on each sales row.

A useful test is to ask what the output should represent. If it should contain more records of the same type, append. If it should contain extra attributes for existing records, merge. Both operations can be used in the same query when a workflow genuinely needs both, but the order and join keys should be intentional.

Refresh, loading, and growing data

After an appended query is loaded, use Data > Refresh All to refresh its sources and update the output. A refresh reruns the query; it is not a one-time copy. If you add rows inside an Excel table, the table normally expands to include them, and a subsequent refresh can read the expanded table. If you add a new separate table or file, however, the query may need to be edited to include that new source.

For a recurring process with many files that share a structure, consider the folder connector rather than manually creating a separate query for every file. A folder-based query can combine files from a folder, subject to consistent file structure and connector behavior. It can be easier to maintain when new monthly files arrive, but it requires careful handling of unrelated files, sample-file changes, and header consistency. Appending a fixed set of named queries is often simpler for a small, stable workbook.

Choose a load destination based on how the result will be used. A worksheet table is convenient for inspection, formulas, and charts. A connection-only query can keep intermediate results out of worksheets. The Data Model may suit larger or related datasets used in PivotTables, but it introduces a different modeling workflow. Loading a large result to a sheet can run into worksheet capacity limits, so consider the data volume and intended analysis before choosing.

Common problems and how to fix them

The output has unexpected columns: compare the source headers carefully. A spelling, punctuation, or spacing difference can make Power Query treat two intended fields as separate columns. Rename them consistently in the source queries and refresh the preview.

Some cells show null: this is expected when a source does not contain a column that exists in another source. If the nulls are unexpected, verify that the relevant headers match exactly and that the source query is using the intended header row.

The row count is larger than expected: Append preserves duplicates. Check whether the source tables overlap or whether a file has been included twice. Remove duplicates only when the definition of a duplicate is clear; comparing every column is not always the same as identifying a repeated business record.

Values become errors or appear inconsistent: inspect each query’s data type steps and conversion errors. A column containing a mixture of dates and text, for example, may not convert cleanly. Correct the source values or use a deliberate transformation, then refresh and review the output again.

A new source is missing: an append query includes the inputs configured in its steps. Adding another worksheet or query to the workbook does not automatically add it to an existing append. Edit the append step to select the new query, or redesign the process around a folder or another source pattern if inputs are continually added.

Alternatives when Append is not the right fit

Power Query is useful when you want a repeatable import-and-clean process that can be refreshed. For a small, formula-driven stack in a worksheet, Microsoft 365 and Excel 2024 include the VSTACK function. It returns arrays placed vertically, but it does not replace Power Query’s source connections and transformation steps. It can be a good fit for straightforward ranges that change with worksheet formulas; it is less suited to a repeatable data-cleaning pipeline across external sources.

Copying and pasting rows is another option for a one-off task, but it is easy to omit rows, duplicate data, or lose track of what changed. For structured data that will be updated, a query is more auditable: its applied steps show how the output was produced, and refresh can repeat those steps. The best method depends on whether the task is a quick one-time combination or an ongoing process.

Frequently Asked Questions

Does Power Query append tables by matching column position?
No. It aligns fields by column name. Columns with different names remain separate, even if they occupy the same position in their respective tables.

Does Append Queries remove duplicate rows?
No. Append keeps every row from every selected query. Remove duplicates in a separate, deliberate step only if repeated records should truly be excluded.

Can I append more than two tables?
Yes. Choose the three-or-more-tables option in the Append dialog and add the queries you want. You can also append an existing appended query with another query.

Will new rows in my source tables appear automatically?
They appear after a refresh if they are within a source the query already reads, such as an expanded Excel table. New, separate sources must be added to the query configuration.

Should I use Append or Merge to combine two tables?
Append stacks records of the same general kind. Merge matches records using one or more key columns and brings fields across. Decide whether you need more rows or more columns.

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.