How to Do Inner Join in Power BI

Bringing two different datasets together is a fundamental step in any data analysis, and an inner join is one of the most common ways to do it. It’s a powerful technique for combining tables based on a shared value, giving you a unified view of your information. This article will walk you through exactly how to perform an inner join in Power BI using both the user-friendly Power Query Editor and the more advanced DAX formulas.

What Exactly Is an Inner Join?

Think of an inner join like matching up two guest lists for an event. One list has the names of everyone who RSVP'd (Table A), and the other has the names of everyone who actually showed up (Table B). An inner join combines these two lists to show you only the people who are on both - the ones who both RSVP'd and attended.

In data terms, an inner join takes two tables and merges them based on a common column, like a CustomerID or ProductID. The final, combined table includes only the rows where the values in that common column exist in both original tables. If a customer exists in your customer list but has never made a purchase, they won't appear in the final joined table.

An Example in Action

Let's say you have a Customers table and an Orders table.

Table 1: Customers

• CustomerID: 1, Name: Alice
• CustomerID: 2, Name: Bob
• CustomerID: 3, Name: Charlie

Table 2: Orders

• OrderID: 101, CustomerID: 2, Product: Coffee Maker
• OrderID: 102, CustomerID: 1, Product: Toaster
• OrderID: 103, CustomerID: 1, Product: Blender

Notice that CustomerID 3 (Charlie) isn't in the Orders table because he hasn't bought anything yet. An inner join on CustomerID would produce the following table:

Result of Inner Join

• CustomerID: 1, Name: Alice, OrderID: 102, Product: Toaster
• CustomerID: 1, Name: Alice, OrderID: 103, Product: Blender
• CustomerID: 2, Name: Bob, OrderID: 101, Product: Coffee Maker

As you can see, Charlie is excluded from the result because his CustomerID didn’t have a match in the Orders table. This is perfect for when you only want to analyze data that has a "complete" relationship across tables.

Inner Join vs. Other Join Types

To really understand an inner join, it helps to see how it differs from other common join types available in Power BI.

• Inner Join: Keeps only matching rows from both tables.
• Left Outer Join: Keeps all rows from the left (first) table and only matching rows from the right (second) table.
• Right Outer Join: Keeps all rows from the right (second) table and only matching rows from the left (first) table.
• Full Outer Join: Keeps all rows from both tables, matching them where possible.

How to Perform an Inner Join with Power Query

The Power Query Editor is the most common and intuitive way to merge tables in Power BI. It provides a graphical interface where you can see your data and click through the process without writing any code. It’s the go-to method for most data preparation tasks.

Let's walk through it step-by-step, using our Customers and Orders tables as an example.

Step 1: Open the Power Query Editor

First, you need to open your report in Power BI Desktop. Once your data is loaded, navigate to the Home tab in the ribbon at the top. Click on the Transform Data button. This will launch the Power Query Editor in a new window, which is where all data transformation activities take place.

Step 2: Start the Merge Queries Process

With the Power Query Editor open, select the primary table you want to merge into from the Queries pane on the left. In our case, let's select the Orders table.

Next, in the Home tab of the Power Query Editor, click the Merge Queries dropdown. You will see two options:

• Merge Queries: Adds the data from the second table into your currently selected table. It modifies the original query.
• Merge Queries as New: Creates a completely new, separate query for the merged table, leaving your original tables untouched. This is often the safer option, especially when you are new to the process.

Let's choose Merge Queries as New for this example.

Step 3: Configure the Merge Dialog Box

A "Merge" dialog box will appear. This is where you'll define the join.

1. The top dropdown should already show your first table (Orders). In the second dropdown right below it, select your second table (Customers).
2. Next, you need to tell Power BI which column to match on. Click on the CustomerID column header in the Orders table view, and then click on the CustomerID column header in the Customers table view. The columns will highlight in a light green to show they've been selected as the key.
3. Now for the most important part: select the Join Kind. In the dropdown menu, choose Inner (only matching rows). Power BI also gives you a helpful summary at the bottom, like "The selection matches 3 of 3 rows from the first table."

Click OK when you're ready.

Step 4: Expand the New Column

You will now be taken to your new, merged query. You'll see all the columns from your first table, plus a new column, likely named after the second table (e.g., Customers). Every row in this new column simply says "Table."

This "Table" column contains all the matching information from your second table. To see it, click the expand icon (a two-sided arrow) in the column header.

An options menu will pop up, listing all the columns available in the second table (Customers). Here you can select which columns you want to add to your merged table. Let's uncheck CustomerID to avoid having a duplicate column and keep Name checked. Also, uncheck "Use original column name as prefix" to keep your column names clean (e.g., Name instead of Customers.Name).

Click OK.

That's it! Your tables are now successfully merged using an inner join. The resulting table contains columns from both original tables, limited only to the rows where a match was found for CustomerID.

How to Perform an Inner Join with DAX

While Power Query is great for permanent data preparation, you might occasionally need to create a temporary, calculated table for a specific analysis or measure inside your data model. This is where DAX (Data Analysis Expressions) comes in.

The primary DAX function for this is NATURALINNERJOIN.

Warning: The NATURALINNERJOIN function is powerful but has a critical quirk: it automatically joins on all columns that have the same name in both tables. If you have multiple columns with identical names (e.g., Date and ProductID), it will try to match on both, which might not be what you want. It's often best practice to ensure only your intended key columns share the same name.

Steps to use NATURALINNERJOIN

1. In Power BI Desktop, navigate to the Data View (the grid icon on the left).
2. In the ribbon, select the Modeling tab and click on New table.
3. A formula bar will appear at the top. This is where you'll write your DAX expression.
4. Enter the following formula:

Merged Customers-Orders = NATURALINNERJOIN(Customers, Orders)

Press Enter. Power BI will create a new virtual table containing only the rows from Customers and Orders where the CustomerID values matched. This new table lives within your data model and will recalculate based on filters and interactions in your report.

Common Challenges and Best Practices

Performing joins is usually straightforward, but a few common issues can trip you up. Here's what to look out for.

• Data Type Mismatches: This is the most common problem. If the CustomerID in one table is a text type ("ABC-123") and in the other is a number type (123), Power BI can't join them. Solution: In Power Query, make sure your join columns are the exact same data type before merging.
• Leading or Trailing Spaces: Hidden spaces can prevent matches. "Customer1 " and "Customer1" look the same but are treated as different values. Solution: In Power Query, right-click the key column header, go to Transform, and select Trim. This removes any accidental spaces from the beginning or end of each cell value.
• Varying Case: Power Query is case-sensitive by default. This means "ID-1" and "id-1" would not match. Solution: Use the Transform > Format > Uppercase (or Lowercase) option on your key columns in both tables to standardize them before merging.
• Performance on Large Datasets: Merging tables with millions of rows can bog down your report. Solution: Filter your data as early as possible in the Power Query steps. Remove any unnecessary columns and rows from both tables before you perform the merge. Less data to process means a faster refresh.

Final Thoughts

Mastering the inner join is a crucial skill for anyone working with Power BI. It allows you to reliably combine related datasets, creating a single, coherent source of truth for your reports whether you use the visual tools in Power Query or the programmatic functions in DAX.

With that said, the process of setting up data sources, cleaning data, and choosing join types is often the most time-consuming part of analytics. At Graphed, we built a tool that handles all that complexity for you. Simply connect your platforms like Google Analytics, Salesforce, or Shopify, and you can instantly create dashboards by asking questions in plain English, like "Compare revenue by campaign from Facebook Ads and Google Ads." We manage the data connections and joins automatically behind the scenes, turning hours of report building into a 30-second task.