How to Connect Two Sheets in Power BI

Bringing data together from two separate Excel sheets into a single Power BI report can feel tricky, but it's the key to unlocking deeper insights. Combining related information, like sales transactions from one sheet and product details from another, allows you to see the bigger picture. This tutorial will walk you through exactly how to connect two sheets in Power BI, step by step.

Why Combine Sheets in the First Place?

Most business data isn't stored neatly in one giant file. It’s often spread across multiple tabs or even different files. For example, you might have:

• An 'Orders' sheet with Transaction ID, Customer ID, Order Date, and Revenue.
• A 'Customers' sheet with Customer ID, Customer Name, and location (City, State).

Independently, they're useful. The 'Orders' sheet tells you what was sold and for how much. The 'Customers' sheet tells you who your customers are. But what if you want to answer the question, "Which cities are driving the most revenue?" To do that, you need to connect them. By linking these two sheets on a common piece of information (the 'Customer ID'), you can create reports that pull columns from both tables simultaneously.

Step 1: Get Your Excel Data into Power BI

Before you can connect anything, you need to bring your data into Power BI Desktop. The best way to prepare your Excel file is by formatting your data as tables.

Prepare Your Excel File

Using Excel Tables makes your life infinitely easier. They have clear headers, they're structured, and Power BI can reliably read them and automatically detect column names.

1. Open your Excel workbook.
2. Go to your first sheet (e.g., 'Orders'). Click anywhere inside your data range.
3. On the ribbon, go to Insert > Table. Excel will automatically highlight your data range. Make sure the "My table has headers" box is checked. Click OK.
4. With the new table selected, go to the Table Design tab that appears on the ribbon. In the top-left corner, give your table a clear, meaningful name like OrdersData instead of the default "Table1."
5. Repeat this process for your second sheet (e.g., 'Customers'), naming it something like CustomerData.

This simple preparation step makes your data clean, organized, and ready for Power BI.

Import into Power BI Desktop

Now, let's pull those newly created tables into your Power BI report.

1. Open a blank Power BI Desktop file.
2. On the Home ribbon, click on Get data and select Excel workbook.
3. Navigate to your saved Excel file and click Open.
4. Power BI will open a Navigator window. Here, you'll see a list of all the sheets and any Tables within the workbook. Your newly named tables (OrdersData and CustomerData) will appear with a distinct blue header icon. This is why using tables is so much better than just selecting raw sheets!
5. Check the boxes next to both your tables.
6. Click the Load button. Power BI will import the data from both tables into your data model.

You can see your imported data in the Data view (the table icon on the left-hand panel).

Step 2: Create a Relationship Between the Tables

This is where the magic happens. You’ve brought two separate tables into Power BI. Now you need to tell Power BI how they’re related so they can talk to each other.

Find the Common Column (The "Key")

For a relationship to work, both tables need a column that contains the same unique identifiers. In our example, both the OrdersData and CustomerData tables have a 'Customer ID' column. This common column is called the "key." It acts as a bridge between the two datasets.

It doesn't matter if the column names are slightly different (e.g., 'CustomerID' in one and 'Cust_ID' in another), as long as the data inside them matches up.

Checking for and Creating the Relationship

Power BI is pretty smart and often tries to detect relationships automatically based on column names. Let's see if it worked, and if not, we'll create the link manually.

1. In Power BI Desktop, click on the Model view icon on the far left side. It looks like three connected boxes.
2. This view shows you all the tables in your model as boxes. If Power BI successfully detected the relationship, you’ll see a line connecting your OrdersData and CustomerData boxes, linking the 'Customer ID' fields.
3. If a relationship line isn't there: No problem! You can create it yourself in seconds. Simply click and hold the 'Customer ID' column in your OrdersData table, drag your mouse over to the 'Customer ID' column in the CustomerData table, and release the mouse button.

A line will appear, solidifying the connection. You have now successfully linked your two sheets!

Step 3: Build Your Combined Visualization

With the relationship established, you can now build reports using columns from both tables in a single visual, as if they were one big dataset.

Let's go back to our initial question: "Which cities are driving the most revenue?"

1. Return to the Report view (the bar chart icon at the top of the left-hand panel).
2. In the Visualizations pane, select an appropriate chart, like a Clustered column chart.
3. From the Fields pane on the right, expand your CustomerData table. Drag the City column to the 'X-axis' field for the chart.
4. Next, expand your OrdersData table. Drag the Revenue column to the 'Y-axis' field.

Instantly, Power BI generates a chart showing total revenue broken down by city. This visual requires data from both tables - 'City' comes from your customer sheet, and 'Revenue' comes from your order sheet. This powerful analysis is only possible because you created that relationship.

Common Stumbling Blocks (And How to Fix Them)

Sometimes relationships don't work perfectly on the first try. Here are a few common issues and their solutions, which are typically handled in the Power Query Editor (click Transform data on the Home ribbon).

• Dirty Data: A common culprit is inconsistent data. "NY" in one sheet and "New York" in another won't match. Extra spaces ("ID-123 " vs. "ID-123") can also break relationships. You can fix this by using the 'Replace Values' and 'Trim' functions in Power Query to standardize your key column.
• Wrong Data Type: If 'Customer ID' is stored as a number in one table and as text in another, Power BI can't connect them. In Power Query, make sure the data type for both key columns is identical (e.g., both set to 'Whole Number' or both set to 'Text').
• Mismatched Key Names: If Power BI doesn't create the relationship automatically, it might be because the key columns have completely different names (e.g., "ID" and "Customer_Identifier"). You can either rename one of the columns in Power Query for clarity or just proceed with the manual drag-and-drop connection.

Final Thoughts

Being able to connect two (or more) sheets is a fundamental skill in Power BI that transforms flat data into a web of interconnected insights. Following the process of preparing your Excel file with tables, importing the data, and creating relationships in the Model view will allow you to build far more powerful and comprehensive reports.

While Power BI is an effective tool, mastering relationships, data modeling, and reporting can take significant time. We built Graphed to remove this friction entirely. Instead of manually importing sheets and defining relationships, you can connect your data sources - like Google Analytics, Shopify, Salesforce, and even spreadsheets - and then simply describe the dashboard you want in plain English. Graphed automates the connections, relationships, and chart-building, giving you a real-time, interactive dashboard in seconds, not hours.