Getting Data from Multiple Sources in Power BI

Introduction

Reliable data is the backbone of any Power BI report. Stunning visuals mean little if the data is messy or incompleten leading to flawed insights. As a data analyst handling real-world projects (from field supervision to dashboards), I've pulled data from Excel logs, SQL databases, CSVs from surveys, and even SharePoint folders. Power BI shines here with its Get Data and Power Query tools.

In this guide, you will learn how to:

1. onnect Power BI to multiple data sources efficiently
2. Use Power Query to preview and explore your data
3. Detect and resolve data quality issues early
4. 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:

1. Excel
2. Text/CSV files
3. SQL Server databases
4. JSON
5. 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

2.Navigate to Home → Get Data → Excel

3.Browse and select your Excel file

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

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

3.Browse and select the CSV file (e.g., menu_item.csv)

4.Preview the dataset in the dialog window

5.Click Load or Transform Data

Step 3: Connecting to PDF
1.Open Power BI Desktop

2.Navigate to Home → Get Data → PDF

3.Select the PDF file

4.Wait for Power BI to detect available tables

5.Select the desired table(s)

6.Click Load or Transform Data

Step 4: Connecting to JSON
1.Open Power BI Desktop

2.Navigate to Home → Get Data → JSON

3.Select the JSON file or input API endpoint

4.Load and expand nested fields to structure the data properly

6.Click Close & Apply