DEV Community

Cover image for Mastering Power BI: Connect, Preview, and Transform Data from any Source
Curtis Mukumba
Curtis Mukumba

Posted on

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

#analytics #data #microsoft #tutorial

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
Image1

1. Excel

  1. Option 1: Home → Excel Workbook/Import data from Excel
    Image2

  2. Option 2: Home → Get Data → Excel Workbook
    Image3

  3. Browse your file location → Select your Excel file → Click Open
    Image4

  4. Navigate to the dataset repository → Select the workbook → Click Open
    Image5

  5. Select relevant sheet/tables → Click Transform to launch Power Query
    Image6

  6. View tab (in Power Query) → Enable data quality previews → View data quality and manipulate → Monitor applied steps
    Image7

  7. Home tab (in Power Query) → click Close and apply (down arrow) → Click Close and Apply
    Image8

2. Text/CSV

  1. Home → Get Data → Text/CSV
    Image9

  2. Navigate to the dataset repository → Select the file → Click Open to open Power Query
    Image10

  3. Specify delimiter → Click transform button to launch Power Query
    Image11

  4. View → Enable data quality previews → View data quality and manipulate → Monitor applied steps
    Image12

  5. Home tab (in Power Query) → Close and apply down arrow → Close and Apply
    Image13

3. PDF

  1. Home → Get Data → More... → PDF → Connect
    Image14 Image15

  2. Navigate to the dataset repository → Select a pdf format file → Click Transform to launch Power Query
    Image16

  3. View → Enable data quality previews → View data quality and manipulate → Monitor applied steps
    Image17

  4. Home tab (in Power Query) → Close and apply down arrow → Close and Apply
    Image18

4. JSON

  1. Home → Get Data → More... → JSON→ Connect.
    Image19 Image20

  2. Navigate to the dataset repository → Select your JSON file or API export → Click Open to open Power Query.
    Image20

  3. Power Query automatically expands hierarchical structures. If it doesn't, go through the following steps.

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

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
Image22 Image23

  1. View tab (in Power Query) → Enable data quality previews → View data quality and manipulate → Monitor applied steps
    Image24

  2. Home tab (in Power Query) → click Close and apply (down arrow) → Click Close and Apply
    Image25

5. SharePoint Folder

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

Image26
Image27

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

Image28
Image29

  1. Authenticate to your SharePoint site within the prompted dialog → Click Connect.
    Image30

  2. Select relevant tables → Click Transform to launch Power Query.
    Image31

  3. View tab (in Power Query) → Enable data quality previews → View data quality and manipulate → Monitor applied steps.
    Image32

  4. Home tab (in Power Query) → click Close and apply (down arrow) → Click Close and Apply
    Image33

6. SQL Server

  1. Home → Get Data → SQL Server.
    Image34

  2. **Enter Server and Database names → Define connection mode (Import or DirectQuery) → Click Ok
    Image35

  3. Select Import or DirectQuery, then authenticate using your credentials.
    Image36

  4. Choose tables from the Navigator pane and click Transform Data.

Image37

7. Web

  1. Home → Get Data → Web.

Image38

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

Image39 Image40

  1. Select relevant tables → Click Transform to launch Power Query.

Image41

  1. View tab (in Power Query) → Enable data quality previews → View data quality and manipulate → Monitor applied steps.
    Image42

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

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

Image44

Image45

  1. Enter the Server Name and select the Database.

Image46

3.Authenticate using organizational credentials.

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

Key Takeaways

  1. Power Query is your first line of defense: Always profile and preview data before loading into the model.
  2. Column Quality, Distribution, and Profile highlight missing values, inconsistencies, and uniqueness.
  3. Data sources vary in complexity: Flat files like CSV are straightforward, while hierarchical sources like JSON or PDF need transformation.
  4. Relational databases (SQL Server, MySQL, Azure Analysis Services) require careful table selection to maintain relationships and avoid duplicates.
  5. Combining multiple sources in a single Power BI report provides richer insights but demands attention to data quality and consistency.
  6. 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.

Top comments (0)