Forem: Timothy Atinuke

Clean, Transform, and Load Data in Power BI: A Practical Guide to Data Preparation

Timothy Atinuke — Tue, 28 Apr 2026 03:56:17 +0000

INTRODUCTION

Data cleaning is the process of identifying and correcting errors, anomalies and inconsistencies in raw data sets to improve the quality of the data and get it ready for advanced analysis and modeling. In today’s data-driven world, raw data is often messy and rarely ready for analysis. The real value of a data analyst lies not just in collecting data, but in their ability to prepare it for meaningful insights. Data cleaning and transformation is essential because it organize tables, remove duplicates, simplify complicated columns through merging or appending, remove duplicates and null values, improve result accuracy for better and informed decision making. This is where ETL (Extract, Transform, Load) becomes essential. Power Query Editor in Power BI Desktop allows you to shape (transform) your imported data. Power Query Editor only modifies a view of your data, so you can be confident that your original data source remains unchanged.
Using Microsoft Power BI, analysts can efficiently clean, transform, and load data into a structured model ready for visualization and reporting.
This guide walks you through the step-by-step practical process of preparing data in Power BI from cleaning messy datasets to transforming the data into analysis-ready format and finally loading it into the data model for modeling and visualization.

ARCHITECTURE OVERVIEW

Power BI’s data preparation workflow through the Power Query Editor is where:
• Data Cleaning occurs
• Data Transformations are performed
• Data Loading for analysis is applied

Each step performed is recorded, making your workflow reproducible and easy to modify.

Working with Power Query Editor
The Power Query Editor is the central workspace for data transformation.
Steps:
• Access it via Transform Data in Power BI Desktop

• Each transformation step is recorded in the Applied Steps pane

• Changes are repeatable and refresh automatically with new data
Key advantage: It enables a no-code/low-code approach while still supporting advanced transformations.

1. Promoting Headers
Often, datasets load with the first row as data instead of column names.
To promote header,
Steps:

Go to Home Column
Use “Use First Row as Headers”
Ensure columns are properly labeled for easier analysis

2. Creating Index Columns
Index columns help to assign unique integer to each row in a table for easy row identification, sorting and modeling.

Steps:

Go to Add Column → Index Column
Choose: From 0, From 1 or Custom increment (which define a starting value and increment, e.g., 3, 6, 9…)

3. Conditional Columns and Logic
Conditional columns is used in the creation of new columns based on rule (IF-THEN-ELSE logic) applied to the existing columns

Steps:

Navigate to Add Column → Conditional Column
Define logic like:
If Salary = 50,000 → “Full Employee”
Else → “Contract Worker”

4. Column from Examples
It automatically creates new columns by providing one or more example values from existing columns in the table.

Steps:

Go to Add Column → Column from Examples
Provide sample outputs
Power BI automatically detects the transformation logic.

5. Unpivot and Pivot Columns
These operations reorganize dataset making dataset more flexible, consistent and compatible for analysis. Pivot Columns: turn long data (category values) from rows into wide format (separate columns). Unpivot Columns: Converts wide data (columns) into long format (rows).

Steps:

Go to Home → Transform
Select column to unpivot/pivot
Click Pivot column to convert
Chose value column (e.g., Sales) to populate the pivoted data
Click Ok
To Unpivot, click unpivot and unpivot columns
Press Ok

6. Grouping and Aggregation
Grouping helps summarize data by primarily using the “Group By”.
Steps:

Go to Transform → Group By
Apply aggregations like: Sum, Count, Average, Min/Max Example:
Total sales by region
Average revenue per customer

7. Merge and Append Queries
Used for combining datasets:
Merge Queries (Join) extract a particular column from one table to join another table through the use of a common key. E.g. merging customer and transaction tables.
Append Queries (Union) concatenate rows from two tables with the same structure into a single table. E.g. appending monthly datasets.

Merging increases the number of columns in the table while Appending increases the number of rows in the table.

Step: To Merge

Open Power query editor through the transform data
Select the table to merge from the left pane (e.g., CodeSphere Hub Sales 2019)
Click on the Home Tab and select the Merge Queries
Choose “Merge Queries” to merge directly into selected table or “Merge Queries as New” to create a new merged table
Select the data table from the first drop down and select the common column (e.g., Product_Key)
Select the second table from the second drop down and chose the matching column (I.e. Product_Key)
Click ok

Step: To Append

Open Power query editor through the transform data
Select the table to append (e.g., CodeSphere Hub Sales 2019)
Click on the Home Tab and select the Append Queries
Choose “Append Queries” to append directly into selected table or “Append Queries as New” to create a new appended table
Select the first query from the first drop down selection (e.g., CodeSphere Hub Sales 2019)
Select the second query from the second drop down selection (e.g., CodeSphere Hub Sales 2020)
Click ok

8. Date and Time Transformations
It is essential for creating time-intelligence calculations like, year-to-date, same-period-last-year, extracting year, month and day, calculating durations, date differences and standardizing date format for time series analysis and trend reporting.
Step:

Open Power query editor
Navigate to Transform → Date or Navigate to Transform → Time to extract values (Day, week, month, quarter, year).

To standardize date format, Navigate to Data Type, Select Date/Time, Date or Time to ensure accurate modeling.

Using DAX functions like DATEDIF to calculate differences

9. Adding Prefix and Suffix Using Power Query
You can modify text fields by adding prefixes or suffixes.
Steps:

Click on Transform data to open Power Query Editor
Select the column to edit
Navigate to the Transform Tab
Click Format
Add Prefix or Suffix
Enter the desired text/character in the dialog box (e.g. add “NGN” before currency values)
Also enter desired text/character for suffix and Click OK
Or use Custom Column with formulas

10. Data Profiling Techniques
Power Query includes built-in data profiling tools for understanding the structure, quality and distribution of data before loading into the model. These tools help quickly assess data health and identify issues.
The 3 concepts of data profiling techniques in power BI are;

Column Profile: which shows the entire statistics of a selected column and it shows statistics like Count, Error, Empty, Distinct Unique, Not Available Number (NAN), Min, Max, Zero, Avg, Standard Deviation, Even and Odd.
Column Distribution: shows a small histogram under the column header that visualize the frequency and distribution of values. It explicitly list out the number of distinct values and Unique values in a selected column
Column Quality: displays valid data, empty values and percentage of errors

Steps:

Click on Transform data to open Power Query Editor
Select the column to profile
Navigate to the View Tab
Enable the option to preview by checking the box and unchecking others (e.g. check the box to preview column quality)
Column Profile
Column Distribution
Column Quality

11. Handling Missing or Null Values
Missing data can distort analysis if not handled properly.

Steps:

First, identify missing data through the column quality to see the percentage of valid, error and empty (null) values in every column
Click the filter arrow on the column header
Select null (or blank) to view only rows with missing data
To replace manually,
- Right click a column
- Select Replace Values
- Type Blank in the “Value to Find”
- Enter replacement (e.g., 0 or Unknown) in the “Replace With”
To replace null and empty value by filling down,
- Select the column Go to Transform Tab
- Click Fill and select Down
- It copies the last non-null value into the subsequent null cells until it hits a new value
- It is also applicable if the “summary” or “header” values us located below the missing data rows.
To remove Missing Data
- Select column
- Click on filter arrow and chose remove Empty.

CONCLUSION

Data preparation is the most critical step in any analytics workflow. Power BI’s Power Query Editor provides a robust and flexible environment to clean, transform, and load data efficiently.
By mastering these techniques from basic cleaning to advanced transformations you position yourself to build accurate models, insightful dashboards, and impactful data stories.
The difference between average and exceptional analysis often lies not in visualization but in how well the data was prepared.

Getting Data from Multiple Sources in PowerBI: A Practical Guide to Modern Data Integration

Timothy Atinuke — Sun, 05 Apr 2026 18:15:41 +0000

INTRODUCTION

According to Microsoft, Power BI is a complete reporting solution that offers data preparation, data visualization, distribution, and management through development tools and an online platform. Power BI can scale from simple reports using a single data source to reports requiring complex data modeling and consistent themes. Use Power BI to create visually stunning, interactive reports to serve as the analytics and decision engine behind group projects, divisions, or entire organizations.
The foundation of every successful Power BI report is reliable data ingestion. Before a report can be successfully created, ability to extract data from various data sources is the first crucial step to building an effective report. Interacting with SQL Server is different from Excel, so learning the nuances of how data connection from different sources works is important in order to be able to use other PowerBI tools for effective decision making.
In most real-world business contexts, data is typically spread across multiple sources rather than confined to one. A data analyst may need to integrate data from Excel files, CSVs, SQL Server databases, PDFs, JSON APIs, and SharePoint folders into a unified report. Power BI is well-equipped for this task, offering powerful tools like Get Data and Power Query to efficiently connect, combine, and transform data from various sources. This guide explores how Power BI enables multi-source data integration and provides a step-by-step approach to implementing it effectively.
In this guide, you will learn how to:
• Connect Power BI to multiple data sources efficiently
• Use Power Query to preview and explore your data
• Detect and resolve data quality issues early
• Build a strong foundation for accurate data modeling and reporting.

Architecture Overview

At a high level, Power BI follows a layered architecture which consists of:
• Power BI Desktop as the reporting and modeling tool
• Multiple data sources, including:
o Excel and Text/CSV files
o SQL Server databases
o JSON and PDF files
o SharePoint folders
All data flows into Power BI through Power Query, where it is reviewed and prepared before loading into the data model.
Connecting Data from Multiple Sources
Power BI allows you to connect to a wide range of data sources. Below are step-by-step guides for each major source.

Step 1: Connecting to Excel

Open Power BI Desktop
Navigate to Home → Get Data → Excel
Browse and select your Excel file
In the Navigator window, select the required sheets or tables
Click Load (to import directly) or Transform Data (to clean first)

Step 2: Connecting to Text/CSV Files

Open Power BI Desktop
Navigate to Home → Get Data → Text/CSV
Browse and select the CSV file (e.g., MultiTimeline.csv)
Preview the dataset in the dialog window
Click Load or Transform Data

Step 3: Connecting to PDF

Open Power BI Desktop
Navigate to Home → Get Data → PDF
Select the PDF file
Wait for Power BI to detect available tables
Select the desired table(s)
Click Load or Transform Data

Step 4: Connecting to JSON

Open Power BI Desktop
Navigate to Home → Get Data → JSON
Select the JSON file or input API endpoint
Load the data into Power Query
Expand nested fields to structure the data properly
Click Close & Apply

Step 5: Connecting to SharePoint Folder

Open Power BI Desktop
Navigate to Home → Get Data → SharePoint Folder
Enter the SharePoint site URL
Click OK and authenticate if required
Select files from the folder
Click Combine & Transform Data

Step 6: Connecting to MySQL Database

Open Power BI Desktop
Navigate to Home → Get Data → MySQL Database
Enter the server name and database
Provide authentication credentials and click connect
Select the required tables
Click Load or Transform Data

Step 7: Connecting to SQL Server

Open Power BI Desktop
Navigate to Home → Get Data → SQL Server
Enter the server name (e.g., localhost)
Leave the database field blank (or specify one if needed)
Click OK
Select authentication method (e.g., Windows credentials)
In the Navigator pane, expand the database (e.g., AdventureWorksDW2020)
Select required tables such as: o DimEmployee o DimProduct o DimAccount
Click Transform Data to open Power Query Editor

Step 8: Connecting to Web Data

Open Power BI Desktop
Navigate to Home → Get Data → Web
Enter the URL of the web page or API
Click OK
Select the data table or structure detected
Click Load or Transform Data

Step 9: Connecting to Azure Analysis Services

Open Power BI Desktop
Navigate to Home → Get Data → Azure → Azure Analysis Services
Enter the server name
Select the database/model
Choose connection mode (Live connection recommended)
Click Connect

Conclusion

Integrating data from multiple sources in Microsoft Power BI is a foundational skill for modern data analysts. By understanding the architecture and following a structured approach, you can transform fragmented datasets into cohesive, insight-driven reports. Ultimately, great analytics begins with great data and great data begins with how well you connect, prepare, understand and use it to make business decisions.
Mastering tools like Power Query and applying best practices in data modeling will significantly enhance the quality and performance of your analytics solutions.