How to merge tables in Power BI
By the Analistable team · Updated · 5 min read
In Power BI Desktop, choose Home → Transform data to open Power Query, select the first table and click Merge Queries (or Merge Queries as New). Pick the key column in both tables, choose a join kind, then expand the columns you need and Close & Apply. If you only need to filter and summarise across the tables, a relationship in Model view is usually the better choice than merging.
Part of our guide: How to merge data in Power BI, Power Query and Tableau
Merge Queries step by step
- Home → Transform data to open the Power Query Editor.
- Select the main table (for example Sales).
- Home → Merge Queries → Merge Queries as New (keeps the originals unchanged).
- Choose the second table (Products), click the key column in both (ProductID), and choose the Join Kind.
- Click OK. A new column contains nested tables; click its expand icon and pick the columns to bring across.
- Close & Apply.
Check the match count shown at the bottom of the Merge dialog. If far fewer rows match than you expect, the keys differ in type, case or spacing. The six join kinds are explained in Power Query join kinds.
Merge or relationship?
| Merge in Power Query | Relationship in the model | |
|---|---|---|
| Result | One wider table | Separate tables connected by a key |
| Model size | Larger (repeats dimension values) | Smaller |
| Filtering across tables | N/A (one table) | Yes, via the relationship |
| Best for | Lookups you need as columns, cleaning, exports | Star schemas: facts and dimensions |
A typical report has a Sales fact table related to Products, Customers and Date dimension tables. Visuals filter Sales by any dimension through the relationships, without merging. Merge when you need the combined columns as one table — for example to clean data before loading, or because a calculation needs both columns in the same row.
Create a relationship instead
- Open Model view.
- Drag ProductID from Sales onto ProductID in Products.
- Check the cardinality (Many to one) and cross-filter direction (Single), then OK.
DAX options
RELATED(Products[Category])in a calculated column on Sales pulls a value across an existing relationship.NATURALINNERJOINandNATURALLEFTOUTERJOINcreate calculated tables that join on columns with the same name and lineage.
Merging and appending in Power BI uses the same Power Query engine as Excel, so the Excel guides apply: Append Queries, combining files from a folder.
Worked example: Sales and Products
Sales has one row per sale with a ProductID; Products has one row per product with a Category. Merging Sales (first) with Products (second) on ProductID with a Left outer join and expanding Category gives:
| SaleID | ProductID | Amount | Category |
|---|---|---|---|
| S-1 | P-10 | 120 | Furniture |
| S-2 | P-11 | 75 | Furniture |
| S-3 | P-12 | 80 | Lighting |
| S-4 | P-99 | 60 | null |
P-99 isn't in Products, so its Category is null. A Left anti join of the same two tables returns just S-4, which is a quick way to list sales that point at missing products before you publish the report. The total of Amount is still 335 after the merge, because each sale matched at most one product.
The M behind the merge
Every merge you click is recorded as M in the Advanced Editor. Knowing the two steps helps when you need to edit them:
let
Source = Table.NestedJoin(Sales, {"ProductID"}, Products, {"ProductID"},
"Products", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Source, "Products", {"Category"}, {"Category"})
in
ExpandedTo join on two columns, list both in each key list: {"StoreID", "ProductID"}. The expand step names the columns it brings across, so a column added to Products later won't appear until you edit the expand step.
Troubleshooting merges in Power BI
| Symptom | Cause | Fix |
|---|---|---|
| All expanded values are null | Key types differ (whole number vs text) or keys have spaces | Set the same type on both keys; Transform → Format → Trim |
| Row count grows after the merge | The second table repeats the key | Remove Duplicates on the key in the lookup table first |
| Refresh is slow | Large tables merged from sources that don't fold the join | Use a relationship instead, or merge in the source database |
| Visual totals are doubled | A merged-in table repeated dimension rows, then a relationship was added too | Keep either the merge or the relationship, not both |
Which approach to choose
- Dimension attribute you will slice by (Category, Region): load the dimension as its own table and relate it. The model stays smaller and filters work in every visual.
- Lookup needed for cleaning or row-level calculations before load (exchange rate per transaction date, mapping old codes to new): merge in Power Query.
- Two fact tables at different grains (daily sales, monthly targets): don't merge them; relate both to shared Date and Product dimensions and compare in measures.
- A single table to export or hand to someone else: merge, because a relationship only exists inside the model.
After any merge, compare the row count of the merged query (shown in the status bar of the Power Query Editor) with the original table. If you need the result outside Power BI, the same merge works in Excel, and the broader options are compared in merging data in Power BI and Tableau.
Frequently asked questions
- How do I merge two tables in Power BI?
- Open Transform data, select a table, choose Merge Queries, pick the key columns and join kind, and expand the columns you need.
- What is the difference between Merge Queries and Append Queries in Power BI?
- Merge adds columns from another table by matching keys. Append adds rows from tables with the same columns.
- Should I merge tables or use relationships in Power BI?
- Use relationships for fact and dimension tables in a star schema. Merge when you need the combined columns in one table.
- Why are my merged columns all null in Power BI?
- The key columns don't match exactly: usually one is text and the other a number, or one has trailing spaces. Set the same data type on both and trim them before merging.
- Can I merge tables from different sources in Power BI?
- Yes. Power Query can merge a table from Excel with one from a database or SharePoint list, as long as both are loaded as queries. The join then runs in Power Query rather than in the source.