Getting Data from Multiple Sources in Power BI: A Pictorial Guide to Seamless Data Integration

Introduction

Power BI is a business intelligence and data visualization tool developed by Microsoft that enables users to connect to multiple data sources, transform raw data, and create interactive dashboards and reports.

It allows individuals and organizations to collect data from sources like Excel, databases, and cloud services; Clean and transform data using Power Query; Build visualizations such as charts, graphs, and maps; And share insights through reports and dashboards across teams.

In simple terms, Power BI helps turn raw data into meaningful insights for better decision-making.
In this guide, you will learn how to:
• Establish connections between Power BI and multiple data sources effectively
• Leverage Power Query to preview and examine datasets
• Identify and fix data quality issues at an early stage
• Create a solid foundation for accurate data modelling and reporting

Architecture Overview

At a general level, the Power BI data architecture includes:
• Power BI Desktop serving as the primary tool for reporting and data modelling
• A variety of data sources, such as:
o Excel and Text/CSV files
o SQL Server databases
o JSON and PDF files
o SharePoint folders
All data is imported into Power BI through Power Query, where it is inspected and transformed before being loaded into the data model.

Connecting Data from Multiple Sources

Power BI supports connections to numerous types of data sources. The following sections provide step-by-step instructions for each major source.

Step 1: Connecting to Excel

1. Launch Power BI Desktop
2. Go to Home → Get Data → Excel
3. Locate and select your Excel file
4. In the Navigator window, choose the necessary sheets or tables
5. Click Load (to import immediately) or Transform Data (to clean the data first)

Step 2: Connecting to Text/CSV File

1. Launch Power BI Desktop
2. Go to Home → Get Data → Text/CSV
3. Browse and select the CSV file (e.g., OYO_crosschecked.csv)
4. Review the dataset preview in the dialog box
5. Click Load or Transform Data

Step 3: Connecting to PDF

1. Launch Power BI Desktop
2. Go to Home → Get Data → PDF
3. Choose the PDF file
4. Allow Power BI to scan and detect available tables
5. Select the relevant table(s)
6. Click Load or Transform Data

Step 4: Connecting to JSON

1. Launch Power BI Desktop
2. Go to Home → Get Data → JSON
3. Select the JSON file or provide the API endpoint
4. Load the data into Power Query
5. Expand nested elements to properly structure the dataset
6. Click Close & Apply

Step 5: Connecting to SharePoint Folder

1. Launch Power BI Desktop
2. Go to Home → Get Data → SharePoint Folder
3. Input the SharePoint site URL
4. Click OK and complete authentication if prompted
5. Choose the files from the folder The option of a Sharepoint Folder requires you to have access to an internal Organizational account to have the sharepoint URL. The URL will look like https://contoso.sharepoint.com/sites/siteName.
6. Click Combine & Transform Data

Step 6: Connecting to MySQL Database

1. Launch Power BI Desktop
2. Go to Home → Get Data → MySQL Database
3. Enter the server name and database details
4. Provide the required login credentials
5. Select the desired tables
6. Click Load or Transform Data

Step 7: Connecting to SQL Server

1. Launch Power BI Desktop
2. Go to Home → Get Data → SQL Server
3. Enter the server name (e.g., localhost)
4. Leave the database field empty (or specify one if necessary)
5. Click OK
6. Select the authentication method (e.g., Windows credentials)
7. In the Navigator pane, expand the database (e.g., World)
8. Choose the required tables such as: o City o Country o CountryLanguage
9. Click Transform Data to open the Power Query Editor

Step 8: Connecting to Web Data

1. Launch Power BI Desktop
2. Go to Home → Get Data → Web
3. Enter the URL of the website or API
4. Click OK
5. Select the detected data table or structure
6. Click Load or Transform Data

Step 9: Connecting to Azure Analysis Services

1. Launch Power BI Desktop
2. Go to Home → Get Data → Azure → Azure Analysis Services
3. Enter the server name
4. Select the appropriate database or model
5. Choose the connection mode (Live connection is recommended)
6. Click Connect This structured approach enhances clarity, makes the guide more practical, and reflects real-world Power BI workflows.

Conclusion

Working with multiple data sources in Power BI goes beyond a simple technical task. It forms the backbone of every dependable and insight-driven report. As demonstrated, modern data ecosystems are diverse and complex, requiring analysts to integrate data from files, databases, cloud platforms, and web services seamlessly.
Power BI Desktop simplifies this process significantly. Its strong integration features, along with Power Query, allow you not only to access data but also to evaluate its structure, assess its quality, and understand its limitations.
However, the true benefit lies in what comes after data connection. The ability to detect inconsistencies, manage missing values, and transform raw data into a well-structured format distinguishes basic reporting from advanced analytics solutions.
In real-world applications, effective data ingestion leads to:
• Improved accuracy of insights
• Enhanced decision-making processes
• Scalable and maintainable data models
For any data professional, mastering data connectivity and preparation is essential. It ensures that every dashboard created is not only visually appealing but also reliable and meaningful.
Ultimately, successful analytics starts with high-quality data—and high-quality data depends on how well it is connected, prepared, and understood.
Power BI Desktop simplifies this process significantly. Its strong integration features, along with Power Query, allow you not only to access data but also to evaluate its structure, assess its quality, and understand its limitations.
However, the true benefit lies in what comes after data connection. The ability to detect inconsistencies, manage missing values, and transform raw data into a well-structured format distinguishes basic reporting from advanced analytics solutions.
In real-world applications, effective data ingestion leads to:
• Improved accuracy of insights
• Enhanced decision-making processes
• Scalable and maintainable data models
For any data professional, mastering data connectivity and preparation is essential. It ensures that every dashboard created is not only visually appealing but also reliable and meaningful.
Ultimately, successful analytics starts with high-quality data—and high-quality data depends on how well it is connected, prepared, and understood.