DEV Community

Cover image for Getting Data from Multiple Sources in Power BI
Oladosu Ibrahim
Oladosu Ibrahim

Posted on

Getting Data from Multiple Sources in Power BI

#data #analyst

Introduction

One of the most important skills every Data Analyst must master is the ability to connect Power BI to multiple data sources, understand the data structure, and assess data quality before building reports.

In this article, I’ll walk you through how to get data from multiple sources in Power BI Desktop, preview the data using Power Query, and use data profiling features to quickly identify data quality issues.

This guide is practical and mirrors real-world Power BI projects.

What You Will Learn

By the end of this guide, you will be able to:

  • Open Power BI Desktop
  • Connect to SQL Server databases
  • Import data from CSV files
  • Preview and understand data using Power Query Editor
  • Use Column Quality, Column Distribution, and Column Profile features
  • Identify common data quality issues before modeling

Getting Started with Power BI Desktop

To begin, download the starter files used in this lab from Microsoft’s official repository:

🔗 Download link:
https://github.com/MicrosoftLearning/PL-300-Microsoft-Power-BI-Data-Analyst/raw/Main/Allfiles/Labs/01-get-data-in-power-bi/01-get-data.zip

After downloading:

  1. Extract the folder to C:\Users\Student\Downloads\01-get-data
  2. Open the 01-Starter-Sales Analysis.pbix file in Power BI Desktop

This starter file has been preconfigured to disable automatic relationship detection, allowing us to focus strictly on data loading and profiling.

Getting Data from Microsoft Excel

In real business environments, most enterprise data lives in databases. Let’s connect Power BI to SQL Server.

Steps:

  1. In Power BI Desktop, go to Home → Get Data → More
    Image1

  2. Select Microsoft Excel and Connect.
    Image2

  3. In the Server field, enter:
    localhost
    (Leave the Database field blank)

  4. Use Windows authentication if prompted

  5. Ignore the encryption warning and continue

Once connected, expand the AdventureWorksDW2020 database.

Select the following tables:

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

Click Transform Data to open Power Query Editor.

You have now connected Power BI to six tables from SQL Server.

Previewing and Understanding Data in Power Query

Power Query allows you to inspect your data before loading it into the data model.

Exploring the Queries Pane

On the left side, you’ll see one query per table. Each query represents a connection to a data source.

Reviewing the DimEmployee Table

The DimEmployee table stores employee details.

Key observations:

  • The table contains 296 rows and 33 columns
  • Some columns contain Table or Value links, which represent relationships
  • These columns can later be used to merge or relate tables

Using Data Profiling Features in Power Query

Data profiling helps you quickly assess data quality and structure.

Column Quality

Enable:

  • View → Column Quality

This shows:

  • Valid values
  • Empty (null) values
  • Errors

Example insight:

  • The Position column contains 94% empty values, which is a potential data quality issue.

Column Distribution

Enable:

  • View → Column Distribution

This helps you understand:

  • Distinct values
  • Unique values

Example:

  • EmployeeKey has 296 distinct and unique values This makes it a good primary key for relationships.

Column Profile

Enable:

  • View → Column Profile

In the DimReseller table:

  • The BusinessType column shows a data issue

  • “Warehouse” appears twice as:

    • Warehouse
    • Ware House (misspelled)

This kind of inconsistency must be cleaned before analysis.

Reviewing the Fact Table

The FactResellerSales table stores transaction-level sales data.

Key insight:

  • The TotalProductCost column has 8% missing values
  • Missing cost data can affect profit calculations and business decisions

Identifying this early prevents inaccurate reporting later.

Getting Data from CSV Files

Not all data comes from databases. Analysts often work with flat files like CSVs.

Importing a CSV File

  1. In Power Query Editor:
  • Home → New Source → Text/CSV
    1. Select ResellerSalesTargets.csv
    2. Review the preview and click OK

This file contains:

  • One row per salesperson per year
  • Monthly sales targets
  • No empty values (hyphens are used instead)

Importing Another CSV File

Repeat the same steps to import ColorFormats.csv.

This file contains:

  • Product colors
  • HEX codes for background and font formatting

At this point, you should have two new CSV-based queries added to your model.

Why This Step Matters in Real Projects

Before building visuals and dashboards, every Data Analyst must:

  • Understand the structure of the data
  • Identify missing values and inconsistencies
  • Confirm unique keys for relationships
  • Know which columns need cleaning or transformation

Skipping this step often leads to:

  • Wrong insights
  • Broken relationships
  • Misleading dashboards

Final Thoughts

Getting data from multiple sources in Power BI is more than just clicking Get Data.
It’s about understanding your data before modeling and visualization.

Power Query gives you powerful tools to:

  • Inspect data quality
  • Detect issues early
  • Build reliable and trustworthy reports

Mastering this step puts you on the right path to becoming a confident Power BI Data Analyst.

Top comments (0)