To append tables in an Excel Data Model, use Power Query to stack their rows into one combined query, then load that query with Add this data to the Data Model selected. In Excel for Microsoft 365 on Windows, select the source queries in Power Query Editor and choose Home > Append Queries > Append Queries as New.
What appending tables means
Appending combines tables vertically: it places the rows from one table below the rows from another. For example, if January, February, and March sales are stored in separate tables with columns for Date, ProductID, Region, and Sales, an append creates one query containing the records from all three months. The combined query can then be loaded into the Data Model for analysis.
Append is not the same as joining tables side by side. Power Query matches columns by their header names, not by their left-to-right positions. If the January table has a Sales column and the February table has a Revenue column, those become separate columns in the appended result; rows without a value for one of them contain nulls. Consistent names and data types make the outcome easier to use.
The Data Model is Excel’s relational analysis layer. It can hold multiple tables, relationships, and measures for PivotTables. You do not have to append every table in a workbook: tables that describe different things, such as Products and Customers, are often better kept separate and connected to a shared transaction table by relationships.
Prepare the source tables
Before combining sources, make sure the tables represent the same kind of record. Monthly sales tables are a natural append because each row describes a sale. Appending a product catalogue to sales transactions is usually not appropriate: one row describes a product, while the other describes a transaction.
For tables stored in the current workbook, convert each source range into an Excel table using Insert > Table, and give each table a clear name on the Table Design tab. Tables expand as data is added, which helps the query include new rows when refreshed. Check the following before building the append:
- Use the same header for fields that mean the same thing, such as OrderDate in every monthly table.
- Remove title rows, blank header rows, and totals that should not be treated as data.
- Check that dates are dates, amounts are numeric, and identifiers have consistent formats.
- Decide whether a source-only column should remain in the combined result or be renamed to match an equivalent field.
Different tables do not have to contain exactly the same columns. Power Query can include the union of their columns, but absent values are null. That behavior is useful when a source gains a field over time, but it can also reveal accidental inconsistencies that should be fixed before analysis.
Append queries and load the result to the model
- In Excel for Windows, select a cell in the first source table and choose Data > From Table/Range. Power Query Editor opens. Repeat for each source table, or use existing queries that already represent those sources.
- In Power Query Editor, choose Home > Append Queries > Append Queries as New. Choosing the “as New” option preserves the original queries and creates a separate combined query.
- In the Append dialog, select Three or more tables if needed, then add the queries to the list. For two sources, select Two tables. Click OK and inspect the preview for the expected columns and rows.
- Rename the new query to something descriptive, such as AllSales. Verify the column headers and data types, and make any required cleanup in the query before loading.
- Choose Home > Close & Load > Close & Load To…. In the Import Data dialog, select Only Create Connection and check Add this data to the Data Model. Confirm the dialog.
The query can be loaded to a worksheet as well if you need to inspect or share the combined rows there. For a large result, loading only to the Data Model avoids placing the full output on a worksheet, where Excel has a limit of 1,048,576 rows per sheet. A Data Model can hold more rows than a worksheet, although practical capacity depends on available memory and the data’s size and shape.
To analyze the result, choose Insert > PivotTable and select the workbook’s Data Model as the source. The exact wording and dialog layout can vary with the Excel build. The combined query appears as a table in the PivotTable field list. If the model also contains a related Products table, for example, you can use product descriptions alongside sales totals without copying the descriptions into every transaction row.
How Power Query aligns columns
Power Query aligns appended fields by column name. It does not assume that the first column in each source corresponds to the first column in the other sources. This is a helpful safeguard when columns are rearranged, but it means small spelling differences create distinct fields: CustomerID and Customer Id will not automatically be treated as the same column.
For example, imagine one table has Date, Store, and Units, while another has Date, Store, Units, and Promotion. The result contains all four columns. Rows from the first table have null in Promotion; rows from the second keep their promotion values. If the second table instead calls the field Promo, the result will contain both Promo and Promotion until you rename one field.
Data type consistency matters too. A date column imported as text in one query may not behave like a date column in another. In Power Query, select the column and set an appropriate type from the data type control. Avoid converting identifiers to numbers just because they contain digits: a code such as 00421 may need to remain text so its leading zeros are preserved.
Append, merge, or create a relationship?
Choose the operation based on the shape of the question you are trying to answer:
- Append adds rows from similar tables. Use it for monthly files, regional sales extracts, or separate exports with the same kind of records.
- Merge matches rows using one or more key columns and adds fields from another query. It is similar to a database join. For example, merge a transaction query with a product lookup when you specifically need product attributes in the query output.
- Relationship connects separate Data Model tables using a common key without flattening them into one query. A sales table can relate to a Products table through ProductID, allowing a PivotTable to use fields from both.
A common misconception is that appending is required just because several tables need to appear in one PivotTable. That is not necessarily true. If one table contains transactions and another contains product names, keep them separate and create a relationship when the key values and model design support it. Append only when rows from each source belong to one logical table.
Refresh behavior and ongoing updates
Power Query stores the transformation steps, rather than making the append a one-time copy and paste. When the source tables change, use Data > Refresh All to rerun queries and update the model. You can also right-click a query or PivotTable and choose its refresh command. After refreshing, confirm that the PivotTable reflects the new records and that any relationships still work.
If new records are entered inside an Excel table, the table generally expands to include them and the query can pick them up on refresh. If data is pasted below the table but outside its boundary, it may be missed. For files in a folder, a folder-based Power Query workflow can combine files that follow a consistent structure; adding a new matching file and refreshing can then bring its rows into the result. Review the folder query’s filters so unrelated files are not included.
Refresh does not automatically repair a changed source layout. A renamed worksheet, moved file, altered header, or changed data type can cause errors or unexpected columns. Treat the query steps and source naming conventions as part of the reporting process, and check the refresh result when source systems change.
Common append problems
If a field appears twice in the output, compare its source headers character by character, including spaces and punctuation. Rename equivalent fields consistently in Power Query before appending. If the output contains many nulls, check whether sources use different headers or whether the missing values are genuinely absent.
If totals are too high, look for duplicate records, repeated source files, or totals rows accidentally imported as transactions. Append does not deduplicate data; it retains rows from every input. If duplicates should be removed, decide which fields define a duplicate and use Power Query’s Remove Rows > Remove Duplicates carefully. Removing duplicates based on too few columns can discard legitimate records.
If a PivotTable cannot combine fields from two model tables, check that the tables have a valid relationship and that the key column values have compatible types. A relationship is different from an append: it does not add source rows together. Also check that the query was loaded to the Data Model, rather than only to a worksheet or as a connection.
For a slow or unexpectedly large model, check whether the query imports unnecessary columns or rows. Remove fields that are not needed for analysis, and filter out irrelevant records in Power Query. Repeated text values and high-cardinality fields can affect model size, so a lean, appropriately typed dataset is usually easier to refresh and analyze.
Excel platform differences
The steps above are for Excel for Microsoft 365 on Windows. Excel for the web does not provide the full Windows Power Query and Data Model authoring experience, so use desktop Excel for these operations. Excel for Mac has Power Query capabilities in supported Microsoft 365 builds, but it does not provide the same Data Model and Power Pivot functionality as Excel for Windows. If the goal is a PivotTable based on the Data Model, use Excel for Windows.
Menu availability can depend on the installed build, account, and organization settings. If you cannot find the Data Model load option or the model-based PivotTable choices, check that you are using the Windows desktop edition and that the relevant Excel features are available in that installation. Do not confuse a normal worksheet PivotTable with one built from the Data Model: the latter can use multiple related tables and model measures.
Frequently Asked Questions
Can I append tables directly in the Data Model?
Use Power Query to append the source tables into a single query, then load that query to the Data Model. The model stores the result for analysis; Power Query is the usual tool for combining the source rows.
Do appended tables need identical columns?
No. Power Query matches columns by header name and includes columns found in any input. When a source does not contain a column, its rows have null for that field. Consistent names and types still make the combined result more reliable.
Does append remove duplicate rows?
No. Append stacks the rows it receives and keeps duplicates. If duplicates are not valid, identify them and remove them in Power Query as a separate, deliberate step.
Can I append more than two tables?
Yes. In Append Queries as New, choose Three or more tables and add all the queries to the list. You can also append a previously combined query to another query later.
Will a PivotTable update when I append new data?
Refresh the query and model with Data > Refresh All, then refresh the PivotTable if needed. New rows must be inside the source table or included by the query’s source rules to be brought in.
Should I append monthly sales tables or relate them?
If each table contains the same kind of sales records for a different month, append them into one sales table. Relate that fact table to separate lookup tables, such as Products or Stores, when you need descriptive fields from those tables.
Related
- Power Query Editor: What It Is, How It Works, and Why
- Excel Data Validation Icon: What It Looks Like and Does
- Data Validation Multiple Selection in Excel: What Works
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 Columns Smaller in Microsoft Word QuicklyTo make page columns narrower in Microsoft Word, click inside the column text, go to Layout > Columns…
- Outlook Reading View: Change the Reading Pane LayoutTo change Outlook’s reading view, adjust the Reading Pane: in classic Outlook for Windows, go to View >…
- Excel Data Analysis Regression: Run and Interpret ItTo run a linear regression in Excel for Microsoft 365 on Windows, arrange the outcome values in one…
- Outlook Auto Forward: Setup Steps and Common LimitsYes. You can set up automatic email forwarding in Outlook using the account’s forwarding setting or, in classic…
- How to Make a Booklet in Word Back to Back PrintingTo make a back-to-back booklet in Word, set the document’s page layout to Book fold, arrange your content…
- How to See Named Ranges in Excel and Check Their CellsTo see a named range in Excel, open Formulas > Name Manager to view its name and the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.