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

Source: Dev.to

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.

Architecture

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 → 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:

1. Open and configure Power BI Desktop
2. Connect to data from Excel, CSV, Database (SQL Server), JSON, PDF, and SharePoint
3. Preview and understand source data using Power Query
4. Use Column Quality, Column Distribution, and Column Profile
5. Identify common data‑quality issues early
6. Prepare datasets for modeling and reporting

Getting Started with Power BI Desktop

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

🔗

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

1. Home → Get Data → SQL Server

2. Enter:

   Server: localhost
   Database: (leave blank)

3. Select Windows Authentication (Windows → Use my current credentials) and click Connect.

4. If you receive a warning that an encrypted connection cannot be established, click OK.

5. In the Navigator pane, expand the AdventureWorksDW2020 database and select the following tables:

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

6. Click Transform Data.

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

Previewing Data in Power Query Editor

• Queries Pane – each table appears as a separate query on the left. Selecting a query displays a preview of its contents.

Dimension Tables (Dim)

Fact Tables (Fact)

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

1. View → Column Quality

   This reveals:

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

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

Column Distribution

1. View → Column Distribution

   You can now see:

   • Number of distinct values
   • Number of unique values

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

Column Profile

1. View → Column Profile

2. Select a column, e.g., 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

1. Home → Get Data → Text/CSV

2. Select ResellerSalesTargets.csv

   • 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

1. Home → Get Data → Excel
2. Select the Excel file
3. Click Transform Data

Typical contents:

• 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

1. Home → Get Data → JSON
2. Select the JSON file or API export
3. Power Query expands nested structures

Note: 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

1. Home → Get Data → PDF
2. Select the PDF file

Typical use cases:

• Financial statements
• Bank reports
• Compliance or regulatory documents

Getting Data from SharePoint Folders

SharePoint is widely used for collaborative file storage across organizations.

Steps

1. Home → Get Data → SharePoint Folder
2. Enter the SharePoint site URL and follow the prompts to connect.

(Continue with the same “Transform Data” workflow to shape the files you need.)

Step 2 – Enter the SharePoint Site URL and Authenticate

(No additional content was provided for this step.)

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
• 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.