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

  1. Two queries share a key column
  2. Home → Merge Queries, pick table 2
  3. Select the matching key in both tables
  4. Keep all rows from the first table? Yes: Choose Left Outer. No: Choose Inner (only matches) or another kind.
  5. Expand the new column and pick fields
  6. 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

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic