Yes, you can append data to an existing table in Excel using Power Query, but the method depends on whether you want the new rows to appear in the original table or in a separate query result. The most straightforward approach is to use the Append Queries feature, which combines rows from two or more tables into one query, then load that combined result into a new or existing worksheet table.
What “Append” Means in Power Query
In Power Query, “append” means stacking rows from one query or table on top of another, similar to SQL’s UNION ALL. This is different from a merge, which joins columns based on a key. Append is used when you have multiple tables with the same columns (or compatible columns) and you want to consolidate them into one dataset. For example, you might have monthly sales tables (January, February, March) and want a single year-to-date table.
How to Append to an Existing Table (Step-by-Step)
There are two common scenarios: appending data to a table that already exists in your workbook, and appending a new query to an existing query. Here’s how to do both.
Scenario 1: Append a New Query to an Existing Query
- Go to the Data tab and select Get Data > Launch Power Query Editor.
- In the Queries pane, select the query that represents your existing table (e.g., “Sales2024”).
- Go to Home > Append Queries > Append Queries as New (or just Append Queries if you want to append to the current query).
- In the dialog, choose “Two or more tables” and select the other query (e.g., “Sales2025”). Click OK.
- The new query will appear in the Queries pane. If you used “Append Queries as New,” rename it (e.g., “AllSales”).
- Load the result by going to Home > Close & Load > Close & Load To, then choose where to place it (e.g., a new worksheet or an existing cell).
Scenario 2: Append to an Existing Worksheet Table
If you already have a table on a worksheet (created via Insert > Table) and you want to add more rows to it, you can’t directly “append” to that table with Power Query without reloading. Instead, you should:
- Convert your existing table into a named range or keep it as a table, but create a query that references it.
- Use the append method above to combine that table with another query.
- Load the combined result to a new location, then either replace the old table or copy the new rows into it manually.
If you want the original table to be updated automatically, consider loading the appended query to a new sheet and then using a formula or a second query to reference that new sheet.
Using the Append Command vs. Writing M Code
The visual interface is fine for simple appends, but for more control (like appending many tables dynamically), you can use the M language. For example, the M code for appending two queries is:
let
Source = Table.Combine({Sales2024, Sales2025})
in
Source
This is equivalent to the Append command. To append a list of tables, you can use Table.Combine with a list of table names. This is useful when you have a folder of files and want to append them all.
Choosing Between Append and Merge
Append is for adding rows; merge is for adding columns. If you need to bring in related information from another table (like customer names to a sales table), use Merge Queries (Home > Merge Queries). If you just need to stack data, use Append. A common mistake is using merge when you actually need append, which results in duplicated or mismatched rows.
Common Problems and How to Fix Them
- Column mismatch: If the columns in the two tables don’t have exactly the same names or order, Power Query will still append but may create extra columns or leave blanks. Ensure column names match exactly, or use the Choose Columns step to align them.
- Data type conflicts: If one column is text and the other is number, append may fail. Change the data type of one column to match the other before appending.
- Duplicates: Appending doesn’t remove duplicates. If you need unique rows, use Remove Duplicates after appending.
- Appending to a table that already has data: If you load an appended query to a cell range that already has data, Excel will ask if you want to overwrite. It’s safer to load to a new sheet and then copy.
Refreshing and Automating the Append
One of the biggest advantages of Power Query is that the append is refreshable. If you update the source data (e.g., add new rows to the original tables), you can right-click the query and select Refresh to update the appended result. To automate, you can set the workbook to refresh on open (Data > Queries & Connections > Properties > Refresh data when opening file). This is especially useful when appending data from external sources like CSV files or databases.
Frequently Asked Questions
Can I append to an existing table without creating a new query?
Yes, if you use the Append Queries command (not “Append Queries as New”), it will modify the current query in place. However, this still creates a query that you must load to a worksheet. There is no way to directly “push” rows into an existing worksheet table without reloading the whole table.
What if my tables have different column orders?
Power Query will align columns by name, not by position. So if the columns have the same names but in different order, the append will still work correctly. If the names differ, you’ll get separate columns for each unique name, which is usually not what you want.
How do I append all files in a folder?
Use Get Data > From File > From Folder. Power Query will combine all files in that folder, and you can then append them into one table. This is a common way to consolidate monthly reports.
Does appending change the original source data?
No, Power Query reads the source data and creates a new query result. It never modifies the original source files or tables. The only exception is if you load the result back into the same worksheet range, which will overwrite that range.
Can I append more than two tables at once?
Yes, in the Append dialog, choose “Three or more tables” and select all the tables you want to combine. The M code will use Table.Combine with a list of all selected tables.
Alternatives to Power Query for Appending
If you don’t want to use Power Query, you can manually copy and paste rows, or use the Consolidate feature (Data > Consolidate) for summing values, but that only works for numeric data. Another option is to use a formula like =VSTACK(range1, range2) in Excel 365, which stacks arrays vertically. For example, with data in A1:C10 and D1:F20, =VSTACK(A1:C10, D1:F20) returns a combined array. This is a dynamic array formula that updates automatically if the source ranges change, but it doesn’t handle data type conversion or cleaning like Power Query does.
Related
- Excel Eye Center Provo: Services, Doctors & What to Know
- Excel: Count Cells with Specific Text
- Excel Online Schedule Template: How to Use and Find
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Not Keeping Formatting? Causes and FixesIf Excel is not keeping formatting, first check whether the change comes from conditional formatting, a table style,…
- Wrap Text in Excel: Keyboard Shortcuts and Quick FixesIn Excel for Microsoft 365 on Windows, select the cell or cells and press Alt, H, W—one key…
- Can You Unmerge Cells in Excel? Here’s What HappensYes. In Excel for Microsoft 365 on Windows, select the merged cell or cells, then choose Home >…
- Excel Scatter Plot With Two Y-Axes: How to Make OneYes. In Excel for Microsoft 365 on Windows, create an XY scatter chart, select the data series that…
- Excel Named Range Scope Greyed Out? Causes and FixesIf the Scope field is greyed out while you edit a named range in Excel, that is usually…
- How to Make a Poster Board in PowerPoint for PrintingTo make a poster board in PowerPoint, set a custom slide size to match the finished poster, arrange…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.