How to Merge Queries in Power BI: Step-by-Step Guide

In Power BI, you merge queries by opening the Power Query Editor and using the Merge Queries option to combine two tables based on matching columns. This creates a new query that joins data from both tables, similar to a database join.

Syntax

The Merge Queries feature in Power BI does not use code but a graphical interface in Power Query Editor. The general steps are:

• Home > Merge Queries or Merge Queries as New
• Select the first table
• Select the second table to merge
• Choose matching columns from both tables
• Select the join kind (e.g., Left Outer, Inner, Right Outer)
• Click OK to create the merged query

This process creates a new column with nested tables that you can expand to include columns from the second table.

powerquery

let
    Source1 = Table1,
    Source2 = Table2,
    MergedTables = Table.NestedJoin(Source1, {"KeyColumn"}, Source2, {"KeyColumn"}, "NewTable", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTables, "NewTable", {"ColumnA", "ColumnB"}, {"ColumnA", "ColumnB"})
in
    ExpandedTable

Example

This example merges two tables: Customers and Orders, joining on CustomerID. It uses a Left Outer join to keep all customers and add their orders.

powerquery

let
    Customers = Table.FromRecords({
        [CustomerID=1, Name="Alice"],
        [CustomerID=2, Name="Bob"],
        [CustomerID=3, Name="Charlie"]
    }),
    Orders = Table.FromRecords({
        [OrderID=101, CustomerID=1, Product="Book"],
        [OrderID=102, CustomerID=2, Product="Pen"],
        [OrderID=103, CustomerID=1, Product="Notebook"]
    }),
    Merged = Table.NestedJoin(Customers, {"CustomerID"}, Orders, {"CustomerID"}, "OrdersTable", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "OrdersTable", {"OrderID", "Product"}, {"OrderID", "Product"})
in
    Expanded

Output

CustomerID | Name    | OrderID | Product
-----------|---------|---------|---------
1          | Alice   | 101     | Book
1          | Alice   | 103     | Notebook
2          | Bob     | 102     | Pen
3          | Charlie | null    | null

Common Pitfalls

• Not selecting matching columns correctly: The columns used to join must have matching data types and values.
• Choosing wrong join kind: For example, using Inner join will exclude unmatched rows.
• Forgetting to expand merged tables: After merging, you must expand the new column to see the joined data.
• Duplicate column names: Expanding without renaming can cause duplicate column names.

powerquery

/* Wrong: Using Inner join excludes unmatched rows */
let
    Customers = Table.FromRecords({
        [CustomerID=1, Name="Alice"],
        [CustomerID=2, Name="Bob"],
        [CustomerID=3, Name="Charlie"]
    }),
    Orders = Table.FromRecords({
        [OrderID=101, CustomerID=1, Product="Book"],
        [OrderID=102, CustomerID=2, Product="Pen"],
        [OrderID=103, CustomerID=1, Product="Notebook"]
    }),
    MergedWrong = Table.NestedJoin(Customers, {"CustomerID"}, Orders, {"CustomerID"}, "OrdersTable", JoinKind.Inner),
    ExpandedWrong = Table.ExpandTableColumn(MergedWrong, "OrdersTable", {"OrderID", "Product"}, {"OrderID", "Product"})
in
    ExpandedWrong

/* Right: Using Left Outer join keeps all customers */
let
    Customers = Table.FromRecords({
        [CustomerID=1, Name="Alice"],
        [CustomerID=2, Name="Bob"],
        [CustomerID=3, Name="Charlie"]
    }),
    Orders = Table.FromRecords({
        [OrderID=101, CustomerID=1, Product="Book"],
        [OrderID=102, CustomerID=2, Product="Pen"],
        [OrderID=103, CustomerID=1, Product="Notebook"]
    }),
    MergedRight = Table.NestedJoin(Customers, {"CustomerID"}, Orders, {"CustomerID"}, "OrdersTable", JoinKind.LeftOuter),
    ExpandedRight = Table.ExpandTableColumn(MergedRight, "OrdersTable", {"OrderID", "Product"}, {"OrderID", "Product"})
in
    ExpandedRight

Quick Reference

Here is a quick summary of common join kinds used in Power BI Merge Queries:

Join Kind	Description
Left Outer	All rows from first table, matching rows from second
Right Outer	All rows from second table, matching rows from first
Full Outer	All rows from both tables, matched where possible
Inner	Only rows with matching keys in both tables
Anti Join (Left)	Rows in first table with no match in second
Anti Join (Right)	Rows in second table with no match in first

Key Takeaways

Use Power Query Editor's Merge Queries to combine tables by matching columns.

Choose the correct join kind to control which rows appear in the merged result.

Always expand the merged column to include data from the second table.

Ensure matching columns have compatible data types to avoid errors.

Be careful with duplicate column names after expanding merged tables.