Multiple data sources in one report in Power BI - Deep Dive

Overview - Multiple data sources in one report

What is it?

Multiple data sources in one report means combining information from different places into a single Power BI report. These sources can be files, databases, or online services. This lets you see and analyze all your data together without moving it all into one place first. It helps create richer, more complete reports.

Why it matters

Without combining multiple data sources, you would have to look at each data set separately, making it hard to get the full picture. This slows down decision-making and can cause mistakes. Using multiple sources in one report saves time and helps you find connections between different data sets easily.

Where it fits

Before learning this, you should know how to connect to a single data source and create basic reports in Power BI. After this, you can learn about data modeling, relationships between tables, and advanced data transformations to make your combined data more useful.

Mental Model

Core Idea

Bringing data from different places into one report lets you see the whole story without moving or copying all the data into one spot.

Think of it like...

It's like making a fruit salad by mixing apples, bananas, and grapes from different bowls onto one plate, so you can enjoy all the flavors together without putting all the fruit into one big bowl first.

┌───────────────┐   ┌───────────────┐   ┌───────────────┐
│ Data Source 1 │   │ Data Source 2 │   │ Data Source 3 │
└──────┬────────┘   └──────┬────────┘   └──────┬────────┘
       │                   │                   │
       ▼                   ▼                   ▼
   ┌─────────────────────────────────────────────┐
   │           Power BI Report Model              │
   │  (Combines and relates data from sources)   │
   └─────────────────────────────────────────────┘
                      │
                      ▼
             ┌─────────────────┐
             │ Visualizations  │
             └─────────────────┘

Build-Up - 7 Steps

1

Foundation

Connecting to a single data source

Concept: Learn how to connect Power BI to one data source and load data.

Open Power BI Desktop. Click 'Get Data'. Choose a source like Excel or SQL Server. Select your file or database. Load the data into Power BI. You now have one data table to work with.

Result

You see your data loaded in Power BI's data view and can start building visuals.

Understanding how to connect to one data source is the first step before combining multiple sources.

2

Foundation

Understanding data tables and relationships

Concept: Learn what tables and relationships mean in Power BI reports.

Data from sources appear as tables in Power BI. Relationships link tables by common columns, like IDs. These links let visuals use data from multiple tables together.

Result

You can see tables and create relationships in the model view.

Knowing how tables relate helps you combine data from different sources effectively.

3

Intermediate

Adding multiple data sources to one report

🤔Before reading on: do you think Power BI can combine data from different source types in one report? Commit to your answer.

Concept: Power BI allows loading data from many sources into one report file.

In Power BI Desktop, use 'Get Data' multiple times to add different sources like Excel, SQL, or web data. Each source creates its own tables. You can then relate these tables in the model.

Result

Your report contains tables from different sources ready to be connected.

Knowing you can mix source types expands your ability to create comprehensive reports.

4

Intermediate

Creating relationships between different source tables

🤔Before reading on: do you think relationships between tables from different sources behave the same as those from the same source? Commit to your answer.

Concept: You can create relationships between tables even if they come from different data sources.

Go to the model view. Drag a column from one table to a matching column in another table. This creates a relationship. Power BI uses this to filter and combine data in visuals.

Result

Tables from different sources work together in your report visuals.

Understanding cross-source relationships lets you build unified reports from diverse data.

5

Intermediate

Using Power Query to transform and combine data

🤔Before reading on: do you think you must combine data sources before loading, or can you do it inside Power BI? Commit to your answer.

Concept: Power Query lets you clean, transform, and merge data from different sources inside Power BI.

Open Power Query Editor. Load tables from different sources. Use 'Merge Queries' to join tables by matching columns. You can also filter, rename, and change data types here.

Result

You get a new combined table ready for analysis without changing original sources.

Knowing how to transform and merge data inside Power BI saves time and keeps data fresh.

6

Advanced

Handling performance with multiple data sources

🤔Before reading on: do you think adding many data sources always improves report speed? Commit to your answer.

Concept: Multiple sources can slow reports; optimizing data load and model design improves performance.

Limit data by filtering in Power Query. Use import mode for faster queries. Avoid many complex relationships. Use star schema design where possible. Monitor performance with Power BI tools.

Result

Your report runs faster and uses less memory despite multiple sources.

Understanding performance trade-offs helps you build usable reports at scale.

7

Expert

Using DirectQuery and composite models with multiple sources

🤔Before reading on: do you think Power BI can combine live queries and imported data in one report? Commit to your answer.

Concept: Power BI supports combining DirectQuery (live data) and imported data using composite models.

Set some tables to DirectQuery mode to get live data from databases. Import others for speed. Power BI lets you relate these tables and build visuals combining both. This requires careful design to avoid slow queries.

Result

You get real-time insights combined with fast static data in one report.

Knowing composite models unlocks powerful hybrid reporting scenarios for real-world needs.

Under the Hood

Power BI loads data from each source into its internal data model as tables. It creates relationships between these tables to allow filtering and aggregation across sources. When you build visuals, Power BI generates queries that combine data from these tables using the relationships. For DirectQuery sources, queries run live on the source system; for imported data, queries run on the in-memory model.

Why designed this way?

Power BI was designed to be flexible, letting users combine many data sources without complex ETL processes. This saves time and keeps data fresh. The internal model and relationships allow seamless integration without physically merging data. Composite models were added later to support hybrid live and imported data, balancing performance and freshness.

┌───────────────┐   ┌───────────────┐   ┌───────────────┐
│ Source A      │   │ Source B      │   │ Source C      │
└──────┬────────┘   └──────┬────────┘   └──────┬────────┘
       │                   │                   │
       ▼                   ▼                   ▼
┌───────────────────────────────────────────────┐
│           Power BI Internal Data Model         │
│ ┌─────────┐  ┌─────────┐  ┌─────────┐          │
│ │ Table A │  │ Table B │  │ Table C │          │
│ └────┬────┘  └────┬────┘  └────┬────┘          │
│      │            │            │               │
│      └────────────┼────────────┘               │
│                   ▼                            │
│             Relationships                      │
└───────────────────────────────────────────────┘
                      │
                      ▼
             ┌─────────────────┐
             │ Visual Queries  │
             └─────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Can you create relationships only between tables from the same data source? Commit to yes or no.

Common Belief: Relationships only work between tables from the same data source.

Reality: Power BI allows relationships between tables from different data sources as long as the data is loaded into the model.

Why it matters: Believing this limits report design and prevents combining valuable data from different systems.

Quick: Do you think adding more data sources always makes your report faster? Commit to yes or no.

Common Belief: More data sources improve report speed because data is split.

Reality: Adding multiple sources can slow down reports due to complex queries and larger models.

Why it matters: Ignoring performance impact leads to slow, unusable reports.

Quick: Can you merge data from different sources only before loading into Power BI? Commit to yes or no.

Common Belief: You must combine data before loading it into Power BI, outside the tool.

Reality: Power Query inside Power BI can merge and transform data from multiple sources after loading.

Why it matters: Not knowing this causes unnecessary extra work and delays.

Quick: Does DirectQuery always work well with multiple data sources combined? Commit to yes or no.

Common Belief: DirectQuery mode works seamlessly with multiple data sources without issues.

Reality: Combining DirectQuery sources with imported data requires careful design to avoid slow or failing queries.

Why it matters: Misusing DirectQuery leads to poor performance and user frustration.

Expert Zone

1

Composite models let you mix DirectQuery and imported tables but require understanding query folding and performance trade-offs.

2

Relationships between tables from different sources can behave differently if data types or cardinality don't match exactly.

3

Power Query transformations run before data loads into the model, so complex merges can slow refresh times significantly.

When NOT to use

Avoid combining too many large data sources in one report if performance is critical; instead, consider data warehousing or pre-aggregated data sets. For real-time needs, use dedicated live dashboards or streaming data tools rather than heavy composite models.

Production Patterns

Professionals often use a star schema with fact tables from a fast source and dimension tables imported for speed. They use Power Query to clean and merge data, then create relationships carefully. Composite models are used in hybrid cloud scenarios where some data is live and some static.

Connections

Data Warehousing

Builds-on

Understanding multiple data sources in Power BI helps grasp how data warehouses centralize and prepare data for analysis.

ETL (Extract, Transform, Load)

Similar pattern

Power Query's role in combining data sources mirrors ETL processes, showing how data is cleaned and merged before analysis.

Supply Chain Management

Analogy in complexity

Just like supply chains combine parts from many suppliers to build a product, Power BI combines data from many sources to build insights.

Common Pitfalls

#1 Trying to create relationships without matching data types.

Wrong approach:

In model view, linking a text column to a number column between tables.

Correct approach:

Ensure both columns have the same data type before creating the relationship, e.g., both text or both number.

Root cause:

Misunderstanding that relationships require compatible data types to work.

#2 Loading entire large data sources without filtering.

Wrong approach:

Importing full database tables with millions of rows without any filters.

Correct approach:

Use Power Query to filter data before loading, limiting rows to only what is needed.

Root cause:

Not considering performance impact of large data volumes.

#3 Using DirectQuery for all tables without considering performance.

Wrong approach:

Setting all tables to DirectQuery mode regardless of size or complexity.

Correct approach:

Use import mode for static or large tables and DirectQuery only for live data that changes frequently.

Root cause:

Lack of understanding of DirectQuery limitations and performance trade-offs.

Key Takeaways

Power BI lets you combine data from many different sources into one report to get a complete view.

You create relationships between tables from different sources to connect data logically.

Power Query inside Power BI can transform and merge data after loading, saving time and effort.

Performance can suffer if you add too many large sources or use DirectQuery without care.

Composite models allow mixing live and imported data but require advanced design skills.

Practice

1. What is the main benefit of using multiple data sources in one Power BI report?

easy

A. You can combine different types of data for richer insights.

B. It automatically cleans all data without user input.

C. It limits the report to only one data table.

D. It removes the need for relationships between tables.

Solution

1. Step 1: Understand the purpose of multiple data sources

   Multiple data sources allow combining different datasets to get a fuller picture.

2. Step 2: Identify the benefit in report insights

   Combining data from various sources enriches the analysis and insights.

3. Final Answer:

   You can combine different types of data for richer insights. -> Option A

4. Quick Check:

   Multiple data sources = richer insights [OK]

Hint: Multiple sources mean combining data for better insights [OK]

Common Mistakes:

• Thinking multiple sources clean data automatically
• Believing multiple sources limit data to one table
• Assuming relationships are not needed

2. Which of the following is the correct way to add a new data source in Power BI Desktop?

easy

A. Right-click on a visual and select Add Data Source

B. Use the Modeling tab to create a new data source

C. Go to Home tab > Get Data > Choose source > Load

D. Drag and drop files directly onto the report canvas

Solution

1. Step 1: Recall the process to add data

   In Power BI Desktop, new data sources are added via Home tab's Get Data option.

2. Step 2: Confirm the correct menu and steps

   Choose the source type and then load the data into the report.

3. Final Answer:

   Go to Home tab > Get Data > Choose source > Load -> Option C

4. Quick Check:

   Get Data on Home tab = add source [OK]

Hint: Use Home > Get Data to add sources [OK]

Common Mistakes:

• Trying to add data from the Modeling tab
• Right-clicking visuals to add data
• Dragging files onto the canvas doesn't load data

3. Given two tables from different data sources: Sales and Products, which DAX formula correctly calculates total sales amount by product category assuming a relationship exists?

medium

A. Total Sales = SUM(Sales[Amount])

B. Total Sales = CALCULATE(SUM(Sales[Amount]), Products[Category])

C. Total Sales = SUMX(Products, RELATED(Sales[Amount]))

D. Total Sales = SUMX(Products, SUM(Sales[Amount]))

Solution

1. Step 1: Understand the relationship usage

   With a relationship between Sales and Products, SUM(Sales[Amount]) aggregates sales correctly by category in visuals.

2. Step 2: Evaluate each formula

   Total Sales = SUM(Sales[Amount]) sums Sales amount directly, which works with relationships. Total Sales = CALCULATE(SUM(Sales[Amount]), Products[Category]) syntax is invalid. Total Sales = SUMX(Products, RELATED(Sales[Amount])) misuses RELATED. Total Sales = SUMX(Products, SUM(Sales[Amount])) incorrectly sums the grand total for each product row, inflating results.

3. Final Answer:

   Total Sales = SUM(Sales[Amount]) -> Option A

4. Quick Check:

   SUM with relationships = correct total [OK]

Hint: Use SUM on sales column when relationships exist [OK]

Common Mistakes:

• Using CALCULATE without filter expression
• Misusing RELATED in SUMX
• Overcomplicating simple SUM aggregation

4. You imported two data sources but your report shows no combined data. What is the most likely issue?

medium

A. You added too many visuals on the report page.

B. You did not refresh the data after import.

C. You used the wrong file format for import.

D. You forgot to create relationships between tables.

Solution

1. Step 1: Identify why combined data is missing

   Without relationships, tables from different sources cannot connect, so combined data won't show.

2. Step 2: Check other options

   Refreshing data or file format issues usually cause errors, not missing combined data. Too many visuals do not affect data combination.

3. Final Answer:

   You forgot to create relationships between tables. -> Option D

4. Quick Check:

   Missing relationships = no combined data [OK]

Hint: Create relationships to combine data sources [OK]

Common Mistakes:

• Ignoring relationships after importing data
• Assuming refresh fixes missing relationships
• Blaming visuals for data connection issues

5. You have sales data from Excel and customer data from SQL Server. You want to create a report showing total sales per customer region. How should you combine these sources effectively?

hard

A. Create separate reports for each source and link them with bookmarks.

B. Load both sources, create a relationship between customer IDs, then build visuals using fields from both tables.

C. Load only the sales data and manually type customer regions in Power BI.

D. Merge the Excel and SQL tables into one before loading into Power BI.

Solution

1. Step 1: Load both data sources separately

   Import Excel sales data and SQL customer data into Power BI as separate tables.

2. Step 2: Create a relationship between customer IDs

   Link the tables on customer ID to connect sales with customer regions.

3. Step 3: Build visuals using fields from both tables

   Use the relationship to show total sales by customer region in one report.

4. Final Answer:

   Load both sources, create a relationship between customer IDs, then build visuals using fields from both tables. -> Option B

5. Quick Check:

   Separate sources + relationship + visuals = combined report [OK]

Hint: Load sources, relate keys, then build combined visuals [OK]

Common Mistakes:

• Trying to merge tables outside Power BI unnecessarily
• Ignoring relationships and typing data manually
• Splitting data into separate reports instead of combining