DEV Community: Temitope Hassan

Clean, Transform, and Load Data in Power BI: A Beginner-Friendly Guide

Temitope Hassan — Mon, 11 May 2026 07:45:24 +0000

Introduction
Data is only as powerful as the shape it’s in. Before dashboards tell compelling stories and visuals drive decisions, data must first be cleaned, structured, and trusted. In Power BI, this transformation happens in Power Query, the engine that converts raw, messy data into meaningful insights.

If you’re new to Power BI, one of the most important skills you can learn is data preparation. No matter how impressive your visuals look, poor-quality data will always produce poor insights. That’s why cleaning and transforming data is a critical step in every Power BI project.

Power Query enables you to clean, reshape, and prepare data before loading it into the data model. With it, you can:

Fix inconsistencies and errors
Handle missing or null values
Remove duplicate records
Reshape tables
Merge or append datasets
Apply user-friendly naming conventions
Create a clean and reliable data model for reporting

In this beginner-friendly guide, you’ll learn step-by-step how to clean, transform, and load data in Power BI using a practical hands-on dataset.

What You Will Learn

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

Resolve inconsistencies and data quality issues
Remove duplicate records
Remove or replace null values
Apply meaningful value replacements
Profile data to assess column quality
Evaluate and transform column data types
Reshape tables using Pivot and Unpivot operations
Combine and merge queries
Apply clear and user-friendly naming conventions
Edit transformations using the Advanced Editor (M Code)

*Opening Power Query Editor
*
In Power BI Desktop:

Go to the Home tab
Select Transform Data

This opens the Power Query Editor, where all data cleaning and transformation tasks are performed.
Load a clean and reliable data model into Power BI

Identify Column headers and names
The first step in shaping your data is to identify the column headers and names, then verify their locations to ensure they are in the right place.

In the following screenshot, the source data in the CSV file for Product_Sales had a target categorized by productKey, product sub-category key, unit cost, and unit price, which are organized into columns, but are not in the right place.

Once you have identified where the column headers and names are located, you can reorganize the data.

The image illustrates how the Use First Row as Headers feature impacts the data

To correct this inaccuracy, you need to remove some of the top rows as they contain data we do not need in this report, and promote the first table row into column headers.

You can promote headers in two ways: by selecting the Use First Row as Headers option on the Home tab or by selecting the drop-down button next to Column1 and then selecting Use First Row as Headers.

Rename Column
The next step in shaping your data is to examine the column headers. You might discover that one or more columns have the wrong headers, a header has a spelling error, or the header naming convention is not consistent or user-friendly.

You can rename column headers in two ways. One approach is to right-click the header, select Rename, edit the name, and then press Enter. Alternatively, you can double-click the column header and overwrite the name with the correct name.

Removing Duplicates in Power Query

To remove duplicate records:

Select the column(s) that should contain unique values
Right-click the selected column
Choose Remove Duplicates

Power Query keeps the first occurrence and removes the rest.

Removing or Replacing Null Values

To remove null values:

Open the column filter dropdown
Uncheck null

You can also replace null values:

Right-click the column
Select Replace Values
Enter an appropriate replacement value

Note:
Power Query treats null as a distinct value type. In some scenarios, you may also use Replace Errors or transformation options to handle missing data effectively.

*Profiling Data to Understand Column Quality
*
From the View tab, enable:

Column Quality
Column Distribution
Column Profile

These profiling tools help you identify:

Empty values
Errors
Duplicate values
Value distribution issues before analysis begins.

Reshaping Data with Pivot and Unpivot

Reshaping data is an essential transformation skill in Power Query.

Unpivot converts columns into rows
Pivot converts rows into columns This is especially useful when working with monthly or repeated values.

Merging and Appending Queries

Merging Queries (JOIN) Combines tables based on a common column (like SQL JOIN).

Home → Merge Queries
Choose join type:
Inner
Left Outer
Right Outer

Appending Queries (UNION)
Stack tables vertically.
Home → Append Queries
Example:
Combine:

2019 sales
2020 sales

Conclusion

Cleaning and transforming data is the foundation of effective Power BI analysis. Power Query provides powerful tools to remove duplicates, handle missing values, profile data quality, reshape tables, and build reliable data models for reporting.

Mastering these skills will help you create dashboards that are not only visually appealing but also accurate, efficient, and professional.

Getting Data from Multiple Sources in Power BI: A Practical Guide to Modern Data Integration for Analysts

Temitope Hassan — Tue, 31 Mar 2026 09:20:57 +0000

Introduction

The foundation of every successful Power BI report is reliable data ingestion. No matter how visually stunning your dashboards are, if the data behind them is incomplete, inconsistent, or poorly structured, the insights they produce will be misleading at best and dangerous at worst.
In real-world business scenarios, data rarely lives in a single location. As a Data Analyst, you will frequently work with Excel files from finance, CSVs from operations, SQL databases from IT, APIs returning JSON, PDFs containing key tables, and SharePoint folders shared across departments, all within the same Power BI report.

Power BI is purposely built for this reality. Its powerful Get Data interface and Power Query transformation engine allow you to connect to virtually any data source, inspect its quality, and shape it before it ever reaches your data model.

In this blog, you will learn how to:

Connect Power BI Desktop to different data source types.
Use Power Query to preview, profile, and explore data.
Identify and resolve data quality issues before they corrupt your model.
build a scalable, multi-source foundation for accurate reporting and analytics.

Architecture Overview

Before connecting to any data source, it helps to understand how Power BI processes data end-to-end. The diagram below represents the layered architecture that governs how data moves from its origin to your final dashboard.

Layer	Component	Purpose
Source Layer	Excel, CSV, PDF, JSON, SharePoint, SQL, Azure	Raw data origins
Ingestion Layer	Power BI Get Data	Connection & authentication
Transformation Layer	Power Query Editor	Cleansing, shaping & merging
Modelling Layer	Power BI Data Model	Relationships, measures & KPIs
Presentation Layer	Reports & Dashboards	Insights for stakeholders

At the core of this architecture is Power Query, the engine that sits between your raw data sources and your data model. Every connection you make in Power BI flows through Power Query, where transformations are recorded as steps and applied automatically on each refresh. This means your data preparation logic is transparent, repeatable, and version-aware.

Connecting to Data from Multiple Sources

Power BI supports hundreds of data connectors organized into categories: File, Database, Power Platform, Azure, Online Services, and Others. In this section, we walk through the most commonly used connectors with clear, step-by-step instructions.

1. Excel
Excel is the most common data source in business environments. Power BI can connect to Excel workbooks and import data from named tables, named ranges, or individual worksheets.

Open Power BI Desktop and navigate to Home → Get Data → Excel Workbook.
Browse your file system and select the target .xlsx or .xls file.
The Navigator window opens, displaying all available sheets and tables.
Select the sheet(s) or named table(s) you want to import.
Click Load to import directly, or Transform Data to open Power Query for pre-load cleaning.

2. Text / CSV files
CSV files are ubiquitous in data workflows. They are frequently exported from ERP systems, CRMs, and operational databases. Power BI handles them natively with automatic delimiter detection.

Steps to Connect

Navigate to Home → Get Data → Text/CSV.
Browse and select your .csv or .txt file.
Power BI auto-detects the delimiter (comma, tab, semicolon) and displays a preview.
Verify that columns are correctly split and data types are detected.
Click Load or Transform Data to proceed.

3. PDF
Power BI can extract tabular data embedded in PDF documents, a common requirement when working with published financial reports, government data releases, or supplier price lists.

Steps to Connect

Navigate to Home → Get Data → PDF.
Browse and select the target PDF file.
Power BI scans the document and attempts to detect table structures by page.
In the Navigator, you will see tables labelled by page (e.g., Table001 (Page 1)).
Select the relevant table(s) and click Transform Data to review before loading.

4. SharePoint Folder

Many organizations store operational files — weekly reports, regional submissions, survey exports — in SharePoint. Power BI's SharePoint Folder connector automatically combines all matching files in a folder into a single unified dataset.
Steps to Connect

Navigate to Home → Get Data → SharePoint Folder.

Enter the root SharePoint site URL (e.g., https://yourcompany.sharepoint.com/sites/analytics).
Click OK and sign in with your Microsoft 365 credentials if prompted.
Power BI lists all files in the SharePoint library. Filter by folder path or file extension as needed.
Click Combine & Transform Data to merge files of the same structure into one table.
Power Query creates a combination query with a sample file for schema definition. Validate and apply.

5. JSON
JSON files are commonly generated by APIs and web-based applications.

Steps to connect:

Home → Get Data → JSON
Power Query expands nested structures
Flatten and transform fields as needed

JSON often requires extra transformation because of its hierarchical format.

6. SQL Server

SQL Server is one of the most widely used enterprise databases and one of Power BI's most mature connectors. Whether running on-premises or in Azure (as Azure SQL Database), the connection process is nearly identical.
Steps to Connect

Navigate to Home → Get Data → SQL Server.
Enter the server name (e.g., localhost, 192.168.1.10, or yourserver.database.windows.net for Azure SQL).
Optionally enter the database name, or leave blank to browse all databases on the server.
Click OK and select your authentication method: Windows, Database, or Microsoft Account.
In the Navigator pane, expand the database (e.g., AdventureWorksDW2022).
Select the required tables — for example, RetailSales
Click Transform Data to review the data in Power Query before loading.

7. Azure Analysis Services
Azure Analysis Services (AAS) is an enterprise-grade analytical modeling platform that hosts pre-built semantic models in the cloud. Connecting Power BI to AAS provides a live connection to curated, governed data models, ideal for large organizations where data modeling is centralized.

Steps to Connect

Navigate to Home → Get Data → Azure → Azure Analysis Services Database -> Connect

Enter the fully qualified server name (e.g., asazure://westeurope.asazure.windows.net/yourmodel).
Authenticate using your Azure Active Directory credentials.
Select the database/model from the available list.
Choose Connect live (recommended) to query the model directly without importing data.

Click Connect, and Power BI loads the model's tables, measures, and hierarchies immediately.

Conclusion

Connecting to multiple data sources in Power BI is more than a technical configuration step, it is the foundational skill that determines the quality, reliability, and credibility of everything you build on top of it.
As you have seen across this guide, modern analytics environments are inherently diverse. Data lives in Excel workbooks, relational databases, cloud services, SharePoint libraries, JSON APIs, and PDF documents. Power BI's Get Data framework, combined with the transformative power of Power Query, gives you a unified, repeatable approach to connecting and preparing all of it.

But the real value is not in the connections themselves. It is in what happens when you treat data ingestion as a deliberate discipline:

You surface data quality issues before they corrupt your reports
You document your transformation logic in a transparent, auditable way
You build data models that are accurate, performant, and easy to maintain
You earn the trust of stakeholders by delivering insights they can rely on

The connectors covered in this blog: Excel, Text/CSV, PDF, JSON, SharePoint Folder, and SQL Server, represent the most common patterns in real-world analytics work. Mastering these will prepare you to handle the vast majority of data integration challenges you will encounter as a professional Data Analyst.

As you grow in your Power BI practice, continue exploring advanced capabilities: incremental refresh for large tables, query folding for performance optimization, dataflows for reusable data preparation, and composite models for combining DirectQuery and import sources.