Getting Data from Multiple Sources in Power BI: A Practical Guide to Modern Data Integration for Analysts

Posted on by — No Comments ↓

Introduction

The foundation of every successful Power BI report is reliable data ingestion. No matter how visually stunning your dashboards are, if the data behind them is incomplete, inconsistent, or poorly structured, the insights they produce will be misleading at best and dangerous at worst.
In real-world business scenarios, data rarely lives in a single location. As a Data Analyst, you will frequently work with Excel files from finance, CSVs from operations, SQL databases from IT, APIs returning JSON, PDFs containing key tables, and SharePoint folders shared across departments, all within the same Power BI report.

Power BI is purposely built for this reality. Its powerful Get Data interface and Power Query transformation engine allow you to connect to virtually any data source, inspect its quality, and shape it before it ever reaches your data model.

In this blog, you will learn how to:

  • Connect Power BI Desktop to different data source types.
  • Use Power Query to preview, profile, and explore data.
  • Identify and resolve data quality issues before they corrupt your model.
  • build a scalable, multi-source foundation for accurate reporting and analytics.

Architecture Overview

Before connecting to any data source, it helps to understand how Power BI processes data end-to-end. The diagram below represents the layered architecture that governs how data moves from its origin to your final dashboard.

Layer Component Purpose
Source Layer Excel, CSV, PDF, JSON, SharePoint, SQL, Azure Raw data origins
Ingestion Layer Power BI Get Data Connection & authentication
Transformation Layer Power Query Editor Cleansing, shaping & merging
Modelling Layer Power BI Data Model Relationships, measures & KPIs
Presentation Layer Reports & Dashboards Insights for stakeholders

At the core of this architecture is Power Query, the engine that sits between your raw data sources and your data model. Every connection you make in Power BI flows through Power Query, where transformations are recorded as steps and applied automatically on each refresh. This means your data preparation logic is transparent, repeatable, and version-aware.

Connecting to Data from Multiple Sources

Power BI supports hundreds of data connectors organized into categories: File, Database, Power Platform, Azure, Online Services, and Others. In this section, we walk through the most commonly used connectors with clear, step-by-step instructions.

1. Excel

Excel is the most common data source in business environments. Power BI can connect to Excel workbooks and import data from named tables, named ranges, or individual worksheets.

  1. Open Power BI Desktop and navigate to Home → Get Data → Excel Workbook.
    Image 1
  2. Browse your file system and select the target .xlsx or .xls file.
    Image 2
  3. The Navigator window opens, displaying all available sheets and tables.
    Image 3
  4. Select the sheet(s) or named table(s) you want to import.
    Image 4
  5. Click Load to import directly, or Transform Data to open Power Query for pre-load cleaning.

2. Text / CSV files
CSV files are ubiquitous in data workflows. They are frequently exported from ERP systems, CRMs, and operational databases. Power BI handles them natively with automatic delimiter detection.

Steps to Connect

  1. Navigate to Home → Get Data → Text/CSV.
    Image 1
  2. Browse and select your .csv or .txt file.
    Image 2
  3. Power BI auto-detects the delimiter (comma, tab, semicolon) and displays a preview.
  4. Verify that columns are correctly split and data types are detected.
  5. Click Load or Transform Data to proceed.
    Image 3

3. PDF
Power BI can extract tabular data embedded in PDF documents, a common requirement when working with published financial reports, government data releases, or supplier price lists.

Steps to Connect

  1. Navigate to Home → Get Data → PDF.
    Image 1
  2. Browse and select the target PDF file.
    Image 2
  3. Power BI scans the document and attempts to detect table structures by page.
  4. In the Navigator, you will see tables labelled by page (e.g., Table001 (Page 1)).
  5. Select the relevant table(s) and click Transform Data to review before loading.

4. SharePoint Folder

Many organizations store operational files — weekly reports, regional submissions, survey exports — in SharePoint. Power BI’s SharePoint Folder connector automatically combines all matching files in a folder into a single unified dataset.
Steps to Connect

  1. Navigate to Home → Get Data → SharePoint Folder.

Image 1

  1. Enter the root SharePoint site URL (e.g., https://yourcompany.sharepoint.com/sites/analytics).
    Image 2
  2. Click OK and sign in with your Microsoft 365 credentials if prompted.
  3. Power BI lists all files in the SharePoint library. Filter by folder path or file extension as needed.
  4. Click Combine & Transform Data to merge files of the same structure into one table.
    Image 3
  5. Power Query creates a combination query with a sample file for schema definition. Validate and apply.

5. JSON
JSON files are commonly generated by APIs and web-based applications.

Steps to connect:

  1. Home → Get Data → JSON
    Image 1
    Image 2
  2. Power Query expands nested structures
  3. Flatten and transform fields as needed
    Image 3
    Image 4

JSON often requires extra transformation because of its hierarchical format.
**

  1. SQL Server**
    SQL Server is one of the most widely used enterprise databases and one of Power BI’s most mature connectors. Whether running on-premises or in Azure (as Azure SQL Database), the connection process is nearly identical.
    Steps to Connect

  2. Navigate to Home → Get Data → SQL Server.
    Image 1

  3. Enter the server name (e.g., localhost, 192.168.1.10, or yourserver.database.windows.net for Azure SQL).

  4. Optionally enter the database name, or leave blank to browse all databases on the server.
    Image 2

  5. Click OK and select your authentication method: Windows, Database, or Microsoft Account.
    Image 2

  6. In the Navigator pane, expand the database (e.g., AdventureWorksDW2022).
    Image 3

  7. Select the required tables — for example, RetailSales

  8. Click Transform Data to review the data in Power Query before loading.
    Image 4

Conclusion

Connecting to multiple data sources in Power BI is more than a technical configuration step, it is the foundational skill that determines the quality, reliability, and credibility of everything you build on top of it.
As you have seen across this guide, modern analytics environments are inherently diverse. Data lives in Excel workbooks, relational databases, cloud services, SharePoint libraries, JSON APIs, and PDF documents. Power BI’s Get Data framework, combined with the transformative power of Power Query, gives you a unified, repeatable approach to connecting and preparing all of it.

But the real value is not in the connections themselves. It is in what happens when you treat data ingestion as a deliberate discipline:

  • You surface data quality issues before they corrupt your reports
  • You document your transformation logic in a transparent, auditable way
  • You build data models that are accurate, performant, and easy to maintain
  • You earn the trust of stakeholders by delivering insights they can rely on

The connectors covered in this blog: Excel, Text/CSV, PDF, JSON, SharePoint Folder, and SQL Server, represent the most common patterns in real-world analytics work. Mastering these will prepare you to handle the vast majority of data integration challenges you will encounter as a professional Data Analyst.

As you grow in your Power BI practice, continue exploring advanced capabilities: incremental refresh for large tables, query folding for performance optimization, dataflows for reusable data preparation, and composite models for combining DirectQuery and import sources.

Leave a Reply