Merge queries adds columns from one table to another by matching rows on a shared column. It is the Power Query version of a lookup. If your orders have a Product ID and another table holds the product names, a merge brings the names in.
Merge is often confused with append. Merge adds columns. Append adds rows. If you are stacking January under December, you want append queries instead.
How to merge queries in Power BI
- In Power BI Desktop, select Transform data to open Power Query.
- Select the main query, the one you want to keep and add columns to.
- On the Home tab, in the Combine group, select Merge queries. Choose Merge queries as new if you want to leave the original untouched.
- In the dialog, choose the second table from the dropdown.
- Click the matching column in each table. They highlight in green.
- Pick a Join kind, then read the message at the bottom. It tells you how many rows matched.
- Select OK. A new column appears, with a table in each row.
- Click the expand icon in that column’s header, tick the columns you need, and untick Use original column name as prefix.
The expand step is the one people miss. Until you do it, the merge has only added a column of nested tables.
Which join kind to use
Power Query has six join kinds. Think of the first table you selected as the left table.
| Join kind | What you get | Use it when |
|---|---|---|
| Left outer | All rows from the left, plus matches from the right | The default. Adding details to a main table |
| Right outer | All rows from the right, plus matches from the left | Rarely. Swapping the tables is clearer |
| Full outer | All rows from both tables | Comparing two lists and keeping everything |
| Inner | Only rows that match in both | Keeping only what exists on both sides |
| Left anti | Left rows with no match | Finding orders with an unknown product |
| Right anti | Right rows with no match | Finding products that were never sold |
The two anti joins are the most underrated. They are the quickest data quality check you can run in Power Query.
Merging on more than one column
Sometimes one column isn’t enough, such as when a price depends on both Product and Country. Hold Ctrl and click the columns in the first table, then click the matching columns in the second table in the same order. Small numbers appear on the column headers to show how they pair up.
Why your merge finds no matches
If the message says few rows matched, check these in order:
- Data types differ. A number won’t match text that looks like a number. Set both columns to the same type before the merge.
- Hidden spaces.
"Canada "is not"Canada". Use Transform > Format > Trim on both columns. - Different capitals. Merges match exact case by default, so
CANADAandCanadadon’t match. Make the case the same, or use fuzzy matching. - Near matches. For values that are almost the same, tick Use fuzzy matching in the merge dialog. Review the results, because fuzzy matching can pair the wrong rows.
Duplicates can add rows
If the matching value appears more than once in the second table, each match creates a row when you expand. Your row count goes up and your totals go wrong. Before merging for a lookup, check that the key is unique in the lookup table. Remove duplicates on that column fixes it, once you know which row to keep.
Merge or relationship?
You often don’t need a merge at all. In a Power BI model, a relationship between a fact table and a dimension table lets you use both together without copying columns, and it keeps the model smaller. This is the idea behind a star schema.
Use a merge when you need the combined data during the query, for example to calculate something before loading, or to find non-matching rows with an anti join.
The M code behind it
Power Query writes the step for you. It looks like this:
= Table.NestedJoin(Orders, {"Product ID"}, Products, {"Product ID"}, "Products", JoinKind.LeftOuter)
The expand step is a separate Table.ExpandTableColumn line. Knowing this helps when you want to change the join kind later: edit JoinKind.LeftOuter in the formula bar.
Sources
- Merge queries overview, Microsoft Learn
- Fuzzy merge, Microsoft Learn
- Table.NestedJoin, Microsoft Learn