Excel Power Pivot does not have a command for appending tables. In Excel for Microsoft 365 on Windows, use Power Query’s Append Queries command to combine the rows, then load the resulting query to the Data Model for use in Power Pivot.
What “append” means in Power Pivot
Appending stacks records from one table below records from another. For example, if one Excel table contains January sales and another contains February sales, appending them creates one table with January and February rows. The tables should describe the same kind of thing, such as sales transactions, even if their records cover different months, regions, or source files.
Appending is different from merging. An append adds rows; a merge joins tables side by side using one or more matching columns, much like a lookup. If your goal is to add a customer name to each sales record using a customer ID, merge the queries or create a relationship. If your goal is to combine this month’s sales rows with last month’s rows, append them.
Power Query is the data-preparation tool that performs the append. Power Pivot is used afterward to work with tables in the Excel Data Model, create relationships, and analyze the model with PivotTables and measures. So the practical workflow is prepare and append in Power Query, then model and analyze in Power Pivot.
Prepare the source tables
Start with a consistent structure. Each source should have a header row, and corresponding fields should use the same column names. For instance, two sales tables might both include OrderDate, ProductID, Quantity, and SalesAmount. The tables do not need to contain the same number of rows.
In Excel, turn each source range into a table by selecting it and choosing Insert > Table, or press Ctrl+T on Windows. Confirm that the table has headers, then give it a clear name under Table Design > Table Name, such as Sales_Q1 and Sales_Q2. Meaningful names make the query list easier to understand later.
Next, create a Power Query connection for each table. Select a cell in the first Excel table and choose Data > From Table/Range. In Power Query Editor, check the headers and data types, then choose Home > Close & Load > Close & Load To… if you want to set its destination. Repeat for the other source. You can also make queries from suitable external sources through the Data tab; the important requirement is that each source is represented by a query.
Append queries step by step
- Open Power Query Editor by selecting a query and choosing Data > Queries & Connections, then double-clicking the query, or by using Data > Get Data to open the editor.
- In Power Query Editor, choose Home > Append Queries > Append Queries as New. “As New” creates a separate combined query and leaves the source queries available for later changes.
- Choose Two tables for two inputs, or Three or more tables when appending several queries. Select the queries to include and confirm.
- Review the preview. Check that the expected fields appear and that records from each source are present. Rename the new query to something descriptive, such as Sales_All.
- Choose Home > Close & Load > Close & Load To…. In the Import Data dialog, choose Only Create Connection and check Add this data to the Data Model, then confirm. If the dialog or available choices differ because of how the query was first created, use its load settings to set the same destination.
To confirm that the result is in the model, open the Power Pivot tab and select Manage. The combined table should appear in the Power Pivot window. If you do not see the Power Pivot tab, check whether the Power Pivot add-in is enabled in Excel’s add-in settings; availability can depend on the Excel edition and installation.
How Power Query matches columns
Power Query aligns appended fields by column name, not column position. If one query lists ProductID before OrderDate and another lists OrderDate before ProductID, the values still go into the matching named columns. That behavior is useful when source layouts differ slightly, but it makes inconsistent headers an important source of mistakes.
When a column exists in one source but not another, the appended result still includes that column; rows from the source without it have a null value there. If one source has a misspelled or differently named field, Power Query treats it as a separate column. For example, “CustomerID” and “Customer Id” will not automatically be recognized as the same field. Rename the columns in the source queries before appending if they represent the same information.
- Matching names: values from each source are placed in the same output field.
- Missing fields: the output contains the field, with nulls for rows whose source did not have it.
- Different data types: verify the type in the combined query, especially for dates, IDs, and amounts. A product code such as 00125 should generally be treated as text if its leading zeros matter.
A quick check of the first few rows is not enough when the sources are large or have varied formats. Inspect column quality and sample records, and test the result with a PivotTable or a row count against the source totals. Also consider whether blank values are genuinely missing data or a sign that a column name or source format needs correction.
Append versus relationships in the Data Model
Appending two fact tables can be a good choice when they have the same business meaning and you want to analyze all their records together. For example, monthly sales extracts with the same fields can become a single Sales table. That can make a PivotTable simpler because it needs one set of sales fields rather than separate fields for each month.
Do not append tables merely because they share a few columns. A Sales table and a Budget table may both have Month and Department, but their rows represent different measures and their other fields may have different meanings. They are often better kept as separate tables and connected to shared lookup tables, such as Calendar and Department, with relationships in the Data Model. The correct design depends on what each row represents and how you need to compare the data.
Appending can also be appropriate for same-shaped tables from different regions or branches, provided their definitions and units agree. If one source records dollars and another records cents, or one uses a different meaning for a field, standardize that data before combining it. An append does not resolve business-rule differences or create relationships automatically.
Refresh behavior and ongoing maintenance
After an append query is set up, refresh it when its source data changes. Use Data > Refresh All in Excel to refresh workbook queries and connections. The combined query then reads the updated results from its source queries, and its load to the Data Model is updated as part of the refresh process.
For Excel tables in the same workbook, adding rows to the source table is usually straightforward: make sure the new records are inside the table, then refresh. If an external file is the source, the query must still be able to reach the file at the saved location. Moving or renaming a file, changing a folder, or changing the source column names can cause refresh errors or alter the output structure.
Keep the source queries and the appended query clearly named. If the append is built as a new query, avoid editing the source queries in ways that unintentionally remove fields required by the combined output. For a recurring monthly process, use a stable folder and consistent file layouts; if the number of files will grow, consider a folder-based import and transformation rather than manually creating a new query for every file.
Common problems and practical fixes
- The result has extra columns: compare source headers for spaces, punctuation, spelling, or inconsistent naming. Rename equivalent fields consistently in Power Query.
- Some output cells are null: check whether that source truly lacks the field or whether its header differs from the other query’s header.
- Dates or numbers look wrong: set suitable data types in the source queries and check regional date formats. A value imported as text will not behave like a date or number in every calculation.
- The combined table is missing from Power Pivot: review the query’s load settings and ensure Add this data to the Data Model is selected. A connection-only query is not necessarily in the model unless that option was enabled.
- Refresh fails: check source paths, permissions, renamed worksheets or tables, and whether the source still has the expected headers.
- The append option is unavailable: make sure you are in Power Query Editor and selecting the Home tab’s append command, rather than looking for an append button in the Power Pivot window.
When diagnosing an incorrect total, distinguish a bad append from duplicate records. Appending preserves rows; it does not remove duplicates. If two source queries contain the same transaction, that record will appear twice unless you explicitly design a deduplication step or correct the source. Do not remove duplicates automatically unless the relevant columns reliably identify a duplicate under your business rules.
Excel for Mac and Excel for the web
These instructions target Microsoft 365 Excel for Windows, where Power Pivot and the Excel Data Model workflow are available in supported editions. Excel for the web does not provide the Power Pivot desktop window for building and managing a model. Excel for Mac also does not offer the same Power Pivot Data Model authoring experience as Windows, and Power Query capabilities and destinations differ by platform and version. If you need to append data and load it into an Excel Power Pivot model, use the Windows desktop version with Power Pivot support.
On a Mac or in a browser, you may still be able to combine or transform data using tools available in that environment, but do not assume those steps create a Power Pivot Data Model that can be managed there. Check the current Microsoft 365 feature availability for your specific platform and license before building a workflow around it.
Frequently Asked Questions
Can I append tables directly in Power Pivot?
No. In Excel, append the queries in Power Query and load the combined result to the Data Model. Power Pivot is where you manage and analyze model tables, not where you perform the Power Query append operation.
Should I choose Append Queries or Append Queries as New?
Choose Append Queries as New when you want a separate combined query while retaining the original queries unchanged. Choose Append Queries when you intend to add one query’s rows directly into the query currently being edited.
Do the columns have to be in the same order?
No. Power Query matches columns by name. The names should agree for corresponding fields, however; different names create separate output columns rather than being matched based on their position.
Does appending remove duplicate rows?
No. Append stacks the rows from the selected queries and can retain repeated records. If duplicates are not valid, identify them using an appropriate key and apply a deliberate cleanup step.
Can I append more than two tables?
Yes. In the Append dialog, select Three or more tables and add the queries to the list. Check that all sources have compatible meanings and that the resulting columns are aligned as intended.
Related
- Excel Opposite of Text to Columns: How to Combine Data
- Multiple Project Tracker Spreadsheet Setup and Tips
- How to Present PowerPoint in Zoom With Notes
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Create a Pareto Chart in Excel: Steps, Tips, and ExamplesTo create a Pareto chart in Excel for Microsoft 365 on Windows, select a table containing category names…
- Excel Donut Chart: How to Create, Format, and Use OneAn Excel donut chart displays how categories contribute to a whole, using colored segments arranged around a central…
- Excel Dashboard Styles: Layouts, Uses, and Design TipsExcel dashboard styles are the visual and structural approaches used to present important measures, trends, and comparisons on…
- Gantt Chart Excel Free Template XLS: How to Use OneYes: you can use a free Gantt chart template in Excel, but many current downloads are in the…
- Excel Align Chart to Grid: Snap, Position, and ResizeTo align a chart with Excel’s cell grid in Microsoft 365 for Windows, select the chart and hold…
- Excel Pivot Table Error Message: Causes, Checks, and FixesAn Excel PivotTable error is usually caused by a blank or invalid source-data header, an incorrect source range,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.