Merge Queries
Power BI · Power Query (Data Transformation)
Theory
Merge Queries joins two tables on matching key columns, like a SQL JOIN. You pick the first table, the second table, the matching column in each (for example CustomerID), and a join kind: Left Outer (all rows from the first, matches from the second), Right Outer, Full Outer, Inner (only matches), Left Anti or Right Anti (rows with no match). The result adds a column holding a nested table, which you then expand to choose the columns you want.
For example, merging Sales with Customers on CustomerID using Left Outer and expanding Name and City gives each order the customer's name and city: order 101 gets Asha from Jaipur. Note that in a star schema you often do not need to merge dimensions into the fact table; a relationship in the model does the same job with less duplication.
Why we use it
Merge brings columns from a related table into one query, which is useful for lookups during cleaning, finding unmatched rows with anti joins, or flattening a snowflaked dimension.
When to use it
Use Merge to enrich or flatten during data prep, or to check data quality with anti joins. For normal reporting between fact and dimension tables, prefer model relationships over merging everything into one wide table.
How it works
- Two queries share a key column
- Home → Merge Queries, pick table 2
- Select the matching key in both tables
- Keep all rows from the first table? Yes: Choose Left Outer. No: Choose Inner (only matches) or another kind.
- Expand the new column and pick fields
- Enriched table ready
Syntax
Home → Merge Queries (or Merge Queries as New)
Pick table 2, select matching columns in both, Join Kind: Left Outer | Right Outer | Full Outer | Inner | Left Anti | Right Anti
Then click the expand icon on the new column
M:
Table.NestedJoin(table1, keys1, table2, keys2, newColumnName, joinKind)
Table.ExpandTableColumn(table, column, columnNames, newColumnNames)Example
#"Merged Queries" = Table.NestedJoin(Sales, {"CustomerID"}, Customers, {"CustomerID"},
"Customers", JoinKind.LeftOuter),
#"Expanded Customers" = Table.ExpandTableColumn(#"Merged Queries", "Customers",
{"Name", "City"}, {"Name", "City"})
// Find orders whose customer is missing from Customers:
Table.NestedJoin(Sales, {"CustomerID"}, Customers, {"CustomerID"}, "C", JoinKind.LeftAnti)Key points
- Merge = join side by side on key columns
- Join kind decides which unmatched rows survive
- Expand the nested table column to pick the fields you need
- Key columns must have the same data type to match
- Left Anti finds orphan rows, a quick data-quality check