How to Connect Multiple Datasets in Power BI

The real power of Power BI isn't just making pretty charts from a single spreadsheet, it's weaving together data from different sources to tell a complete story. If your sales data is in one file, your marketing spend in another, and your customer information in a third, you're looking at separate, incomplete puzzle pieces. This article guides you step-by-step through connecting multiple datasets in Power BI to create a single, unified view of your business performance.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Bother Connecting Multiple Datasets?

Connecting data sources moves you from simply reporting on metrics to genuinely understanding your business. Isolated datasets can only answer basic questions. By combining them, you can uncover the relationships between different parts of your operation.

Consider a common scenario for an e-commerce store:

• You have Sales Data from your Shopify store (Products Sold, Revenue, Order Dates).
• You have Marketing Data from Facebook Ads (Campaign Spend, Impressions, Clicks).
• You have Website Analytics from Google Analytics (Session sources, User behavior, Landing Pages).

Separately, these tell you:

• How much you sold.
• How much you spent on ads.
• How much traffic you got.

But when you connect them, you can start answering the questions that actually matter:

• Which specific Facebook Ad campaign drove the most revenue?
• What is the true Return on Ad Spend (ROAS) for each campaign?
• Do customers who arrive from organic search spend more or less than those from paid ads?

Combining datasets lets you see the full journey, from ad click to website visit to final purchase. This is the foundation of meaningful business intelligence.

Step-by-Step: Getting Your Data into Power BI

The first part of the process is simply importing all your separate data files into a single Power BI project. Think of this as gathering all your puzzle pieces in one box before you start putting them together.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Open Power BI Desktop and Find "Get Data"

When you open a new project in Power BI Desktop, your main starting point is the Get Data button on the Home ribbon. Clicking this button reveals a dropdown with the most common data sources, like Excel workbooks and SQL Server databases. Clicking "More…" at the bottom of this list opens a window showcasing the hundreds of different data sources Power BI can connect to, from simple text/CSV files to SaaS platforms like Salesforce.

Step 2: Connect to Your First Data Source

Let's start by importing a sales report from an Excel file.

1. Click Get Data → Excel workbook.
2. Navigate to your file (e.g., SalesData.xlsx) and click Open.
3. The Navigator window will appear, showing you the sheets and tables available within that Excel file. Select the checkbox next to the table you want to import (e.g., Sales).

At the bottom of the Navigator window, you have two key choices:

• Load: This loads the data as-is directly into your Power BI model. It’s quick and easy if you know your data is already clean.
• Transform Data: This opens the Power Query Editor, a powerful tool for cleaning, shaping, and transforming your data before loading it. This is where you would handle things like removing empty rows, splitting columns, or changing data types.

For this example, we'll assume the data is mostly clean, so you can click Load.

Step 3: Add Your Other Data Sources

After loading your sales data, the right-hand Fields pane in Power BI will show your first table. Now, just repeat the process for every other dataset you need.

Let’s say your marketing spend is in a separate CSV file and your customer list is in another Excel workbook.

1. Navigate back to the Home ribbon and click Get Data → Text/CSV. Select your MarketingSpend.csv file, review it in the Navigator, and click Load.
2. Go back to Get Data → Excel workbook. Select CustomerDetails.xlsx, choose the correct table, and click Load.

After a few moments, your Fields pane will now show three separate, unrelated tables: Sales, MarketingSpend, and CustomerDetails. You’ve successfully imported multiple datasets, but they can’t talk to each other yet. That's where data modeling comes in.

The Core Concept: Creating Relationships in the Model View

Now that your data is in Power BI, you need to tell Power BI how these tables relate to one another. You do this in the Model view.

On the far left of your Power BI window, you'll see three icons: Report, Data, and Model. Click the Model view icon (it looks like a diagram with three connected boxes).

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What are Relationships?

Relationships are the logical connections between your tables. They work by linking a column in one table to a corresponding column in another. For a relationship to work, the two tables must share a common column, often called a key.

• Primary Key: A column with unique values in a table. For example, in your CustomerDetails table, a CustomerID column where each customer has one unique ID is a perfect primary key.
• Foreign Key: The same column appearing in another table, where its values may repeat. Your Sales table would likely also have a CustomerID column, but here the IDs will repeat because one customer can make multiple purchases.

The relationship links the primary key (CustomerID in CustomerDetails) to the foreign key (CustomerID in Sales). This tells Power BI that any given sale belongs to the customer with that matching ID.

How to Create Relationships in Power BI

In the Model view, you’ll see each of your imported tables represented as a box listing all its columns. To create a relationship, simply find the common key column in two tables and drag-and-drop it.

1. Find the CustomerID column in your CustomerDetails table.
2. Click and hold your mouse on CustomerID.
3. Drag it over to your Sales table and release it on top of the CustomerID column there.

Power BI will automatically draw a line between the two tables, which signifies that a relationship has been created. It will even analyze the data to determine the type of relationship, which brings us to two important concepts: cardinality and cross-filter direction.

A Quick Primer on Cardinality and Cross-Filter Direction

When you create a relationship, Power BI makes a few assumptions about how your tables interact. You can see these by double-clicking the relationship line. While the defaults are usually fine for beginners, understanding what they mean is helpful.

Understanding Cardinality

Cardinality describes the nature of the relationship between two tables. The most common types are:

• One-to-many (1:*): This is the most prevalent type. One customer can have many sales. One product can be in many orders. Your CustomerDetails to Sales relationship is a classic one-to-many relationship, as indicated by the "1" on the CustomerDetails side and the asterisk (*) on the Sales side of the relationship line.
• One-to-one (1:1): Less common. This would be used if, for instance, you had a table of employees and a separate table of their assigned security badges. Each employee has only one badge, and each badge is assigned to only one employee.
• Many-to-many (:): A more complex scenario. In a university database, a single student can enroll in many classes, and a single class can contain many students. Power BI can handle this but often it’s better practice to create a "bridge" or "junction" table in between them.

Understanding Cross-Filter Direction

This setting determines the "flow" of filtering between the tables.

• Single: This is the default and recommended setting. It means filters flow from the "one" side of the relationship to the "many" side. For example, if you filter by a customer’s name from the CustomerDetails table, it will correctly filter the Sales table to show only that customer's purchases.
• Both: This allows filters to flow in both directions. While sometimes useful, it can lead to ambiguity and performance issues in complex models. As a general rule, stick with "Single" unless you have a specific, justifiable reason to change it.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

The Payoff: Building Your Unified Report

With your data imported and your relationships established, you can now build reports that seamlessly combine information from all your sources. Head back to the Report view (the bar chart icon).

Thanks to the relationships you built, you can now create a single visual using columns from totally different tables as if they were one.

Let's build a simple chart to see which marketing campaigns drove sales from which customers.

1. Select a Stacked column chart from the Visualizations pane.
2. From the Fields pane on the right:

Instantly, you have a single chart that tells a powerful story, showing which customer spent the most, broken down by the marketing campaign associated with their purchase date. This visual would be absolutely impossible to create if the tables weren't correctly related. You've successfully transformed disparate data into connected business insight.

Final Thoughts

Connecting multiple datasets is the key to unlocking true analytical power in Power BI. By importing your data sources and defining the relationships between them in the Model view, you create a foundation for cohesive, insightful reports that give you a complete picture of performance.

While powerful, manually connecting data sources, shaping the data, and managing relationships across platforms like Google Analytics, Shopify, Facebook Ads, and your CRM can be a huge time-sink. We built Graphed to do all this heavy lifting for you. Simply connect your marketing and sales tools with a few clicks, and our AI data analyst handles the modeling automatically. Then, just ask questions in plain English - like "create a dashboard showing my top-performing ad campaigns by revenue" - and get a live, interactive dashboard in seconds, without ever needing to worry about the underlying tables or relationships.