DEV Community: Ibrahim Abdulrasaq

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

Ibrahim Abdulrasaq — Sun, 25 Jan 2026 10:41:24 +0000

Data is only as powerful as the shape it’s in. Before charts impress and dashboards tell stories, data must be cleaned, structured, and trusted. In Power BI, that transformation happens in Power Query, the engine that turns raw, messy data into meaningful insights.

If you are new to Power BI, one of the most important skills you need to learn is data preparation. No matter how good your visuals look, poor data quality will always lead to poor insights. This is why cleaning and transforming data is a critical step in any Power BI project.

Power Query is the engine in Power BI that allows you to clean, transform, and prepare your data before it is loaded into the data model. With Power Query, you can fix inconsistencies, handle missing values, reshape tables, combine data from different sources, and apply user-friendly naming conventions that make your model easy to understand.

In this beginner-friendly guide, I will walk you step by step through 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, unexpected values, and data quality issues
Remove duplicate records
Remove or replace null values
Apply user-friendly value replacements
Profile data to understand 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)
Load a clean and reliable data model into Power BI

Getting Started: Download the Practice Files

To follow along with this guide:

Download the dataset from the link below:

https://github.com/MicrosoftLearning/PL-300-Microsoft-Power-BI-Data-Analyst/raw/Main/Allfiles/Labs/02-transform-data-power-bi/02-transform-data.zip

Extract the zip file to:

C:\Users\Downloads\02-transform-data

C:\This PC\Downloads\02-transform-data

Open the file 02-Starter-Sales Analysis.pbix

Note: If a sign-in dialog appears, select Cancel. Close any informational windows and select Apply Later if prompted.

Opening Power Query Editor

In Power BI Desktop, go to the Home tab and select Transform Data.

This opens the Power Query Editor, where all data cleaning and transformation tasks are performed.

Let's start with how to configure a table/query

This section will walk you through how to:

Rename query/table
Locate column
Filter
Merge columns
Delimiter Etc.

Configuring the Salesperson Query

Select the DimEmployee query and rename it to Salesperson. Query names become table names in the data model.
Note: Always use clear and meaningful names.

Use Go to Column to locate the SalesPersonFlag column and filter it to TRUE so that only salespeople are included.

Remove all unnecessary columns and keep only:

EmployeeKey
EmployeeNationalIDAlternateKey
FirstName
LastName
Title
EmailAddress

Merge the FirstName and LastName columns using a space as the separator and name the new column Salesperson.

Rename EmployeeNationalIDAlternateKey to EmployeeID and EmailAddress to UPN.

Configuring the SalespersonRegion query

Select the DimEmployeeSalesTerritory query and rename it to SalespersonRegion.

Remove the DimEmployee and DimSalesTerritory columns.

Configuring the Product Query

Select the DimProduct query and rename it to Product.

Filter the FinishedGoodsFlag column to TRUE so that only finished goods are included.

Remove all columns except:

ProductKey
EnglishProductName
StandardCost
Color
DimProductSubcategory

Expand the DimProductSubcategory column and include:

EnglishProductSubcategoryName
DimProductCategory

Note: Ensure the option to use original column names as prefixes is unchecked.

Having followed up to this stage. You should now be able to configure the Reseller, Region, Sales, and Targets queries.

Note: In this context, a query is the same as a table in the data model.

Now, let's move to other aspects of data cleaning and transformation.

Removing Duplicates in Power Query

To remove duplicate records, select the column or columns that should be unique, right-click, and 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 and uncheck null.

You can also replace null values. To do this, right-click the column, select Replace Values, and enter an appropriate value to replace the nulls.

A few notes:

Power Query distinguishes null specifically, so sometimes instead of “Replace Values,” you might also use "Replace Errors or Transform" to Replace Values but for nulls, “Replace Values” works fine.
Emphasizing “enter an appropriate value to replace the nulls” makes it clearer that nulls are being handled.

Profiling Data to Understand Column Quality

Enable Column Quality, Column Distribution, and Column Profile from the View tab.

These tools help identify empty values, duplicates, and value distribution issues before analysis.

Reshaping Data with Pivot and Unpivot

Unpivot converts columns into rows and is useful for monthly or repeated values. Pivot converts rows into columns when categories should appear as separate fields.

This section introduces unpivoting, a crucial transformation.

Key Steps:

Rename ResellerSalesTargets to Targets

Unpivot monthly columns (M01–M12).

Apply correct data types

Merging Queries and Disabling Load

Use Merge Queries to combine related tables. After merging ColorFormats into Product, disable loading for ColorFormats to keep the data model clean.

Steps:

Select ColorFormats query/table

Right-click the top-left corner of your data table (above the first row header, to the left of the first column header). Select "Use First Row as Headers" from the context menu.

This action will automatically rename the existing column headers to the values present in the first row and promote that row's data to be the official headers for your table.

Merge Product with ColorFormats

Use Left Outer Join

Since ColorFormats is only used for merging:

Disable Enable Load for the ColorFormats query.

This keeps your model clean and optimized.

You can also follow a similar approach to append queries.

Here is a quick explanation of the difference between the two:

Merge Query: Combines tables horizontally using a common column (adds new columns).

Similar to SQL JOIN

Append Query: Combines tables vertically by stacking rows (adds new rows).

Similar to SQL UNION

Simple Rule to Remember
Merge = JOIN → add columns
Append = UNION → add rows

Using the Advanced Editor

The Advanced Editor allows you to view and edit the M code behind each transformation. While optional for beginners, it provides deeper insight and control over Power Query logic.

Loading the Data and Saving the Report

Select Close and Apply to load all queries. Confirm that seven tables are loaded.

Save the report using File > Save As, apply pending changes if prompted, and close Power BI Desktop.

Summary

In this guide, you learned how to clean, transform, and prepare data in Power BI using Power Query, from resolving data quality issues and reshaping tables to merging queries and building a reliable data model ready for analysis. By now, you’re all set to tackle your own datasets and start uncovering meaningful insights!

Final Thought

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, reshape tables, and prepare a reliable data model for reporting. Mastering these skills will make your dashboards more accurate, efficient, and professional.

Getting Data from Multiple Sources in Power BI: A Complete Beginner-Friendly Guide

Ibrahim Abdulrasaq — Wed, 31 Dec 2025 09:23:51 +0000

Introduction

The foundation of every successful Power BI report is reliable data ingestion. No matter how visually appealing your dashboards are, if the underlying data is incomplete, inconsistent, or poorly understood, the insights will be misleading.

In real-world business environments, data rarely comes from a single source. As a Data Analyst, you may need to work with:

Excel files
CSV text files
SQL Server databases
JSON APIs
PDF reports
SharePoint folders

All within the same project.

Power BI is designed to handle this complexity through its powerful Get Data and Power Query capabilities.

In this blog, you’ll learn how to connect to multiple data sources in Power BI, preview the data, and assess its quality before building your data model. By the end, you’ll be confident working with diverse data sources and preparing them for meaningful analysis.

High-Level Overview of Power BI Data Architecture

In this workflow, Power BI operates as the central hub where data from multiple sources is brought together and prepared for analysis.

Our architecture consists of:

Power BI Desktop → reporting, modeling, and development environment
Multiple data sources, such as:
- Excel and Text/CSV files
- SQL Server databases
- JSON and PDF files
- SharePoint folders
Power Query Editor → for cleaning, transforming, and profiling data

All data flows into Power BI through Power Query, where it is reviewed and prepared before loading into the data model.

What You’ll Accomplish in This Guide

In this step-by-step walkthrough, you will:

Open and configure Power BI Desktop
Connect to data from Excel, CSV, Database, SQL Server, JSON, PDF, and SharePoint
Preview and understand source data using Power Query
Use Column Quality, Column Distribution, and Column Profile
Identify common data quality issues early
Prepare datasets for modeling and reporting

Getting Started with Power BI Desktop

To practice along with this guide, first download the practice files from the link below:

🔗 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:

Extract the folder.
Open 01-Starter-Sales Analysis.pbix in Power BI Desktop.

This starter file disables automatic relationship detection so you can focus specifically on data ingestion and profiling.

Getting Data from SQL Server

Enterprise-level data is often stored in relational databases. Power BI connects easily to SQL Server.

Steps to connect:

Go to Home → Get Data → SQL Server

Enter:
- Server: localhost
- Database: leave blank

Select Windows Authentication (select Windows > Use my current credentials, and then Connect).

Select OK if you receive a warning that an encrypted connection cannot be established.

In the Navigator pane. Expand the AdventureWorksDW2020 database

Select the following tables:
- DimEmployee
- DimEmployeeSalesTerritory
- DimProduct
- DimReseller
- DimSalesTerritory
- FactResellerSales

Click Transform Data

Power Query Editor opens with six queries loaded from SQL Server.

Previewing Data in Power Query Editor

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

Queries Pane

Each table appears as a separate query on the left. Selecting a query displays a preview of its contents.

Dimension Tables (Dim)

Examples:

DimEmployee:one row per employee

DimProduct:one row per product

DimReseller:one row per reseller

DimSalesTerritory:regions, countries, and groups

Fact Tables (Fact)

FactResellerSales: one row per sales order line

Understanding the difference between fact and dimension tables is essential for proper star-schema data modeling in Power BI.

Using Power Query Data Profiling Features

Power Query includes built-in tools to help assess data quality before modeling.

Column Quality

Enable:

View → Column Quality

This reveals:

Percentage of valid values
Empty (null) values
Errors

Example insight:

The Position column in DimEmployee contains 94% empty values, signaling a potential data quality issue.

Column Distribution

Enable:

View → Column Distribution

You can now see:

Number of distinct values
Number of unique values

Example:

EmployeeKey shows the same distinct and unique count → meaning every row is unique (useful when creating keys and relationships).

Column Profile

Enable:

View → Column Profile

Then select a column, such as BusinessType in DimReseller.

You may notice inconsistent labels:

“Warehouse”
“Ware House” (misspelled)

This inconsistency must be corrected before analysis to prevent inaccurate grouping or reporting errors.

Getting Data from Text/CSV Files

Flat files are extremely common in reporting workflows.

Importing a CSV file

Step 1: Home → Get data → Text/CSV

Step 2: Select ResellerSalesTargets.csv

This file contains:

One row per salesperson per year
Monthly sales targets
Hyphens instead of null values

Repeat the process to import "ColorFormats.csv", which contains color formatting values.

Getting Data from Excel Files

Excel remains one of the most widely used business data tools.

To import Excel data:

Step 1: Home → Get Data → Excel

Step 2: Select the Excel file

Step 3: Then click Transform Data
Excel files are ideal for:

Budgeting and finance sheets
Manual business inputs
Operational logs and trackers

Getting Data from JSON Files

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

Steps:

Step 1: Home → Get Data → JSON

Step 2: Select the JSON file or API export

Step 3: Power Query expands nested structures
Step 4: Flatten and transform fields as needed

JSON often requires extra transformation because of its hierarchical format.

Getting Data from PDF Files

Power BI can extract structured tables from PDF documents.

Steps:

Step 1: Home → Get Data → PDF

Step 2: Select the PDF file
Step 3: Choose detected tables
Step 4: Transform in Power Query

Useful for:

Financial statements
Bank reports
Compliance or regulatory documents

Getting Data from SharePoint Folders

SharePoint is widely used for collaborative file storage across organizations.

Steps:

Step 1: Home → Get Data → SharePoint Folder

Step 2: Enter the SharePoint site URL and authenticate

Step 3: Filter and combine files as needed

This approach is ideal when working with "multiple files stored in a shared location".

Why Data Profiling Matters

Before building dashboards, you must:

Identify missing values
Detect inconsistent labels
Validate key columns for relationships
Understand value distributions

Skipping this step can lead to:

Broken relationships
Incorrect KPIs
Misleading insights

Power Query ensures your data is 'accurate, reliable, and business-ready before visualization.

Conclusion

Getting data from multiple sources is a core skill for every Power BI data analyst. Power BI makes this process seamless by supporting a wide range of data connectors and providing powerful tools to preview and profile data before modeling.

By combining SQL Server, Excel, CSV, JSON, PDF, and SharePoint data in Power BI, you can build comprehensive, enterprise-ready reports with confidence.

Mastering this step ensures your dashboards are not only visually appealing but also accurate, trustworthy, and truly impactful.

SQL for Data Analytics: A Must Read for Beginner Data Analysts Before Diving Into SQL

Ibrahim Abdulrasaq — Tue, 30 Dec 2025 11:09:37 +0000

SQL (Structured Query Language) is one of the most important tools for every data analyst. It allows you to retrieve, filter, combine, and analyze data stored in databases. But before jumping straight into writing SQL queries, it is essential to first build a strong foundation in core data concepts, analytical thinking, and basic statistics. These skills give meaning to the results you obtain with SQL and help you solve real world problems more effectively.

Before diving into SQL, every beginner data analyst should understand key data concepts, basic statistics, and develop a strong analytical mindset. These foundations provide context and enable effective problem solving once you start working with data using SQL.

Fundamental Concepts to Understand Before Learning SQL

(1) Relational Databases

SQL is used to communicate with relational databases, systems that store data in an organized, structured format using tables. In most organizations, data is spread across multiple related tables rather than one large file, and SQL helps analysts retrieve and connect this information efficiently.

Understanding how relational databases work is essential because SQL is not just about writing queries, it is about understanding the data structure you are working with.

(2) Database Structure

Before writing SQL queries, a beginner analyst should understand the key components of a database:

-Tables store data in rows and columns, similar to an Excel sheet

-Rows or Records represent a single entry such as one customer or one transaction

-Columns or Fields represent attributes such as name, price, date, or product

-Primary Keys are unique identifiers for each row such as Customer ID or Transaction ID

-Foreign Keys link one table to another, creating relationships between datasets

These concepts make it easier to understand how JOINs work in SQL and how different tables connect in real world databases.

(3) Data Types

Every column in a database has a specific data type, which determines the kind of values it can store. Common examples include:

-VARCHAR for text values
-INT for whole numbers
-FLOAT for decimal numbers
-DATE for date values

Data types affect calculations, storage, filtering, and data integrity. Using the wrong data type can lead to errors or inaccurate results.

(4) NULL Values

NULL does not mean zero or empty. It represents missing or unknown data. In SQL, NULL behaves differently and must be handled carefully.

For example:

You cannot filter NULL values using the equals operator.
Instead, you must use IS NULL or IS NOT NULL

A good analyst understands what NULL means and how it affects averages, aggregations, and analysis outcomes.

Essential Skills Beyond SQL

Learning SQL alone is not enough to become a strong data analyst. The following complementary skills make your SQL knowledge more meaningful and practical.

(1) Analytical Mindset and Problem Solving

Data analysis is not just about running queries. It is about asking the right questions and interpreting results meaningfully.

A good analyst:

-Looks for trends, patterns, and anomalies

-Understands what the data is really saying

-Translates numbers into insights and decisions

SQL retrieves data. Your analytical mindset gives it meaning.

(2) Basic Statistics

Before analyzing data using SQL, beginners should understand key statistical concepts such as:

-Mean, median, and mode
-Variance and standard deviation
-Correlation and distribution patterns

Statistics help you:

-Interpret query results
-Avoid misleading conclusions
-Support insights with evidence

Without statistics, SQL results are just numbers, not insights.

(3) Excel Proficiency

Excel is one of the best starting tools for beginner analysts. Many data manipulation concepts in Excel translate naturally to SQL.

For example:

-Sorting and filtering work like SQL ORDER BY and WHERE

-Pivot tables are similar to SQL aggregations

-VLOOKUP works in a similar way to SQL JOIN operations

A strong Excel foundation makes learning SQL faster and more intuitive.

(4) Data Cleaning Concepts

In the real world, data is rarely clean. Before analysis, an analyst must know how to:

-Identify missing values
-Remove duplicates
-Fix inconsistent formats
-Standardize data

SQL is often used for data cleaning tasks, so understanding these concepts helps you write more meaningful and organized queries.

(5)Business Understanding

Beyond technical skills, a great data analyst understands the business context behind the data.

This helps you:

-Ask relevant questions
-Focus on metrics that matter
-Provide insights that support decision making

Without business understanding, analysis becomes just reporting, not problem solving.

Understanding SQL Through a Simple Analogy: Imagine SQL as a Detective Solving a Case 🕵️‍♂️🔎

Imagine SQL as a brilliant detective, not chasing criminals, but uncovering insights hidden inside data.

Every dataset is a crime scene.
Every table is a file of evidence.
Every query is a step toward solving the mystery.

Here is a simple illustration of how SQL works like a detective, using basic SQL commands:

SELECT: Gathering Clues
A detective focuses only on useful evidence. SELECT helps us pick the exact columns we need.

↪️ Command illustration:

SELECT name, salary
FROM employees;

We extract only the clues that matter.

WHERE: Filtering Suspects
A detective narrows down suspects based on facts. WHERE does the same.

↪️ Command illustration:

SELECT *
FROM employees
WHERE department = 'Finance';

From many records, we zoom in on the relevant ones.

ORDER BY: Arranging the Evidence

↪️ Command illustration:

SELECT name, score
FROM students
ORDER BY score DESC;

The most important evidence appears first.

GROUP BY: Finding Patterns

↪️ Command illustration:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Hidden trends and relationships become clearer.

JOIN: Connecting the Dots

SELECT orders.order_id, customers.name
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;

Separate clues come together to form one complete story.

SQL is not just a query language. It is a mindset. It teaches us to investigate, question, connect, and discover meaning from data. And just like a great detective, the more cases you solve, the sharper your thinking becomes.

Final Thoughts

SQL is a powerful and indispensable tool for every data analyst. However, its real strength comes from the foundation you build before using it. Understanding relational databases, statistics, analytical thinking, and core data concepts ensures that you do not just write queries. You interpret results correctly and provide meaningful insights.

Once these fundamentals are clear, learning SQL becomes more intuitive, practical, and valuable in your data analytics journey.