Appending Queries to Stack Tables in Power BI

Course: Microsoft Power BI Training

Topic: Appending Queries to Stack Tables

Course Link: Microsoft Power BI Training

Demo Link: Register for a Course Demo

1. Introduction to Appending Queries in Power BI

Appending queries is an important data transformation operation in Microsoft Power BI. It combines rows from two or more tables into a single table. The process is commonly described as stacking tables because the rows of one table are placed after the rows of another table.

Appending is particularly useful when data is stored in separate tables that represent the same type of information. For example, a company may maintain separate sales tables for January, February, and March. Instead of analyzing each month separately, the analyst can append the three tables into one consolidated sales table.

Power Query performs appending based on column names rather than the physical position of columns. If the tables contain different column names, the resulting table includes the combined set of columns, and missing values appear as null in the applicable rows. Therefore, standardizing column names and data types before appending is an important best practice.

1.1 Learning Objectives

After completing this topic, learners should be able to:

• Understand the meaning of appending queries and stacking tables.
• Differentiate between append and merge operations.
• Append two tables using Power Query Editor.
• Append three or more tables into a single query.
• Understand the difference between Append Queries and Append Queries as New.
• Prepare tables with consistent column names and data types.
• Append tables containing different columns.
• Use Power Query M to append tables programmatically.
• Validate appended data and identify potential duplicates.
• Apply appending techniques to practical business datasets.

2. What Does Stacking Tables Mean?

Stacking tables means placing the rows of one table underneath the rows of another table to create a combined dataset. This is also known as vertical combination or row-wise combination.

2.1 Example of Stacking Tables

Consider two tables containing sales information.

Table 1: January Sales

Table 2: February Sales

After appending the two tables, the result contains all five rows.

Appended Sales Table

The columns remain the same, while the rows from both tables are combined into one dataset.

2.2 Visual Representation of Appending

January Sales Table       February Sales Table
+---------+------+-----+  +---------+------+-----+
| OrderID |Product|Sales|  | OrderID |Product|Sales|
+---------+------+-----+  +---------+------+-----+
| 1001    |Laptop|45000|  | 1004    |Monitor|12000|
| 1002    |Mouse |  800|  | 1005    |Printer| 7000|
| 1003    |Keyboard|1500| +---------+------+-----+
+---------+------+-----+
            |
            v
       Append Queries
            |
            v
+---------+---------+-------------+
| OrderID | Product | SalesAmount |
+---------+---------+-------------+
| 1001    | Laptop  | 45000       |
| 1002    | Mouse   | 800         |
| 1003    | Keyboard| 1500        |
| 1004    | Monitor | 12000       |
| 1005    | Printer | 7000        |
+---------+---------+-------------+

3. Why Is Appending Queries Important?

Businesses frequently store similar data in separate tables, files, worksheets, databases, or reporting periods. Appending allows analysts to consolidate these records into one table for consistent analysis.

3.1 Common Business Scenarios

• Monthly sales: Combine January, February, March, and subsequent monthly sales records.
• Regional sales: Combine North, South, East, and West region transactions.
• Multiple branches: Consolidate sales data from different store locations.
• Annual reporting: Combine transactions from multiple yearly files.
• Customer records: Combine customer lists from different departments when they represent compatible records.
• Inventory reporting: Stack inventory snapshots or transaction records when their structures and intended meanings are compatible.
• Employee information: Consolidate similar employee tables from multiple offices.
• Online and offline sales: Combine transaction records from different sales channels.

3.2 Benefits of Appending

4. Understanding Append Queries in Power Query

Power Query provides an Append command for combining tables. It is available in Power BI Desktop through Power Query Editor.

4.1 Where to Find Append Queries

1. Open Power BI Desktop.
2. Connect to the required data sources and load the tables into Power Query.
3. Select Transform Data to open Power Query Editor.
4. Open the Home tab.
5. Locate the Combine group.
6. Select the dropdown for Append Queries.
7. Choose Append Queries or Append Queries as New, depending on your requirement.

4.2 Two Append Options

4.3 When to Use Append Queries

Use Append Queries when the selected query is intended to become the combined table and you do not need a separate output query for the operation.

For example, if a query named JanuarySales should become a consolidated sales query containing both January and February transactions, you can append FebruarySales to JanuarySales and then rename the resulting query appropriately.

4.4 When to Use Append Queries as New

Use Append Queries as New when you want to preserve the original queries and create a separate consolidated result.

For example, you may keep JanuarySales and FebruarySales unchanged while creating a third query named AllSales. This approach is often easier to maintain because the source queries remain separate from the consolidated output.

5. Appending Two Tables Step by Step

This section explains how to append two tables using the Power Query interface.

5.1 Example Scenario

A retail business has two tables:

• OnlineSales: Sales made through the website.
• StoreSales: Sales made through physical stores.

Both tables contain OrderID, OrderDate, Product, and SalesAmount. The company wants to create one table for overall sales analysis.

5.2 Step-by-Step Procedure

1. Open Power BI Desktop.
2. Select Get Data and connect to the sources containing OnlineSales and StoreSales.
3. Select Transform Data to open Power Query Editor.
4. Verify that both queries appear in the Queries pane.
5. Select the OnlineSales query.
6. Go to Home and open the Append Queries dropdown.
7. Select Append Queries or Append Queries as New.
8. In the Append dialog, select Two tables.
9. Choose OnlineSales as the primary table if it is not already selected.
10. Choose StoreSales as the table to append.
11. Select OK.
12. Review the resulting table to confirm that rows from both queries are present.
13. Rename the consolidated output to AllSales if appropriate.
14. Select Close & Apply after validating the result.

5.3 Expected Result

The resulting query contains all rows from OnlineSales followed by the rows from StoreSales in the append operation's logical table order. The source queries can remain separate if Append Queries as New is used.

Appending does not automatically remove duplicate records. If the same transaction exists in both tables, it can appear more than once in the appended result.

6. Appending Three or More Tables

Power Query can append more than two tables in a single operation. This is useful when data is divided across multiple months, regions, departments, or business units.

6.1 Example Scenario

A company maintains three monthly sales tables:

• JanuarySales
• FebruarySales
• MarchSales

The company wants to create one quarterly sales table.

6.2 Step-by-Step Procedure

1. Load the three tables into Power Query Editor.
2. Open the Home tab.
3. Select Append Queries as New.
4. In the Append dialog, select the Three or more tables option.
5. Review the Available table(s) list.
6. Select JanuarySales and choose Add.
7. Select FebruarySales and choose Add.
8. Select MarchSales and choose Add.
9. Confirm that all three tables appear in the Tables to append list.
10. Select OK.
11. Rename the new query to QuarterlySales.
12. Validate the column structure, row count, data types, and totals.
13. Select Close & Apply when the output is correct.

6.3 Example of the Result

Assuming the source tables contain the stated number of rows and no rows are filtered out during the append process, the combined result contains 370 rows.

7. How Power Query Matches Columns During Appending

One of the most important concepts in appending is that Power Query matches columns by their names, not by their positions in the source tables.

7.1 Matching Columns by Name

Consider two tables with the same column names in different orders.

Table A

Table B

After appending, the values are aligned according to their matching column names. The fact that SalesAmount appears first in Table B does not cause the value 800 to be placed under OrderID.

7.2 What Happens When Column Names Differ?

If two tables contain different column names, Power Query creates a result containing the union of the column names. A source row receives null for any output column that does not exist in that source table.

Example:

Table A: Store Sales

Table B: Online Sales

Appended result

Although the two amount columns may represent the same business concept, Power Query treats SalesAmount and Revenue as different columns because their names differ.

7.3 Best Practice: Standardize Column Names

Before appending tables, standardize equivalent column names. For example, rename Revenue to SalesAmount if both columns represent the same measure and use compatible units and definitions.

Do not rename columns merely because their names appear similar. Confirm that the fields represent the same concept and use compatible units, granularity, and business definitions.

8. Preparing Tables Before Appending

Appending works best when the source tables have a consistent structure. Data preparation before appending reduces errors and prevents unnecessary null values.

8.1 Standardize Column Names

Make equivalent columns use consistent names.

8.2 Standardize Data Types

Equivalent columns should generally use compatible data types before appending.

If one table stores SalesAmount as text and another stores it as a number, the resulting column may have inconsistent values or require further conversion. Convert the columns deliberately and investigate conversion errors.

8.3 Remove Unnecessary Columns

If the source tables contain unrelated fields, decide whether they are needed in the consolidated output. Removing unnecessary columns can simplify the resulting schema, but keep fields required for auditing, matching, or downstream analysis.

8.4 Clean Inconsistent Values

Standardize values such as region names, status labels, and product categories where they represent the same concept.

For example, values such as "West", "WEST", and "West " may need to be standardized before analysis. This cleaning step is separate from the append operation itself.

9. Appending Queries Using Power Query M

Power Query M is the formula language used to define query transformations. The Table.Combine function combines a list of tables into one table and is commonly used for appending queries programmatically.

9.1 Appending Two Tables

Suppose two existing queries are named JanuarySales and FebruarySales. The following example creates a combined result.

let
    AppendedSales = Table.Combine(
        {
            JanuarySales,
            FebruarySales
        }
    )
in
    AppendedSales

This expression combines the rows from both queries. The column structure of the result is based on the columns present in the input tables.

9.2 Appending Three Tables

To combine January, February, and March sales, include all three query results in the list passed to Table.Combine.

let
    QuarterlySales = Table.Combine(
        {
            JanuarySales,
            FebruarySales,
            MarchSales
        }
    )
in
    QuarterlySales

The example assumes that the three queries already exist and return tables.

9.3 Appending More Tables

The same pattern can be used with more than three queries.

let
    AnnualSales = Table.Combine(
        {
            JanuarySales,
            FebruarySales,
            MarchSales,
            AprilSales,
            MaySales,
            JuneSales,
            JulySales,
            AugustSales,
            SeptemberSales,
            OctoberSales,
            NovemberSales,
            DecemberSales
        }
    )
in
    AnnualSales

This pattern is suitable when the monthly queries already exist and are maintained as separate queries. For recurring files, a folder-based workflow may be easier to maintain than creating a new query for each month.

9.4 Adding a Source Label Before Appending

When combining regional or channel-based tables, it can be helpful to add a column identifying the source of each record. This makes it easier to compare performance across sources after appending.

let
    OnlineWithChannel = Table.AddColumn(
        OnlineSales,
        "SalesChannel",
        each "Online",
        type text
    ),
    StoreWithChannel = Table.AddColumn(
        StoreSales,
        "SalesChannel",
        each "Store",
        type text
    ),
    AllSales = Table.Combine(
        {
            OnlineWithChannel,
            StoreWithChannel
        }
    )
in
    AllSales

The resulting table includes a SalesChannel column that identifies whether a record came from OnlineSales or StoreSales. Ensure that the source queries contain the expected fields before applying the transformation.

9.5 Standardizing Columns Before Combining with M

The following example renames Revenue to SalesAmount before appending the online and store sales tables.

let
    OnlineStandardized = Table.RenameColumns(
        OnlineSales,
        {{"Revenue", "SalesAmount"}},
        MissingField.Ignore
    ),
    StoreStandardized = StoreSales,
    AllSales = Table.Combine(
        {
            OnlineStandardized,
            StoreStandardized
        }
    )
in
    AllSales

The MissingField.Ignore option allows the rename operation to continue if Revenue is absent. However, a reusable production workflow should validate required columns and ensure that any ignored missing field is acceptable. Renaming is appropriate only when Revenue and SalesAmount have the same meaning and compatible units.

10. Appending Queries Versus Merging Queries

Appending and merging are both methods of combining data in Power Query, but they serve different purposes.

10.1 Appending Queries

Appending stacks rows from two or more tables. It is appropriate when tables represent the same type of entity or transaction.

Example: January sales, February sales, and March sales are combined into one sales table.

10.2 Merging Queries

Merging combines tables using matching values in one or more columns. It is similar to a relational database join. A merge can add information from a related table to the records of another table.

Example: A Sales table is merged with a Customers table using CustomerID to add customer names or customer categories to sales records.

10.3 Choosing the Correct Operation

• Use Append when you want to stack similar records.
• Use Merge when you want to match records and bring related information into a table.
• Use both operations in the same workflow when necessary.

11. Handling Different Table Structures

Source tables do not always have identical columns. Power Query can still append them, but the result may contain additional columns and null values.

11.1 Missing Columns

Suppose one regional sales table includes a Discount column while another does not. The appended output contains the Discount column, and rows from the source without that field receive null values in the output column.

This is expected behavior, but analysts should decide whether the missing information is acceptable or whether the source table needs further preparation.

11.2 Different Column Names

Different names for the same concept can create separate columns. Rename equivalent columns before appending to ensure correct alignment.

11.3 Different Data Types

Data types may differ across sources. For example, one source may store OrderDate as text while another uses Date. Standardize types before or after appending, and validate conversion errors.

11.4 Different Granularity

Granularity describes what each row represents. A sales table with one row per order should not automatically be appended to a table with one row per product line if the two datasets represent different levels of detail.

Before appending, confirm that the tables have compatible row meanings. Otherwise, the consolidated table may mix incompatible records and produce misleading totals.

12. Removing Duplicates After Appending

Appending does not automatically remove duplicate rows. If the same record appears in two source tables, it may appear twice in the appended result.

12.1 Why Duplicates Occur

• The same transaction is included in multiple files.
• A source file has been copied or exported more than once.
• Two systems contain overlapping records.
• A query accidentally includes the consolidated output as one of its own inputs.
• The chosen record key does not correctly identify unique transactions.

12.2 How to Handle Duplicates

1. Identify the columns that define a unique business record.
2. Inspect repeated records and compare their values.
3. Determine whether the records are true duplicates or valid separate transactions.
4. Use Remove Duplicates or a suitable grouping and ranking process only when justified.
5. Compare row counts and important totals before and after removal.
6. Document the rule used to decide which records should be retained.

Do not remove duplicates solely because two rows look similar. Different orders can share the same customer, date, and sales amount while still being valid transactions.

13. Validating an Appended Table

Validation ensures that the combined table contains the expected records and columns. It is an essential part of a reliable Power BI workflow.

13.1 Row Count Validation

If the input tables contain 100, 120, and 150 rows, the expected combined count is 370 rows, provided that no additional filtering or row removal takes place.

In general:

Expected combined row count =
    Rows in Table 1
    + Rows in Table 2
    + Rows in Table 3
    + ...

13.2 Column Validation

• Confirm that all required columns are present.
• Check for unexpected additional columns.
• Verify that equivalent fields have consistent names.
• Check whether null values indicate missing source columns.

13.3 Data Type Validation

Confirm that dates, numeric fields, identifiers, and text columns use appropriate data types. Investigate any conversion errors before loading the result.

13.4 Total Validation

Compare totals such as SalesAmount or Quantity between the source tables and the appended output. If the totals differ, investigate filters, errors, missing records, duplicates, and type conversions.

13.5 Source Traceability

When appropriate, include a source label such as SalesChannel, Region, or SourceFile. This helps identify where each row originated and makes it easier to investigate issues.

14. Common Problems and Troubleshooting

15. Best Practices for Appending Queries

• Standardize column names: Use consistent names for fields that represent the same concept.
• Check data types: Ensure equivalent columns use compatible types.
• Confirm row granularity: Make sure each table represents compatible records.
• Use meaningful query names: Names such as AllSales or QuarterlySales make the purpose of the result clear.
• Choose the correct append option: Use Append Queries for an existing query or Append Queries as New to preserve the original queries.
• Check column differences: Unexpected columns may introduce null values or indicate source inconsistencies.
• Validate totals: Compare row counts and business totals before and after appending.
• Preserve source traceability: Add source identifiers where they help with auditing and analysis.
• Handle duplicates carefully: Do not assume that appending automatically removes repeated records.
• Review Applied Steps: Ensure that earlier transformations do not remove required records or columns.
• Design for refresh: Confirm that new files and future data can be processed consistently.
• Document business rules: Record how incompatible columns, missing values, and duplicate records are handled.

16. Practical Project: Combine Monthly Sales Tables

16.1 Project Objective

Create a consolidated sales table by appending three monthly sales tables and preparing the result for reporting in Power BI.

16.2 Sample Input Tables

JanuarySales

FebruarySales

MarchSales

16.3 Project Steps

1. Import the three tables into Power BI.
2. Open Power Query Editor.
3. Confirm that all three queries contain compatible columns.
4. Check that OrderID, Product, and SalesAmount have appropriate data types.
5. Select Append Queries as New.
6. Choose Three or more tables.
7. Add JanuarySales, FebruarySales, and MarchSales.
8. Select OK.
9. Rename the result to QuarterlySales.
10. Validate the output row count and sales total.
11. Select Close & Apply.
12. Create a report visual using the consolidated data.

16.4 Expected Results

The total sales value is calculated from the six sample records: 45,000 + 800 + 12,000 + 7,000 + 1,500 + 2,500 = 68,800.

16.5 Additional Challenge

• Add a Month column to each source table before appending.
• Append the three tables into one consolidated query.
• Create a report visual showing total sales by month.
• Compare the source row counts with the consolidated output.
• Introduce one inconsistent column name and observe how the appended schema changes.
• Correct the column name and verify that the data aligns properly.

17. Interview Questions and Answers

Q1. What is appending in Power BI?

Answer: Appending combines rows from two or more tables into one table using Power Query.

Q2. What does stacking tables mean?

Answer: Stacking tables means placing the rows of one table underneath the rows of another table, creating a vertical combination.

Q3. What is the difference between Append Queries and Append Queries as New?

Answer: Append Queries modifies the selected query to include rows from another query. Append Queries as New creates a separate query containing the combined result while preserving the original queries.

Q4. Can Power Query append more than two tables?

Answer: Yes. The Append dialog supports combining three or more tables in one operation.

Q5. Does Power Query match columns by position?

Answer: No. Power Query aligns columns by their names, not by their relative positions in the source tables.

Q6. What happens when one table is missing a column?

Answer: The appended output includes the combined column set. Rows from a table that does not contain a particular column receive null values in that column.

Q7. What is the difference between append and merge?

Answer: Append stacks rows from tables, while merge matches records using selected columns and can bring related information into a table.

Q8. Does appending remove duplicate rows?

Answer: No. Appending combines the source rows and does not automatically remove duplicates. Duplicate handling must be performed separately if required.

Q9. What is Table.Combine?

Answer: Table.Combine is a Power Query M function that combines a list of tables into a single table.

Q10. Why should column names be standardized before appending?

Answer: Standardizing names ensures that equivalent fields align into the same output column instead of becoming separate columns.

Q11. Can tables from different data sources be appended?

Answer: Yes. Power Query can append compatible tabular queries from different sources, provided that the required connections and transformations are supported.

Q12. Why should data types be checked before appending?

Answer: Compatible data types improve consistency and reduce conversion errors in the consolidated table.

Q13. How can an analyst verify that appending worked correctly?

Answer: Compare row counts, column names, data types, source totals, and representative records between the input tables and the appended output.

Q14. What is a common use case for appending queries?

Answer: Combining monthly sales files into one annual sales table is a common example.

Q15. What happens if appended tables have different column names for the same field?

Answer: Power Query treats differently named columns as separate columns. Standardize the names before appending if the fields represent the same concept.

18. Quick Revision Checklist

• Appending combines rows from multiple tables.
• Stacking is another way to describe vertical row combination.
• Append Queries modifies the selected query.
• Append Queries as New creates a separate combined query.
• Power Query supports appending two or more tables.
• Columns are matched by column names rather than positions.
• Missing columns produce null values for the relevant source rows.
• Equivalent columns should use consistent names and data types.
• Appending does not automatically remove duplicates.
• Table.Combine can append tables using Power Query M.
• Append and Merge are different operations.
• Validate row counts, totals, and the resulting schema.
• Use source labels when they improve traceability.
• Test the workflow when new source data is introduced.

19. Conclusion

Appending queries is an essential Power Query technique for consolidating records from multiple tables into a single dataset. It is particularly useful for combining monthly files, regional transactions, branch records, and other datasets that represent compatible types of information.

To append data successfully, analysts should understand the difference between modifying an existing query and creating a new appended query, standardize equivalent column names, validate data types, and investigate unexpected null values or duplicate records. Careful validation ensures that the resulting table supports reliable Power BI reporting and analysis.

With practice, learners can use both the Power Query interface and the Table.Combine function to create efficient, repeatable data consolidation workflows.

20. Additional Learning Resources

• Explore Microsoft Power BI Training at JustAcademy
• Register for a JustAcademy Course Demo
• Visit the JustAcademy Website
• Microsoft Learn: Append Queries in Power Query
• Microsoft Learn: Power Query Documentation
• Microsoft Learn: Table.Combine Function
• Microsoft Learn: Merge Queries Overview