Merging Queries — Merge queries (join tables) and choose join types (intro-to-standard)

The need to combine data from multiple tables is as old as the relational model itself. When Edgar F. Codd published his seminal paper in 1970, he formalized the concept of relational algebra, which included join operations as first-class primitives for combining tuples across relations based on shared attributes. This mathematical foundation underpins every modern database system and, by extension, every data transformation tool that operates on tabular data. Power Query, the data preparation engine embedded in Microsoft Power BI, Excel, and Azure Data Factory, inherits this lineage directly — its Merge Queries feature is essentially a GUI-driven implementation of the classic relational join.

Before self-service BI tools existed, analysts relied on database administrators to write SQL JOIN statements or used complex VLOOKUP chains in spreadsheets — both approaches were error-prone and inaccessible to non-technical users. Microsoft introduced Power Query (initially as a free Excel add-in called "Data Explorer") precisely to democratize the extract-transform-load (ETL) pipeline. The merge operation became one of its most powerful features, enabling users to perform inner, outer, cross, and anti joins without writing a single line of SQL or M code.

The central question that merge queries address is deceptively simple: how do you combine rows from two tables that share a logical relationship? Whether you are linking a sales fact table to a product dimension, associating customer IDs across CRM and billing systems, or reconciling inventory records from two warehouses, the merge operation provides a declarative, reproducible way to perform that combination. Understanding which join type to select — and why — is what separates a correct, complete result from one riddled with missing rows or unintended duplicates.

Before executing a merge in Power Query, you must internalize a handful of foundational concepts that map directly to relational database theory. A merge in Power Query is the equivalent of a SQL JOIN: it produces a new table whose rows are determined by matching values in one or more key columns shared between a primary query (left table) and a secondary query (right table). The result initially appears as a nested table column, which you then expand to surface the desired fields. This two-phase workflow — match, then expand — is a distinctive Power Query design pattern that gives you fine-grained control over which columns enter your data model.

The following Venn-style diagram illustrates the six join types available in Power Query's Merge dialog. Each join type is defined by which region of the Venn diagram it retains: the intersection (matched rows), the left-only region (rows in the primary query with no match), the right-only region (rows in the secondary query with no match), or some combination thereof. Understanding this visual vocabulary is essential because Power Query's merge dialog presents these options in a drop-down, and selecting the wrong one is the single most common source of incorrect results.

In the diagram above, the left circle represents rows from the primary (left) query and the right circle represents the secondary (right) query. The overlapping region contains rows where the key column values match across both tables. When you open Power Query's Merge dialog, you select one option from a drop-down that directly corresponds to these six regions. The default selection is Left Outer (all from first, matching from second), which is the safest general-purpose choice because it guarantees you never lose rows from your primary table while still enriching them with data from the secondary table wherever a match exists.

While Power Query provides a graphical interface for merging, every merge operation generates an expression in M language (also known as Power Query Formula Language). Understanding the generated code deepens your ability to debug merges, optimize performance, and handle edge cases. The core function is Table.NestedJoin, which takes the left table, left key column(s), right table, right key column(s), a name for the new nested column, and a JoinKind enumeration value.

After the merge step, the result table contains all columns from the left table plus a single new column of type Table. Each cell in this column is itself a table containing the matched row(s) from the right table. To bring those columns into the flat result, you use Table.ExpandTableColumn, specifying which columns to surface. This two-step process — join then expand — is deliberate: it prevents accidental inclusion of every column from the right table, which could bloat your data model.

Cardinality and Row Count Impact

A critical consideration when merging is the relationship cardinality between the key columns. If the key in the right table is unique (1:1 or 1:N from left to right), the output row count will not exceed the left table's row count. However, if the right table's key contains duplicates relative to the left (creating an M:N scenario), the output will contain a Cartesian product of matched rows — potentially multiplying the row count dramatically. In formal terms, if a left-table key value k appears m times and the same key value appears n times in the right table, the merge produces m × n rows for that key value. Failing to account for this is a frequent source of inflated aggregates in downstream reports.

Each of the six join types serves a distinct analytical purpose. Selecting the wrong type is a semantic error — Power Query will not throw an exception, but your results will be silently incorrect. The table below provides a comprehensive reference, mapping each Power Query join kind to its SQL equivalent, its set-theoretic definition, and a canonical use case.

Suppose you have loaded two queries into Power Query Editor: a Sales table containing transaction records and a Products table containing product metadata. You want to enrich each sales record with the product's category and unit price from the Products table, while retaining all sales rows even if a product lookup fails. This calls for a Left Outer join on the shared ProductID column.

The standard merge operation in Power Query performs exact-match joins, but real-world data is messy. Power Query offers Fuzzy Merge, which uses a similarity algorithm (based on Jaccard distance over character n-grams) to match keys that are close but not identical — for example, joining "Microsoft Corp" to "Microsoft Corporation." You configure a similarity threshold between 0 and 1, where 1 requires an exact match. This feature bridges the gap between Power Query's GUI-driven ETL and the kind of probabilistic record linkage typically handled by specialized tools or custom Python scripts.

Another advanced consideration is query folding. When both tables originate from the same SQL-based source, Power Query can translate the merge step into a native SQL JOIN statement and push the computation to the database engine. This dramatically improves performance because the data never leaves the server until after the join. You can verify whether folding occurred by right-clicking the merge step in the Applied Steps pane and selecting View Native Query. If the option is grayed out, folding has broken — often because a prior step (like a custom M function or a manual table construction) is not foldable. Designing your Power Query pipeline with folding in mind is a key optimization technique for enterprise-scale datasets.