How to Add Multiple Datasets in Power BI

Bringing multiple datasets together in Power BI is how you turn simple charts into a comprehensive story about your business performance. Instead of looking at website traffic in one report and sales data in another, you can combine them to see exactly which marketing channels are driving the most revenue. This article will walk you through the primary methods for adding and connecting multiple datasets, from basic imports to more advanced composite models.

Why Combine Datasets in Power BI?

Working with single datasets is fine, but the real power of business intelligence comes from synthesis. When you combine sources, you unlock a much deeper level of analysis that isn't possible when your data lives in separate silos.

Here’s why it's so important:

• Get the Full Picture: Imagine you have sales data in Shopify, ad spend in Facebook Ads, and website behavior in Google Analytics. Separately, they each tell part of the story. Together, you can create a complete customer journey, calculating true ROI by linking ad spend directly to purchases and seeing which landing pages convert best.
• Enrich Your Data: You might have a transaction table from a sales database with a Customer ID, but all the customer details (like name, location, and C-sat score) live in a separate CRM system like Salesforce. By joining these two datasets on Customer ID, you can filter your sales by customer region or satisfaction level, adding rich context to your analysis.
• Create Advanced Calculations: Combining different datasets allows you to create powerful DAX measures that span across tables. For example, if you import last year's sales figures from an Excel file and combine them with this year's live sales data from a SQL database, you can easily calculate year-over-year revenue growth.

Understanding Power BI's Data Connection Methods

Before you start adding data, it's helpful to know the different ways Power BI can connect to your sources. The method you choose impacts performance, data freshness, and what you can do with your model.

Import

The Import method is the most common and often the fastest for dashboard performance. When you use Import, Power BI loads a full copy of the data from the source and stores it within the .pbix file itself. Because the data is stored locally in Power BI's high-compression engine, calculations and visualizations are incredibly quick.

• Pros: Excellent performance, supports all DAX functions and Power Query transformations.
• Cons: Data is only as fresh as the last refresh, and large datasets can create very large file sizes. You must schedule data refreshes in the Power BI Service to keep it up-to-date.

DirectQuery

With DirectQuery, the data stays in the original source database. Power BI doesn't import a copy, instead, it sends queries directly to the source database every time a user interacts with a report (e.g., clicks on a slicer or filter). This is ideal for extremely large datasets that won't fit in memory or when you need real-time data.

• Pros: Data is always current, and it can handle massive datasets (terabytes in size).
• Cons: Dashboard performance can be slower since it depends on the speed of the underlying data source. There are also some limitations on the DAX functions and Power Query transformations you can use.

Live Connection

A Live Connection is similar to DirectQuery but specifically for connecting to Analysis Services models or Power BI datasets that have already been published to the Power BI service. It essentially lets you build a new report on top of a certified, governed dataset, which is great for promoting a "single source of truth" within an organization.

Method 1: Importing Multiple Sources into a Single Model

This is the most straightforward approach and the one most users start with. You simply import data from multiple different sources and build relationships between them. For this example, let's say we want to combine an Excel file of Sales Targets with a SQL database of actual sales transactions.

Step 1: Add Your First Data Source (Excel)

First, we'll import the sales targets from an Excel spreadsheet.

1. On the Home ribbon in Power BI Desktop, click on Get Data and select Excel workbook.
2. Navigate to your Excel file and click Open.
3. The Navigator window will appear and give you a list of all the worksheets in the workbook. Select the tables or sheets you want to load. In this case, we'll select our SalesTargets sheet.
4. Click Load. If your data needs cleaning (like removing columns or changing data types), click Transform Data to open the Power Query Editor first. For this example, we’ll assume it's clean and load it directly.

You have now created a data model with one table from one dataset.

Step 2: Add Your Second Data Source (SQL Server)

Now, let's bring in the actual sales data. This data lives in a SQL Server database, so we'll connect to that next.

1. Go to Get Data again, but this time select SQL Server.
2. Enter the server and database name. For the Data Connectivity mode, choose Import. Click OK.
3. The Navigator will now show the tables and views available in that database. Find and select the FactSales and DimEmployees tables.
4. Click Load to add these tables to your existing data model.

Step 3: Establish Relationships

You'll now see all three tables (SalesTargets, FactSales, and DimEmployees) in the Fields pane on the right. Currently, they are just three disconnected lists of data. To make them work together, you need to tell Power BI how they relate to each other.

1. Go to the Model view (the third icon on the left-hand pane that looks like a database diagram).
2. You'll see boxes representing each of your tables. Find a common column between them. For example, both FactSales and DimEmployees likely have an EmployeeID column.
3. Click and drag the EmployeeID column from the DimEmployees table and drop it directly onto the EmployeeID column in the FactSales table.
4. Then, let’s drag and drop the EmployeeID to our sales target tables as well to see how different members on our team are performing against a target.
5. Power BI will create a line between them - this is the relationship.

That's it! You can now build visuals that use data from all three tables simultaneously and see how the information is being properly cross-filtered.

Method 2: Using the Composite Models

Sometimes you’ll need to work with a dataset too massive for the import mode but at the same time have some of your valuable data stored locally. Here is when the composite model comes in handy. We will walk through how to achieve a similar result in the example before of our sales analysis, but in this section, we are going to add a fourth fact table coming directly from a web page as well as our Google BigQuery instance without the need to have them imported to our Power BI model.

Creating Our First Direct Query Connection

1. On a brand-new page, let’s open a blank Power BI report.
2. Let’s select Google BigQuery as our data source.
3. Then, let's set a project and select our sales project and the real-time sales table.
4. In the connectivity settings, ensure you make the Direct connection option for a live connection.

Adding Non-Direct Query Connections

Now, it's time to add a new data source using the import mode.

1. Go again to the Get Data option and select the appropriate selection for the web option and paste your URL from your sales target page.
2. You'll notice that by default, Power BI will use the import mode to bring this data into your model.
3. Upon loading this new data source, Power BI will display a warning. You'll see a message appear to indicate that you're mixing data storage modes, by clicking continue, it will automatically enable a composite model.

Method 3: Connecting to an Existing Certified Power BI Dataset

The enterprise best practice for collaboration is to create shared data sources that everyone in your organization can use to create their reports. This ensures data can be trusted and is always consistent. A Power BI dataset lets others connect, as we will show in this section.

1. First, we should create a local copy of the data. You can achieve this through a Live Connection to your data source.
2. Then connect to it from a brand new Power BI Report. Once your connection is set up to the Published Power BI Data source, you'll see a green light indicating you are connected and that the report is using this as the primary and only data source.
3. A message below the ribbon will guide you through the process. This option will enable an automatic Direct Query to your data and allow you to bring more datasets to the report and create our relationships as well. This option opens up endless possibilities for better and more consistent data sources.

Best Practices for Handling Multiple Data in Power BI

No matter the method you decide to follow, it's always important to keep a couple of things in mind before combining multiple data sources. Here are some of our recommendations:

• Ensure data consistency and quality. Sometimes, some of the data in your model will need to be cleaned, so take advantage of Power BI's query editor to transform your data before the loading process to ensure the final result is easy to interpret and visually clean for all of your stakeholders.
• Always start small. We recommend adding your connections one by one, as this will allow you to track your data model much more efficiently, making sure the relationships and creation are easier as well.
• Document your data sources: As much as possible, always add annotations to your databases and name your tables and data tables in your Power Query model so everyone will understand what is happening in each step.
• Use a Data Gateway: If you are connecting from different data sources, a data gateway should become your best choice as an extra security layer for all your connections, ensuring the connections can refresh and data flows across the entire organization properly.

Final Thoughts

Effectively blending multiple datasets is the mark of a sophisticated Power BI user. By importing various sources, creating relationships, and leveraging DirectQuery or certified datasets when needed, you transform siloed information into a cohesive and interactive reporting experience that empowers better business decisions.

While Power BI is a powerful tool for this, the process often requires significant manual effort - connecting sources, cleaning data in Power Query, and carefully building a data model before you can even build your first chart. We created Graphed to remove this friction by unifying marketing and sales data in an instant. With one-click integrations to sources like Google Analytics, Shopify, Facebook Ads, and HubSpot, you skip the manual ETL. Just ask a question in plain English like "Show me total customers by Shopify order count and segment that by average amount" or "Show me my sessions week over week or which Facebook campaign has more ROI" and our AI instantly handles the query logic, table relationships, and generates a real-time dashboard, giving you insights without opening thousands of different Excel sheets.