DEV Community

Cover image for Getting Data from Multiple Sources in PowerBI: A Practical Guide to Modern Data Integration
Timothy Atinuke
Timothy Atinuke

Posted on

Getting Data from Multiple Sources in PowerBI: A Practical Guide to Modern Data Integration

#microsoft #beginners

INTRODUCTION

According to Microsoft, Power BI is a complete reporting solution that offers data preparation, data visualization, distribution, and management through development tools and an online platform. Power BI can scale from simple reports using a single data source to reports requiring complex data modeling and consistent themes. Use Power BI to create visually stunning, interactive reports to serve as the analytics and decision engine behind group projects, divisions, or entire organizations.
The foundation of every successful Power BI report is reliable data ingestion. Before a report can be successfully created, ability to extract data from various data sources is the first crucial step to building an effective report. Interacting with SQL Server is different from Excel, so learning the nuances of how data connection from different sources works is important in order to be able to use other PowerBI tools for effective decision making.
In most real-world business contexts, data is typically spread across multiple sources rather than confined to one. A data analyst may need to integrate data from Excel files, CSVs, SQL Server databases, PDFs, JSON APIs, and SharePoint folders into a unified report. Power BI is well-equipped for this task, offering powerful tools like Get Data and Power Query to efficiently connect, combine, and transform data from various sources. This guide explores how Power BI enables multi-source data integration and provides a step-by-step approach to implementing it effectively.
In this guide, you will learn how to:
• Connect Power BI to multiple data sources efficiently
• Use Power Query to preview and explore your data
• Detect and resolve data quality issues early
• Build a strong foundation for accurate data modeling and reporting.

Architecture Overview

At a high level, Power BI follows a layered architecture which consists of:
• Power BI Desktop as the reporting and modeling tool
• Multiple data sources, including:
o Excel and Text/CSV files
o SQL Server databases
o JSON and PDF files
o SharePoint folders
All data flows into Power BI through Power Query, where it is reviewed and prepared before loading into the data model.
Connecting Data from Multiple Sources
Power BI allows you to connect to a wide range of data sources. Below are step-by-step guides for each major source.

Step 1: Connecting to Excel

  1. Open Power BI Desktop Image 1
  2. Navigate to Home → Get Data → Excel Image 2
  3. Browse and select your Excel file Image 3
  4. In the Navigator window, select the required sheets or tables Image 4
  5. Click Load (to import directly) or Transform Data (to clean first)

Step 2: Connecting to Text/CSV Files

  1. Open Power BI Desktop
  2. Navigate to Home → Get Data → Text/CSV Image 5
  3. Browse and select the CSV file (e.g., MultiTimeline.csv) Image 6
  4. Preview the dataset in the dialog window Image 7
  5. Click Load or Transform Data

Step 3: Connecting to PDF

  1. Open Power BI Desktop
  2. Navigate to Home → Get Data → PDF Image 8
  3. Select the PDF file Image 9
  4. Wait for Power BI to detect available tables
  5. Select the desired table(s)
  6. Click Load or Transform Data Image 10

Step 4: Connecting to JSON

  1. Open Power BI Desktop
  2. Navigate to Home → Get Data → JSON Image 11
  3. Select the JSON file or input API endpoint Image 12 Image 13
  4. Load the data into Power Query
  5. Expand nested fields to structure the data properly Image 14
  6. Click Close & Apply

Step 5: Connecting to SharePoint Folder

  1. Open Power BI Desktop Image 15
  2. Navigate to Home → Get Data → SharePoint Folder Image 16
  3. Enter the SharePoint site URL
  4. Click OK and authenticate if required Image 17
  5. Select files from the folder
  6. Click Combine & Transform Data Image 18

Step 6: Connecting to MySQL Database

  1. Open Power BI Desktop Image 19
  2. Navigate to Home → Get Data → MySQL Database Image 20
  3. Enter the server name and database Image 21
  4. Provide authentication credentials and click connect Image 22
  5. Select the required tables
  6. Click Load or Transform Data Image 23

Step 7: Connecting to SQL Server

  1. Open Power BI Desktop
  2. Navigate to Home → Get Data → SQL Server Image 24
  3. Enter the server name (e.g., localhost) Image 25
  4. Leave the database field blank (or specify one if needed)
  5. Click OK
  6. Select authentication method (e.g., Windows credentials)
  7. In the Navigator pane, expand the database (e.g., AdventureWorksDW2020)
  8. Select required tables such as: o DimEmployee o DimProduct o DimAccount
  9. Click Transform Data to open Power Query Editor Image 27

Step 8: Connecting to Web Data

  1. Open Power BI Desktop
  2. Navigate to Home → Get Data → Web Image 28
  3. Enter the URL of the web page or API Image 29
  4. Click OK
  5. Select the data table or structure detected
  6. Click Load or Transform Data Image 30

Step 9: Connecting to Azure Analysis Services

  1. Open Power BI Desktop
  2. Navigate to Home → Get Data → Azure → Azure Analysis Services Image 31
  3. Enter the server name
  4. Select the database/model
  5. Choose connection mode (Live connection recommended)
  6. Click Connect Image 32

Conclusion

Integrating data from multiple sources in Microsoft Power BI is a foundational skill for modern data analysts. By understanding the architecture and following a structured approach, you can transform fragmented datasets into cohesive, insight-driven reports. Ultimately, great analytics begins with great data and great data begins with how well you connect, prepare, understand and use it to make business decisions.
Mastering tools like Power Query and applying best practices in data modeling will significantly enhance the quality and performance of your analytics solutions.

Top comments (0)