Getting Data from Multiple Sources in Power BI: A Complete Beginner-Friendly Guide

Introduction

The foundation of every successful Power BI report is reliable data ingestion. No matter how visually appealing your dashboards are, if the underlying data is incomplete, inconsistent, or poorly understood, the insights will be misleading.

In real-world business environments, data rarely comes from a single source. As a Data Analyst, you may need to work with:

• Excel files
• CSV text files
• SQL Server databases
• JSON APIs
• PDF reports
• SharePoint folders

All within the same project.

Power BI is designed to handle this complexity through its powerful Get Data and Power Query capabilities.

In this blog, you’ll learn how to connect to multiple data sources in Power BI, preview the data, and assess its quality before building your data model. By the end, you’ll be confident working with diverse data sources and preparing them for meaningful analysis.

High-Level Overview of Power BI Data Architecture

In this workflow, Power BI operates as the central hub where data from multiple sources is brought together and prepared for analysis.

Our architecture consists of:

• Power BI Desktop → reporting, modeling, and development environment

• Multiple data sources, such as:

  • Excel and Text/CSV files
  • SQL Server databases
  • JSON and PDF files
  • SharePoint folders

• Power Query Editor → for cleaning, transforming, and profiling data

All data flows into Power BI through Power Query, where it is reviewed and prepared before loading into the data model.

What You’ll Accomplish in This Guide

In this step-by-step walkthrough, you will:

• Open and configure Power BI Desktop
• Connect to data from Excel, CSV, Database, SQL Server, JSON, PDF, and SharePoint
• Preview and understand source data using Power Query
• Use Column Quality, Column Distribution, and Column Profile
• Identify common data quality issues early
• Prepare datasets for modeling and reporting

Getting Started with Power BI Desktop

To practice along with this guide, first download the practice files from the link below:

🔗 https://github.com/MicrosoftLearning/PL-300-Microsoft-Power-BI-Data-Analyst/raw/Main/Allfiles/Labs/01-get-data-in-power-bi/01-get-data.zip

After downloading:

1. Extract the folder.
2. Open 01-Starter-Sales Analysis.pbix in Power BI Desktop.

This starter file disables automatic relationship detection so you can focus specifically on data ingestion and profiling.

Getting Data from SQL Server

Enterprise-level data is often stored in relational databases. Power BI connects easily to SQL Server.

Steps to connect:

• Go to Home → Get Data → SQL Server

• Enter:

  • Server: localhost
  • Database: leave blank

• Select Windows Authentication (select Windows > Use my current credentials, and then Connect).

Select OK if you receive a warning that an encrypted connection cannot be established.

• In the Navigator pane. Expand the AdventureWorksDW2020 database

• Select the following tables:

  • DimEmployee
  • DimEmployeeSalesTerritory
  • DimProduct
  • DimReseller
  • DimSalesTerritory
  • FactResellerSales

• Click Transform Data

Power Query Editor opens with six queries loaded from SQL Server.

Previewing Data in Power Query Editor

Power Query allows you to understand the data before loading it into the model.

Queries Pane

Each table appears as a separate query on the left. Selecting a query displays a preview of its contents.

Dimension Tables (Dim)

Examples:

• DimEmployee:one row per employee

• DimProduct:one row per product

• DimReseller:one row per reseller

• DimSalesTerritory:regions, countries, and groups

Fact Tables (Fact)

• FactResellerSales: one row per sales order line

Understanding the difference between fact and dimension tables is essential for proper star-schema data modeling in Power BI.

Using Power Query Data Profiling Features

Power Query includes built-in tools to help assess data quality before modeling.

Column Quality

Enable:

View → Column Quality

This reveals:

• Percentage of valid values
• Empty (null) values
• Errors

Example insight:

• The Position column in DimEmployee contains 94% empty values, signaling a potential data quality issue.

Column Distribution

Enable:

View → Column Distribution

You can now see:

• Number of distinct values
• Number of unique values

Example:

• EmployeeKey shows the same distinct and unique count → meaning every row is unique (useful when creating keys and relationships).

Column Profile

Enable:

View → Column Profile

Then select a column, such as BusinessType in DimReseller.

You may notice inconsistent labels:

• “Warehouse”
• “Ware House” (misspelled)

This inconsistency must be corrected before analysis to prevent inaccurate grouping or reporting errors.

Getting Data from Text/CSV Files

Flat files are extremely common in reporting workflows.

Importing a CSV file

Step 1: Home → Get data → Text/CSV

Step 2: Select ResellerSalesTargets.csv

This file contains:

• One row per salesperson per year
• Monthly sales targets
• Hyphens instead of null values

Repeat the process to import "ColorFormats.csv", which contains color formatting values.

Getting Data from Excel Files

Excel remains one of the most widely used business data tools.

To import Excel data:

Step 1: Home → Get Data → Excel

Step 2: Select the Excel file

Step 3: Then click Transform Data
Excel files are ideal for:

• Budgeting and finance sheets
• Manual business inputs
• Operational logs and trackers

Getting Data from JSON Files

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

Steps:

Step 1: Home → Get Data → JSON

Step 2: Select the JSON file or API export

Step 3: Power Query expands nested structures
Step 4: Flatten and transform fields as needed

JSON often requires extra transformation because of its hierarchical format.

Getting Data from PDF Files

Power BI can extract structured tables from PDF documents.

Steps:

Step 1: Home → Get Data → PDF

Step 2: Select the PDF file
Step 3: Choose detected tables
Step 4: Transform in Power Query

Useful for:

• Financial statements
• Bank reports
• Compliance or regulatory documents

Getting Data from SharePoint Folders

SharePoint is widely used for collaborative file storage across organizations.

Steps:

Step 1: Home → Get Data → SharePoint Folder

Step 2: Enter the SharePoint site URL and authenticate

Step 3: Filter and combine files as needed

This approach is ideal when working with "multiple files stored in a shared location".

Why Data Profiling Matters

Before building dashboards, you must:

• Identify missing values
• Detect inconsistent labels
• Validate key columns for relationships
• Understand value distributions

Skipping this step can lead to:

• Broken relationships
• Incorrect KPIs
• Misleading insights

Power Query ensures your data is 'accurate, reliable, and business-ready before visualization.

Conclusion

Getting data from multiple sources is a core skill for every Power BI data analyst. Power BI makes this process seamless by supporting a wide range of data connectors and providing powerful tools to preview and profile data before modeling.

By combining SQL Server, Excel, CSV, JSON, PDF, and SharePoint data in Power BI, you can build comprehensive, enterprise-ready reports with confidence.

Mastering this step ensures your dashboards are not only visually appealing but also accurate, trustworthy, and truly impactful.