Appending Queries — Append queries (union rows) and validate schema alignment (intro)

Historical Context & Motivation

The need to combine rows from multiple tables into a single, coherent dataset is one of the oldest problems in data management. In relational algebra, this operation is formalized as the union operator, which Edgar F. Codd described in his foundational 1970 paper on the relational model of data. The union requires both relations to be union-compatible—possessing the same number of attributes with matching domains—a constraint that Power Query inherits and extends in its Append Queries feature. As enterprise analytics matured through the 2000s, organizations increasingly stored logically related data across separate files, databases, and cloud endpoints, making vertical concatenation a daily ETL necessity.

Despite decades of tooling evolution, the fundamental question remains the same: how do you stack rows from two or more tables while ensuring that the resulting schema is correct and no data is silently lost or misaligned? Power Query's Append Queries feature answers this question with a visual workflow, but understanding the underlying mechanics—especially schema alignment—is essential to avoiding subtle bugs in your data models.

Core Principles & Definitions

Before diving into the Power Query interface, it is important to establish the conceptual vocabulary that governs append operations. The append operation is a vertical concatenation: it takes all rows from a second table and places them beneath the rows of the first table, producing a new table whose row count equals the sum of the source row counts (analogous to SQL's UNION ALL). Unlike a merge (a horizontal join on key columns), an append adds no new columns from the secondary table that do not already conceptually belong to the schema. Power Query performs this through the M function Table.Combine, which takes a list of tables and produces a single output table.

Visual Explanation — The Append Operation

In the diagram above, both source tables share an identical schema—three columns named ID, Product, and Revenue—so the append produces a clean result with no nulls. This is the ideal case. In practice, you will frequently encounter tables whose schemas diverge: a column may be named revenue in one table and Revenue in another (recall that M is case-sensitive), or one table may include an extra Region column that the other lacks. The next section examines what happens in those scenarios.

How Append Works Under the Hood

When you invoke Append Queries in the Power Query Editor, the GUI generates M code that calls Table.Combine. Understanding the semantics of this function illuminates why schema alignment matters. Table.Combine accepts a list of tables and an optional set of columns (a table type) that defines the output schema. If no explicit schema is provided, Power Query infers the output schema by computing the union of all column names across all input tables. Any column that exists in one table but not another will be filled with null for the rows originating from the table that lacks it.

The M Code Behind an Append

Consider two queries, Sales_Q1 and Sales_Q2. After appending them via the GUI, the generated step in the formula bar reads: = Table.Combine({Sales_Q1, Sales_Q2}). For an n-ary append of three or more tables, the list simply grows: = Table.Combine({Sales_Q1, Sales_Q2, Sales_Q3}). Because Power Query evaluates lazily, no data is physically copied until the query is loaded or refreshed.

Type Coercion Behavior

When two tables share a column name but differ in data type—say, Revenue typed as Int64.Type in Table A and Currency.Type in Table B—Power Query will attempt to promote the column to a common supertype. If no safe promotion exists, the column may revert to Any.Type, which defers type errors until downstream transformations or model load. Detecting and resolving these mismatches proactively is a core goal of schema validation.

Schema Alignment — Classification of Mismatches

Schema misalignment is the most common source of bugs in append workflows. Understanding the categories of mismatches allows you to detect and remediate them systematically. The following diagram illustrates three classes of schema mismatch and their consequences in an appended result.

Worked Example — Appending Monthly Sales Tables

Suppose you receive three CSV files—Sales_Jan.csv, Sales_Feb.csv, and Sales_Mar.csv—each containing daily sales records for a retail chain. The January and February files share columns Date, Store, Product, and Revenue. However, the March file introduces a fifth column Discount and has revenue (lowercase) instead of Revenue. Our goal is to produce a single, clean, appended query with no null columns.

Append vs. Merge — Strengths & Limitations

A common source of confusion in Power Query is distinguishing the Append operation from the Merge operation. In database terminology, append corresponds to a UNION (vertical concatenation of rows), while merge corresponds to a JOIN (horizontal combination of columns based on a key). Choosing the wrong operation is a surprisingly frequent error—analogous to confusing array concatenation with a hash-map lookup in code. The table below contrasts their behaviors across several dimensions.

Connection to Advanced Theory — Dynamic Append & Folder Sources

The manual append workflow shown in this lesson is appropriate when the number of source tables is small and stable. In production environments, however, data engineers frequently need to append an unknown or growing number of files—such as daily CSV extracts dropped into a shared folder. Power Query addresses this via the Folder connector, which treats the entire contents of a directory as a single source and dynamically appends all files at refresh time. This is implemented internally using Folder.Files followed by a custom function that parses each file and a Table.Combine over the resulting list of tables.

The folder-based approach essentially wraps everything covered in this lesson—column name normalization, type casting, and Table.Combine—inside a parameterized M function. Mastering the manual append covered here is a prerequisite: you need to understand what can go wrong with two tables before you can write robust code that handles hundreds.

Practice Problems

Lesson Summary