How to Join Tables in Power BI

Joining tables is one of the most fundamental tasks in Power BI, turning separate sets of raw data into a cohesive and insightful report. If you have sales data in one table and product details in another, you need to combine them to discover which product categories are your bestsellers. This guide will walk you through the various ways to join tables in Power BI, focusing on the powerful Merge Queries feature in the Power Query Editor and the relationship-based approach in the Model View.

Why Joining Tables is Essential in Power BI

Imagine your data is spread across different files. You have an export from your sales system with transaction IDs, product IDs, and quantities sold. In another spreadsheet, you have a product directory with product IDs, product names, categories, and prices. On their own, each table tells only part of the story.

The sales sheet tells you what sold, but not in a very descriptive way ("ProductID 15 sold 10 units"). The product sheet tells you about your products, but nothing about their sales performance. By joining them on the common ProductID column, you can answer critical business questions like:

• What is the total revenue per product category?
• Which specific products are the most popular?
• How have sales for a particular product trended over time?

In Power BI, this is accomplished primarily by "merging" queries or by creating "relationships." Merging physically combines the tables into a new, single table, while relationships create a virtual link that lets your tables work together without being physically fused. We'll cover both.

Your Data Workspace: Getting Started with Power Query Editor

Almost all data transformation, including merging tables, happens in the Power Query Editor. Think of it as your workshop, a place separate from your final polished report where you can shape, clean, and combine your data without affecting the original files.

To open the Power Query Editor:

1. On the Home tab in Power BI Desktop, find the Queries section.
2. Click on the Transform data button.

A new window will open. On the left side, you'll see a list of all the data sources (queries) you've loaded into your report. This is where the magic begins.

The Main Method: Using Merge Queries to Join Tables

Let's walk through a common scenario. We have two tables:

• Sales: Contains OrderID, ProductID, UnitsSold, and SaleDate.
• Products: Contains ProductID, ProductName, and ProductCategory.

Our goal is to add the product name and category to our sales data so we can analyze sales by category. Here's how to do it step-by-step.

Step 1: Go to the Merge Queries Menu Item

In the Power Query Editor, select your main or "left" table. In our case, this is the Sales table, as it's the primary table we want to add information to.

Next, navigate to the Home tab within the Power Query Editor ribbon. In the Combine section, click on Merge Queries. You'll see two options:

• Merge Queries: This will merge the other table directly into the one you have selected. Your Sales table will get new columns.
• Merge Queries as New: This creates an entirely new, third table that is the result of the merge, leaving your original two tables untouched. This is useful if you want to preserve the originals for other purposes.

For this example, let's select Merge Queries to add the data directly to our Sales table.

Step 2: Select Tables and Matching Columns

A "Merge" dialog box will appear. Your Sales table is already selected at the top. In the dropdown menu below it, select the second table you want to join—in this case, the Products table.

Now, you need to tell Power Query how these tables relate to each other by selecting the common column, often called the "key."

• In the Sales table at the top, click on the header for the ProductID column.
• In the Products table at the bottom, click on the header for the ProductID column.

Once you select both, Power Query will show you a small note at the bottom of the dialog box, confirming how many rows from the first table have a match in the second. This is a quick check to see if your join is working as expected.

Understanding the Different 'Join Kinds' in Power BI

This is the most critical step. The "Join Kind" determines how the tables are combined and which rows are kept or discarded. Power BI offers six types of joins, and choosing the right one is essential for accurate analysis.

Left Outer (the default)

What it does: Keeps all rows from the first (top) table and only the matching rows from the second (bottom) table. When to use it: Use when you want to enrich a primary table with data from a lookup table. For instance, to keep every sale and add product info, even if some ProductID don't exist in the product list. Missing product details will show as null.

Right Outer

What it does: Keeps all rows from the second (bottom) table and brings in matching rows from the first (top) table. When to use it: When the product list is the primary focus, and you want to see all products along with their sales data if available. Products with no sales will have nulls in sales columns.

Full Outer

What it does: Keeps all rows from both tables. Matches data where possible, unmatched rows are included with nulls filling in missing columns. When to use it: To see a comprehensive picture, including mismatches, such as sales without valid product IDs and products never sold.

Inner

What it does: Only keeps rows where the ProductID exists in both tables. When to use it: When you only need sales for products that are in your product list, discarding any unmatched records.

Left Anti

What it does: Shows all rows from the first table that do not have a match in the second. When to use it: For data validation, to find sales transactions with invalid ProductIDs (not in product list).

Right Anti

What it does: Shows all rows from the second table that do not have a match in the first. When to use it: To identify products that have never been sold.

For our demonstration, we'll stick with the default Left Outer join and click OK.

Finalizing Your Join: Expanding the Data

After you click OK, you'll see your table back in Power Query with a new column at the end, typically named after the merged table (e.g., "Products"). Instead of actual data, each cell in this column says Table—that's normal! Power BI has bundled the related data from the Products table. You need to "expand" this column to access the specific fields.

1. Find the new Products column. In its header, click the expand icon (two arrows pointing in opposite directions).
2. A dropdown will show all columns from the Products table (ProductID, ProductName, ProductCategory).
3. Select the columns you want to add—typically, ProductName and ProductCategory. You may deselect ProductID if you already have it.
4. Tip: Uncheck "Use original column name as prefix" for cleaner column names.
5. Click OK.

Voila! Your Sales table now includes ProductName and ProductCategory for each transaction. Click Close & Apply to load your data into Power BI and start creating visuals.

An Alternative Approach: Creating Relationships in the Model View

Merging in Power Query is useful, but for large datasets or dynamic relationships, creating relationships is often better. It keeps your data model streamlined and scalable.

A relationship doesn't merge tables but tells Power BI how they're linked, enabling you to use columns from related tables in your visuals seamlessly.

When to Use Relationships vs. Merges

• Use a Merge when: You need a flattened, denormalized table for specific tasks or static lookups.
• Use a Relationship when: Building a scalable, efficient data model for reporting with a star schema, keeping data normalized.

How to Create a Relationship

1. After loading tables without merging, click the Model view icon (three connected boxes).
2. Locate your tables and find the common column (ProductID) in both.
3. Drag ProductID from Sales and drop it onto ProductID in Products.
4. Power BI will automatically create a line between the tables. Now, you can use fields from both tables in your report, and the relationship will handle the joins behind the scenes.

Common Mistakes and Best Practices When Joining Tables

As you work, watch out for:

• Mismatched Data Types: Ensure keys are the same type in both tables. Convert as needed.
• "Dirty" Data: Remove extra spaces and standardize casing via Power Query transformations.
• Overuse of Merges: For large datasets, rely on relationships to keep models efficient. Use merges for pre-processing.

Final Thoughts

Connecting tables correctly is key to unlocking Power BI's full potential. Choose merging or relationships based on your needs: for one-time transformations, merging works well, for scalable, maintainable models, relationships are preferred. Mastering both methods enhances your ability to create powerful, interactive dashboards and reports.

Connecting data and preparing it for analysis can be tedious, but tools like Power Query and relationships streamline the process. And if you want to speed things up even more, <a href="https://www.graphed.com/register" target="_blank" rel="noopener">Graphed</a> helps generate live, interactive dashboards automatically, handling all joins and data modeling behind the scenes.