How to Create a Power BI Report from Multiple Datasets

Building a Power BI report from a single Excel sheet is a great start, but the real magic happens when you combine data from multiple sources. Bringing together sales data from your CRM, ad performance from Google Ads, and website traffic from Google Analytics finally lets you see the whole picture. This article provides a step-by-step guide on how to connect, clean, and combine multiple datasets in Power BI to create a truly comprehensive report.

Why Combine Datasets? The Case for a Unified View

Working with siloed data is like trying to solve a puzzle with half the pieces missing. Each platform gives you a narrow view of performance, but it doesn't tell you how everything connects. By combining datasets, you can finally answer critical business questions that are impossible to address with a single source.

For example, imagine you want to understand the true return on investment (ROI) for your marketing campaigns. To do this, you need:

• Ad Spend Data: From platforms like Facebook Ads or Google Ads.
• Sales Data: From an e-commerce platform like Shopify or a CRM like Salesforce.
• Website Traffic Data: From Google Analytics to see the customer journey.

By bringing these three datasets into Power BI, you can connect ad clicks to website visits and ultimately to actual sales revenue. This provides a holistic view, turning isolated metrics into actionable business intelligence. Combining data helps you enrich your analysis, uncover hidden trends, and make decisions based on the full story, not just a single chapter.

Step 1: Planning Your Data Model Before You Start

Before you dive into Power BI, take a few minutes to sketch out a plan. The goal is to build a simple and efficient data model, which is just a fancy term for how your different data tables will be connected.

Think about your data in two main categories:

• Fact Tables: These tables contain the numbers you want to measure, like sales amounts, transactions, sessions, or ad clicks. They record events that have happened. Your main sales ledger is a classic fact table.
• Dimension Tables: These tables contain the descriptive information that gives context to your fact tables. Think of them as lookup tables for customers, products, dates, or sales reps. They describe the "who, what, when, and where" behind the numbers.

The most important part of your plan is identifying the common columns, or "keys," that will link these tables together. For instance, a Sales table (fact) can connect to a Products table (dimension) using a shared ProductID column. Similarly, it can connect to a Customers table via a CustomerID column. Identifying these relationships upfront will make the entire process smoother.

Step 2: Connecting to Your Different Data Sources

With your plan in mind, it's time to bring your data into Power BI Desktop. Power BI can connect to hundreds of different data sources, from simple flat files to cloud-based applications.

Here’s how to get started:

1. Open a new Power BI report.
2. In the Home ribbon, click on Get Data. A dropdown menu and a dialogue box will appear, showing all the common data sources.
3. Select your first data source. For this example, let's say it's an Excel workbook containing sales transactions. Click Connect and navigate to your file.
4. The Navigator window will open, showing you the sheets or tables within your workbook. Select the data you need and click Transform Data. Do not click Load yet. This will open the Power Query Editor.
5. Now, let's add a second source. While still in the Power Query Editor, click New Source in the Home ribbon and select your next data source – for example, a CSV file with customer details.
6. Follow the prompts to connect. You'll now see both datasets listed as separate queries in the Queries pane on the left side of the Power Query Editor.

Repeat this process for every dataset you need to include in your report. All your raw data is now staged and ready for the next step: cleaning.

Step 3: Cleaning and Preparing Your Data with Power Query

Power Query Editor is your data workshop. It's where you clean, shape, and prepare your data before it gets loaded into your final report. A few minutes spent cleaning here will save you hours of headaches later on.

For each query (dataset) you loaded, perform these basic cleaning tasks:

• Check Data Types: Power BI is pretty good at guessing data types, but you should always double-check. Make sure dates are formatted as dates, numbers as numbers, and text as text. You can change the data type by clicking the icon next to the column header.
• Rename Columns: Give your columns clear, intuitive names. "Cust_ID" is better as "CustomerID." This makes building visuals much easier.
• Remove Unnecessary Columns: If a column isn't relevant to your final report, get rid of it. A leaner dataset performs faster. Right-click the column header and select Remove.
• Handle Nulls or Errors: Use the Remove Rows or Replace Values features to clean up any blank entries or errors that could skew your analysis.

The beauty of Power Query is that every transformation you make is recorded as a step in the "Applied Steps" pane on the right. This means your cleaning process is repeatable and can be easily edited if you make a mistake.

Step 4: Combining Your Datasets Using Merge and Append

Once your individual datasets are clean, it's time to combine them. Power Query gives you two primary methods for this: Append and Merge.

When to Use Append Queries

Use Append when you want to stack tables on top of each other. This is perfect when you have data with the exact same columns but spread across multiple files or tables. A common use case is combining monthly sales reports (e.g., January Sales, February Sales, March Sales) into a single master sales table for the quarter.

To append tables:

1. In the Power Query Editor, select one of the tables you want to stack.
2. Go to the Home tab and click the dropdown arrow on Append Queries.
3. Select Append Queries as New. This leaves your original queries untouched and creates a new, combined query.
4. In the dialogue box, choose whether you are combining two tables or three or more, and then select the tables to append. Click OK.

You now have a new table containing all the rows from the selected tables, ready for analysis.

When to Use Merge Queries

Use Merge when you want to join two tables side-by-side based on a common key. This is similar to a VLOOKUP or INDEX/MATCH in Excel but far more powerful. You would use Merge to add descriptive columns from a dimension table to your fact table. For example, adding Product Name and Category (from a Products table) to your Sales table.

To merge tables:

1. Select your fact table (e.g., Sales).
2. In the Home tab, click the dropdown arrow on Merge Queries.
3. Select Merge Queries as New.
4. In the top dropdown of the Merge window, your Sales table should be selected. In the bottom dropdown, select the dimension table you want to join (e.g., Products).
5. Select the matching key column in both tables by clicking on them (e.g., ProductID). Power BI will show you how many rows match.
6. For the Join Kind, 'Left Outer' is the most common and is usually the default. This keeps all rows from the top table (your sales data) and pulls in matching information from the bottom table. Click OK.

This creates a new query with a new column that frighteningly just says "Table." Don't panic! Click the expand icon (two arrows pointing in opposite directions) on that column header, uncheck "Use original column name as prefix," and select the columns you want to add to your sales table (e.g., ProductName, Category). Click OK, and your sales table is now enriched with product details.

Step 5: Defining Relationships Between Your Tables

Once you've cleaned and combined your data in Power Query, click Close & Apply in the top-left corner. This loads your prepared data into the Power BI data model.

Next, it's time to formalize the connections between your tables. For this, you'll use the Model view.

1. On the left-hand side of the Power BI window, click the icon that looks like three connected boxes to open the Model view.
2. You'll see a diagram showing all the tables you loaded. Power BI is smart and will often try to automatically create relationships based on matching column names. Sometimes it gets them right, but you should always verify them.
3. To create a relationship manually, simply click and drag the key column from one table and drop it directly onto the corresponding key column in the other table. For example, drag CustomerID from your Customers table and drop it on CustomerID in your Sales table.
4. A line will appear connecting the two tables. You can double-click this line to edit the relationship's properties, like its cardinality (most commonly "one-to-many") and cross-filter direction. A "one-to-many" relationship means one customer can have many sales.

By establishing these relationships, you're telling Power BI how all your tables are related. This is the crucial step that enables you to build visualizations using fields from multiple different tables.

Step 6: Building Your Unified Report

This is where all your hard work pays off. Go back to the Report view (the bar chart icon on the left) to start building visuals.

Because you've properly modeled your data, you can now seamlessly use fields from any of your tables within the same chart. In the Data pane on the right, you'll see all of your tables listed. You can expand them to see the fields within.

Try this for yourself:

• Create a new Clustered column chart.
• From your Sales table, drag the SalesAmount field onto the Y-axis.
• From your Products table, drag the Category field onto the X-axis.

Voilà! You’ve just created a single visualization that uses data from two different tables. Power BI can do this because it understands the relationship you defined between them via the ProductID. Now you can slice your sales data by product, analyze customer trends by region, and build a truly integrated report that provides a complete view of your business performance.

Final Thoughts

Combining multiple datasets in Power BI moves you beyond simple, one-dimensional reporting and into true business intelligence. By connecting your sources, shaping data in Power Query, and building a relational model, you can unlock insights that were previously hidden in disconnected spreadsheets and platforms.

We know that stitching together data from sources like Shopify, Google Analytics, and various ad platforms to build these relationships can be tedious and time-consuming. At Graphed, we automate this entire process. We connect directly to your marketing and sales tools, handle the data warehousing and modeling for you, and allow you to build real-time, unified dashboards just by describing what you want in simple English. It turns hours of manual prep work into a 30-second conversation, so you spend less time wrangling data and more time acting on it.