In Power Query, how does Merge Queries differ from Append Queries?

## The two directions of combining Power Query is the data-preparation engine inside Power BI Desktop (also in Excel and in Fabric dataflows). A query is a list of Applied Steps written in the M language, each transforming the table the previous step produced. Two of those steps combine the current query with another query, and they combine along perpendicular axes. **Merge Queries** combines *horizontally*. It matches rows of the current query against rows of another query using one or more key columns, and the result has the original columns plus columns brought in from the other query. The table gets wider; the row count stays the same only if every key matches at most one row on the other side. **Append Queries** combines *vertically*. It puts the rows of the second query underneath the rows of the first. The table gets taller; the column list is the union of both column lists. The SQL analogy is exact enough to reason with: Merge is `JOIN`, Append is `UNION ALL`. Note the `ALL` — Append performs no de-duplication whatsoever. If the same invoice row exists in both source queries, you load it twice, and the model will happily double-count it. ## What Merge actually writes Merging generates two M steps, and understanding the pair explains most merge surprises: 1. `Table.NestedJoin(Left, {"CustomerID"}, Right, {"CustomerID"}, "Right", JoinKind.LeftOuter)` adds one new column whose every cell holds a *table* — the rows from the right query that matched that key. 2. `Table.ExpandTableColumn(...)` flattens the chosen columns out of that nested table. Because the intermediate shape is nested, expanding a one-to-many merge multiplies rows: if a customer has three matching orders, that one customer row becomes three after expansion. This is the single most common "my row count exploded" report, and it is not a bug — it is what a join does. Merge offers six join kinds. Left Outer keeps all rows of the first query and adds matches from the second; Inner keeps only matched rows; Full Outer keeps everything; Right Outer mirrors Left Outer. **Left Anti** returns only the rows of the first query with *no* match in the second — the standard tool for answering "which of my fact rows have no dimension entry?" Right Anti is the mirror. There is also a fuzzy-matching option that matches approximate text with a configurable similarity threshold, useful for dirty name data and dangerous everywhere else. Key matching compares both value and type: a text `"1001"` does not match a numeric `1001`, and text comparison is case-sensitive by default, so `"ACME"` and `"Acme"` are different keys. Trim, clean and set types on both sides before merging. ## What Append actually does Append generates `Table.Combine({Query1, Query2})` and matches columns **by name, case-sensitively** — never by position. A column that exists in only one of the queries appears in the result with nulls for the rows that came from the query lacking it. That is usually what you want when appending twelve monthly files, and it is a quiet trap when one file's header says `Amount` and another says `amount`: you get two columns, each half full. Append can take more than two queries in one step, which is cleaner than chaining pairs. When the same column carries different types in the two queries, the appended column often widens to `any`, and you should set the type explicitly afterwards. ## Folding and source boundaries When both queries read from the same foldable source — two tables in the same SQL Server database over one connection — Power Query can usually translate the merge into a SQL `JOIN` and the append into a `UNION ALL`, so the server does the work. Combining across *different* sources (a SQL table and an Excel file) cannot fold: rows are pulled into the local mashup engine, and Power Query's privacy-level rules also come into play for how those sources may be combined. ## Choosing between them, and choosing neither Same shape, different slices — this year's file plus last year's file, one query per region — is an Append. Extra attributes for rows you already have is a Merge. But in Power BI, before merging, ask whether you need the merge at all: pulling product name and category into the sales table with a Merge widens the fact table, and the model normally wants the product table loaded separately with a relationship to the fact table instead. Merge is for the cases where the lookup genuinely belongs in the row — resolving a key, or attaching a value the model cannot reach by relationship.