To append data in Excel with Power Query, load each source table as a query, open the Power Query Editor, choose Home > Append Queries > Append Queries as New, select the queries to stack, then choose Close & Load. Appending puts rows from multiple tables into one result; it matches columns by their header names, not their positions. The steps below are for Excel for Microsoft 365 on Windows.
Before you start, check that the source tables have headers and broadly consistent column names. You can append tables with different columns, but Power Query will include all the columns in the result and put null values in places where a source has no matching column.
What You’ll Need
- An Excel workbook containing the data you want to combine. The sources can be on different worksheets, provided each is available as an Excel table or Power Query query.
- A header row for each source, with meaningful column names such as Date, Product, and Amount.
- Excel for Microsoft 365 for Windows. Power Query is also available in many Excel for Mac editions, but the available connectors and menu options can differ. Excel for the web has more limited authoring options; use the desktop app if you cannot find the commands below.
- A plan for the output: a new worksheet is usually easiest to review and refresh.
Step-by-Step
-
Prepare each source range as a table. Check that each range has one header row, no merged cells, and no completely blank header names. Click inside the first range and select Insert > Table, or press Ctrl+T on Windows. Confirm that My table has headers is selected, then click OK. Repeat for every range. In Table Design, give each table a clear, unique name, such as NorthRegion and SouthRegion. Avoid putting notes, totals, or blank rows inside the data area.
Using Excel tables is helpful because they can expand as you add rows, and Power Query can use the table name as its source. If your information is already formatted as a table, you do not need to create another one.
-
Load the first table into Power Query. Click a cell in the table, then select Data > From Table/Range. Excel opens the Power Query Editor and creates a query for that table. If a prompt asks you to confirm the table has headers, check the setting and continue. In the editor, look through the column names and a few rows to confirm that the intended range was captured.
To add another source, close the editor with Home > Close & Load if needed, click in the next table, and choose Data > From Table/Range. Repeat for each source. The queries should appear in the Queries & Connections pane. If a source is already a query, you can use it without creating a second query from the worksheet.
-
Check the queries before combining them. In Power Query Editor, inspect each query for the same kind of information: for example, monthly sales records should have the same basic fields in every query. Rename inconsistent headers so that the same field has the same spelling in each source. For example, change Qty in one query to Quantity if that is the column name used by the others.
Also review data types. A date column should be a date in all sources, and a quantity column should be numeric. Select a column and use the data type icon beside its name, or choose Transform > Data Type. A value stored as text in one query and as a number in another can cause confusing results or conversion errors after the append.
-
Open the Append command. In the Power Query Editor, select Home > Append Queries > Append Queries as New. Choose Append Queries as New to create a separate combined query while leaving the original source queries available. Use Append Queries without as New only if you deliberately want to add rows to the query that is currently open.
If you are in Excel rather than the editor, open a query from the Queries & Connections pane by double-clicking it. This brings you back to Power Query Editor, where the append command is available.
-
Choose the queries to stack. In the Append dialog, choose Two tables if you are combining two queries, then select a query in each list. For three or more, choose Three or more tables and move the required queries into the Tables to append list. Select each query by its name, not by the worksheet it came from, and confirm that you have included every intended source exactly once. Click OK.
Power Query adds the rows from the selected queries into one result. It does not match records across tables or remove duplicate rows. The column names determine which values line up: if one query has a column called City and another calls the equivalent field Town, those will appear as separate columns until you rename one of them to match.
-
Review the appended result. Check the preview for the expected columns and records. Scroll down to confirm that rows from each source appear, and look for nulls, errors, or unexpected blank values. A null can be normal when one source does not contain a particular column; it can also reveal a misspelled header. Use the filter arrows in the preview to inspect individual values, and check the data type icons for each column.
If you spot a problem, return to the relevant source query in the Queries list, correct the header, type, or source step, and then inspect the appended query again. Power Query records these changes as steps, so you can revise the process without manually rebuilding the combined table.
-
Load the combined query to a worksheet. In Power Query Editor, select Home > Close & Load to load the result using the default settings, usually as a table on a new worksheet. To choose where it goes, select Home > Close & Load > Close & Load To, choose Table, and select New worksheet or Existing worksheet. If you choose an existing worksheet, specify a clear starting cell where the output will not overwrite other information.
The combined table is the query output. Avoid typing permanent corrections directly into it: a refresh can replace the displayed results. Make corrections in the source data or in the query steps instead.
-
Refresh after the source data changes. Add or update rows in the source tables, then select Data > Refresh All to update the appended result. You can also right-click the combined table or its query in Queries & Connections and choose Refresh. If you add a new source table that was not part of the original append, edit the append query and include it; Refresh All does not automatically add a newly created query to the append list.
Common Mistakes
- Using Append when you need Merge. Append stacks records vertically, such as January rows followed by February rows. If you need to bring fields from one table alongside matching records in another, such as adding a customer address to each order using Customer ID, use Merge Queries instead.
- Assuming columns match by position. Power Query aligns fields by column name. If Amount is the second column in one query and the fourth in another, the values still go into Amount, as long as the headers match. If names differ, the result may contain two columns rather than one.
- Including the header row as data. If a source was imported incorrectly, its header text may appear as a row in the output. Check that the table has a proper header row and that Power Query recognizes it as headers.
- Forgetting to include a query. The append dialog only combines the queries selected there. A new worksheet or table will not join the result by itself. Reopen Append Queries as New or edit the append step to add it.
- Editing the loaded output by hand. The worksheet table is generated from the query. Manual edits can be lost or become inconsistent when you refresh. Correct source records or apply a transformation in Power Query.
- Expecting duplicates to disappear. Append retains repeated rows. If duplicates are unwanted, identify which columns define a duplicate, then remove duplicates in the appropriate query using Home > Remove Rows > Remove Duplicates.
Tips
- Use consistent names for tables and queries, such as Sales_January and Sales_February. Clear names make the Append dialog easier to use and maintain.
- Standardize column names and data types in each source query before appending. Doing this upstream makes it easier to spot problems and gives the combined query a predictable structure.
- Keep the source queries as connection-only if you do not need separate worksheet copies. In Close & Load To, choose Only Create Connection for source queries, then load the final appended query as a table. This can keep a workbook tidier.
- Give the final query a descriptive name, such as AllSales, in the Query Settings pane. This makes it easier to identify in Queries & Connections and in later reports.
- For recurring data, keep adding records to the source Excel tables and refresh the query. If each new period arrives as a separate file or table, plan how that source will be added to the query rather than assuming Power Query will discover it automatically.
- Save the workbook after setting up the query. Power Query stores the steps with the workbook, so you can reopen and adjust the process later.
Troubleshooting
The Append option is unavailable. Make sure you are in Power Query Editor, not just viewing the worksheet. Open a query from Data > Queries & Connections. If the query list is empty, first create queries from your tables with Data > From Table/Range.
The output has extra columns or many nulls. Compare the headers in each source query character by character. Similar names such as OrderDate and Order Date are different column names. Rename equivalent fields consistently, then refresh or review the append result.
Some rows are missing after refresh. Check whether the source query points to an Excel table or to a fixed cell range. A fixed range may exclude rows added below it. Convert the source range to a table, confirm that new rows are inside the table, and refresh. Also verify that the source query has not applied a filter that removes those records.
Refresh reports a conversion error. Find the column and row mentioned in the error details. One source may contain text such as N/A in a column that Power Query expects to be numeric, or a date in an unexpected format. Correct the source value or adjust the type-conversion step, then refresh again.
The new query is not included. Adding a table to the workbook does not change an existing append definition. Open the appended query, edit its Source or append step as applicable, and select the new query in the Append dialog. If the menus differ on Mac or Excel for the web, use desktop Excel for Windows to follow the same editor paths.
Frequently Asked Questions
Does append combine rows or columns?
It combines rows. The result contains records from one query followed by records from the next. To match rows and bring columns together based on a shared key, use Merge Queries.
Do the source tables need identical columns?
No. Power Query includes the columns it finds across the selected queries. A source without a particular column contributes null values for that field. For a cleaner result, use consistent headers for fields that represent the same information.
Can I append more than two tables?
Yes. In the Append dialog, select Three or more tables and add each query to the list. Check the list before clicking OK so you do not leave out a source or include one twice.
Will the appended table update automatically?
It updates when you refresh the query, for example with Data > Refresh All. New rows in a source table can be picked up on refresh, but a newly created source query must be added to the append definition first.
Can I remove duplicate rows after appending?
Yes. In Power Query Editor, select the columns that define a duplicate, then choose Home > Remove Rows > Remove Duplicates. If you select every column, only rows identical across all selected columns are removed.
Related
- Find Duplicate Names in Excel: Methods That Work Well
- Find and Replace Tab Names in Excel: What Actually Works
- Excel Fill Down Shortcut: Use Ctrl+D to Copy a Column
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Make 2 Columns in Word for Part of a PageTo make only part of a page two columns in Word, select the text, then choose Layout >…
- How to Calculate Row Percentage in ExcelTo calculate a row percentage in Excel, divide each value by the total of the values in that…
- How to Change View Options in Outlook for a Better InboxTo change how your inbox looks in Outlook for Microsoft 365 on Windows, open the folder you want…
- Excel Paste Special Shortcut: Keys and OptionsIn Excel for Microsoft 365 on Windows, copy the cells, select the destination, and press Ctrl+Alt+V to open…
- Excel Keyboard Shortcut to Select a Row in Any WorksheetIn Excel for Microsoft 365 on Windows, press Shift+Space to select the entire worksheet row containing the active…
- Excel Power Query Merge Table Types and When to Use ThemExcel Power Query offers six merge join types: Left outer, Right outer, Full outer, Inner, Left anti, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.