Getting Data from Multiple Sources in Power BI

Introduction

If you’re new to Power BI, one of the first things you need to learn is how to bring data into the tool. In real-world projects, your data won’t come from just one place. Power BI bridges this gap by allowing analysts to connect, transform, and unify data from multiple sources into a single semantic model.

Whether you're working with Excel files, SQL databases, JSON documents, or cloud platforms like SharePoint and Azure, understanding how to connect these sources efficiently is critical for building scalable and insightful reports.

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, our Power BI data architecture 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

1. Navigate to Home → Get Data → Excel

1. Browse and select your Excel file

1. In the Navigator window, select the required sheets or tables

1. Click Load (to import directly) or Transform Data (to clean first)

Step 2: Connecting to Text/CSV Files

1. Open Power BI Desktop

1. Navigate to Home → Get Data → Text/CSV

1. Browse and select the CSV file (e.g., Stimulating Sustainable.csv)

1. Preview the dataset in the dialog window
2. Click Load or Transform Data

Step 3: Connecting to PDF

1. Open Power BI Desktop
2. Navigate to Home → Get Data → PDF

1. Select the PDF file

1. Wait for Power BI to detect available tables
2. Select the desired table(s)
3. Click Load or Transform Data

Step 4: Connecting to JSON

1. Open Power BI Desktop
2. Navigate to Home → Get Data → JSON

1. Select the JSON file

1. OR input API endpoint

1. Load the data into Power Query

1. Expand nested fields to structure the data properly

1. Click Close & Apply

Step 5: Connecting to SharePoint Folder

1. Open Power BI Desktop
2. Navigate to Home → Get Data → SharePoint Folder

1. Enter the SharePoint site URL

1. Click OK and authenticate if required
2. Select files from the folder

1. Click Combine & Transform Data

Step 6: Connecting to MySQL Database

1. Open Power BI Desktop
2. Navigate to Home → Get Data → MySQL Database

1. Enter the server name and database

1. Provide authentication credentials

1. Select the required tables
2. Click Load or Transform Data

Step 7: Connecting to SQL Server

1. Open Power BI Desktop
2. Navigate to Home → Get Data → SQL Server

1. Enter the server name (e.g., localhost)
2. Leave the database field blank (or specify one if needed)
3. Click OK

1. Select authentication method (e.g., Windows credentials)

1. In the Navigator pane, expand the database (e.g., AdventureWorksDW2020)
2. Select required tables such as: o DimEmployee o DimProduct o FactResellerSales
3. Click Transform Data to open Power Query Editor

Step 8: Connecting to Web Data

1. Open Power BI Desktop
2. Navigate to Home → Get Data → Web

1. Enter the URL of the web page or API
2. Click OK

1. Select the data table or structure detected
2. Click Load or Transform Data

Step 9: Connecting to Azure Analysis Services

1. Open Power BI Desktop
2. Navigate to Home → Get Data → Azure → Azure Analysis Services

1. Enter the server name
2. Select the database/model
3. Choose connection mode (Live connection recommended)

1. Click Connect