Mastering Power BI: Connect, Preview, and Transform Data from any Source

Curtis Mukumba — Sat, 11 Apr 2026 14:22:32 +0000

Connecting Data from Multiple Sources in Power BI: A Comprehensive Technical Guide

Introduction

In the world of data analytics, insights are only as good as the data that powers them. Often, organizations store critical business information across multiple platforms—spreadsheets, databases, APIs, PDFs, SharePoint, and cloud services. To build accurate, insightful Power BI reports, data analysts must seamlessly connect, consolidate, and profile data from all these sources.

This blog guides you through connecting nine different data sources in Power BI, from Excel files to Azure Analysis Services, using Power Query for previewing and profiling your data before building robust models. By following these steps, you'll gain confidence in handling diverse datasets and preparing them for meaningful analysis.

Architecture Overview

Power BI acts as the central hub in a modern data workflow. The architecture for data ingestion and preparation can be visualized as:

Data Sources → Power Query Editor → Data Model → Reports & Dashboards

Key components include:

Power BI Desktop: The development environment for data connections, modeling, and reporting.
Power Query Editor: A powerful interface for cleaning, transforming, and profiling data.
Data Sources: Excel, CSV, PDF, JSON, SharePoint folders, SQL Server, MySQL, web sources, and Azure Analysis Service.
Data Model: Once cleaned and transformed, data is loaded here for relationships, measures, and visualizations.

Each data source has its unique connection method and nuances. Let’s break them down with step-by-step instructions.

Connecting Data from Different Sources

First of all, launch Power BI Desktop: Open Power BI → Blank report

1. Excel

Option 1: Home → Excel Workbook/Import data from Excel
Option 2: Home → Get Data → Excel Workbook
Browse your file location → Select your Excel file → Click Open
Navigate to the dataset repository → Select the workbook → Click Open
Select relevant sheet/tables → Click Transform to launch Power Query
View tab (in Power Query) → Enable data quality previews → View data quality and manipulate → Monitor applied steps
Home tab (in Power Query) → click Close and apply (down arrow) → Click Close and Apply

2. Text/CSV

Home → Get Data → Text/CSV
Navigate to the dataset repository → Select the file → Click Open to open Power Query
Specify delimiter → Click transform button to launch Power Query
View → Enable data quality previews → View data quality and manipulate → Monitor applied steps
Home tab (in Power Query) → Close and apply down arrow → Close and Apply

3. PDF

Home → Get Data → More... → PDF → Connect
Navigate to the dataset repository → Select a pdf format file → Click Transform to launch Power Query
View → Enable data quality previews → View data quality and manipulate → Monitor applied steps
Home tab (in Power Query) → Close and apply down arrow → Close and Apply

4. JSON

Home → Get Data → More... → JSON→ Connect.
Navigate to the dataset repository → Select your JSON file or API export → Click Open to open Power Query.
Power Query automatically expands hierarchical structures. If it doesn't, go through the following steps.

- Home tab (in Power Query) → Identify columns to expand

Depending on the nature of the columns flatten nested records and transform data as required:
- Home tab (in Power Query) → Expand the columns (Opposite arrows) → Expand to new Rows → Monitor applied steps Or

- Home tab (in Power Query) → Expand the columns (Opposite arrows) → Select values → Disable Use original column name as prefix → Click Ok → Monitor applied steps

View tab (in Power Query) → Enable data quality previews → View data quality and manipulate → Monitor applied steps
Home tab (in Power Query) → click Close and apply (down arrow) → Click Close and Apply

5. SharePoint Folder

Home → Get Data → More... → SharePoint Folder → Connect.

Open your internet browser → copy your sharepoint site URL → Paste in the Power BI authentication prompt → Click Connect.

Authenticate to your SharePoint site within the prompted dialog → Click Connect.
Select relevant tables → Click Transform to launch Power Query.
View tab (in Power Query) → Enable data quality previews → View data quality and manipulate → Monitor applied steps.
Home tab (in Power Query) → click Close and apply (down arrow) → Click Close and Apply

6. SQL Server

Home → Get Data → SQL Server.
**Enter Server and Database names → Define connection mode (Import or DirectQuery) → Click Ok
Select Import or DirectQuery, then authenticate using your credentials.
Choose tables from the Navigator pane and click Transform Data.

7. Web

Home → Get Data → Web.

Copy the web URL or API endpoint → paste it on the From web dialog box and click OK.

Select relevant tables → Click Transform to launch Power Query.

View tab (in Power Query) → Enable data quality previews → View data quality and manipulate → Monitor applied steps.
Home tab (in Power Query) → click Close and apply (down arrow) → Click Close and Apply
![Image43](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zmg54jso5arnld1ujt1x.pn

9. Azure Analysis Service

Home → Get Data → More... → Azure → Azure Analysis Services database.

Enter the Server Name and select the Database.

3.Authenticate using organizational credentials.

Choose the desired measures and dimensions and click Load or Transform Data.

Key Takeaways

Power Query is your first line of defense: Always profile and preview data before loading into the model.
Column Quality, Distribution, and Profile highlight missing values, inconsistencies, and uniqueness.
Data sources vary in complexity: Flat files like CSV are straightforward, while hierarchical sources like JSON or PDF need transformation.
Relational databases (SQL Server, MySQL, Azure Analysis Services) require careful table selection to maintain relationships and avoid duplicates.
Combining multiple sources in a single Power BI report provides richer insights but demands attention to data quality and consistency.
Best practice: Document your transformations and track changes in Power Query for auditability.

Conclusion

Connecting to multiple data sources in Power BI is a fundamental skill for data analysts. Power BI’s flexibility allows seamless integration from Excel, CSV, JSON, PDF, SharePoint, web services, and enterprise databases like SQL Server, MySQL, and Azure Analysis Services.

By mastering these connections and leveraging Power Query profiling tools, you ensure your data is accurate, clean, and ready for modeling. Proper data ingestion not only strengthens your reports but also builds trust in the insights you deliver.

With this knowledge, analysts can confidently consolidate diverse data sources into a single, unified Power BI model, unlocking comprehensive, actionable intelligence across the organization.

DEV Community: Curtis Mukumba