Forem: Dreez

Data Preparation in Power BI: Cleaning, Transforming, and Loading Data for Real-World Analytics

Dreez — Wed, 29 Apr 2026 11:17:53 +0000

Introduction

Before any dashboard delivers value, there’s a quiet but critical phase every analyst must master data preparation.

In Microsoft Power BI, this happens inside the Power Query Editor, where raw, messy, inconsistent data is shaped into something reliable and analysis-ready.

If you’re building a career in data analytics, this is not optional.

Dashboards don’t fail because of visuals, they fail because of poor data preparation.

In real-world scenarios, data comes in different formats (Excel, CSV, databases)

With missing values and inconsistencies
Without proper structure for analysis

Your role as an analyst is to clean, transform, and structure that data before it ever reaches a report.

What You’ll Learn

This guide walks you through the core transformation techniques in Power BI, with practical explanations and examples you can apply immediately:

Cleaning messy datasets
Creating calculated and conditional columns
Reshaping data (pivot/unpivot)
Combining datasets (merge/append)
Profiling data quality
Handling nulls and duplicates

💡 Career Insight:

Power Query skills are one of the most underrated but most demanded competencies in analytics roles.

1. Working with Power Query Editor

How to Access

Open Power BI Desktop
Click Home → Transform Data

This will take you directly to the Power Query Editor.

Key Components

Queries Pane → List of tables
Data Preview Grid → Your working dataset
Applied Steps → Tracks every transformation
Ribbon Tools → Transform, Add Column, View

💡

Every action you take is recorded, making your workflow repeatable and automated.

2. Identifying Column Headers and Names

Talking about data transformation and cleaning in Power Query, identifying column headers and names is considered the very first step.

Once your data is loaded into Microsoft Power BI, the Power Query Editor treats every row as part of the dataset by default. That’s why identifying the correct column headers and ensuring they are properly named is a critical first step. It helps structure your data correctly and makes further transformation and analysis much easier to understand and manage.

In most datasets, the column headers are typically located in the first row. Recognizing this early is an important step in preparing your data for transformation and reshaping. Once identified, these values should be promoted to serve as the official column headers within the table in Microsoft Power BI.

Doing this ensures your dataset is properly structured, making it easier to apply further transformations, perform analysis, and maintain clarity throughout your workflow.

As shown in the image, the highlighted first row is automatically treated as the header by the Power Query Editor in Microsoft Power BI. However, the actual column headers for the dataset are located in the second row.

This means the second row needs to be promoted to replace the default headers so that each column is accurately named, ensuring proper data structure and making subsequent transformations more meaningful and easier to apply.

A. How to Promote First Row to Header

Select the drop-down button next to Column1 and
Select Use First Row as Headers.

B. How to Rename Column Header

Click on column name
Select Transform from the Ribbon tray and
Click on the rename option
Edit name as desired.

3. Creating Index Columns

In Microsoft Power BI, column indexing involves adding a new column that assigns a unique sequential number to each row in a dataset. This is especially useful during data preparation and transformation in the Power Query Editor.

Use cases:

Creating unique row identifiers: An index column provides a simple way to assign a unique ID to each row, which is helpful for tracking records, and creating relationship between tables, especially when the dataset lacks a primary key.
Ranking or ordering data: Indexing allows you to establish a clear order within your dataset. This can be used to rank values, maintain sorting positions, or create custom sequences for analysis.

How to add column index

Go to Add Column tab
Select Index Column
Choose: From 0 or 1 or Custom

Note:

From 0: The numbering starts at 0 and increases sequentially (0, 1, 2, 3…).
From 1: The numbering starts at 1 and continues sequentially (1, 2, 3, 4…).
Custom: This option allows you to define your own starting point and increment (step value), giving you full control over how the sequence is generated.

💡 Useful when your dataset lacks a natural key.

4. Adding Column from Examples

In Microsoft Power BI, “Column from Examples” is a smart feature in the Power Query Editor that automatically creates a new column based on patterns you provide.

What it means:
You simply type a few example values of the result you want, and Power BI detects the pattern and generates the transformation for the entire column.

How it works (conceptually):

You provide sample outputs based on existing columns
Power Query analyzes the pattern
It applies the detected logic to all rows automatically

Use cases:

Extracting specific text (e.g., first names from full names)
Combining columns (e.g., first name + last name)
Formatting values (e.g., dates, codes, or labels)

Why it’s useful:
It reduces the need to write complex formulas manually, making data transformation faster, more intuitive, and beginner-friendly.

How to Add Column from Examples

Select the Column for use case
Click on "Add Column ribbon"
Select Column from Examples
click the "from selection" option

Double-click on the column provided to enter desired example format and click OK.

PowerBI will replicates format through all rows down the column as shown below.

5. Grouping and Aggregation

In Microsoft Power BI, grouping and aggregation are key techniques used in the Power Query Editor to summarize and organize data for meaningful analysis.

Grouping involves combining rows that share common values in one or more columns, while aggregation applies calculations (like sum, count, or average) to those grouped rows.

How it works (conceptually):

Select a column to group by (e.g., Region, Product, or Category)
Apply an aggregation (e.g., total sales, number of transactions)
Power Query produces a summarized table based on those groups

Common aggregation operations:

Sum: Total of numeric values
Count: Number of rows
Average: Mean value
Min/Max: Smallest or largest value

How to Use Aggregation and Grouping

Select column
Click on Transform
Click Group By
Enter Desired column name for output
Choose desired aggregation operation: Sum
Choose column for operation execution ()
Click OK.

In the example shown below we chose the SUM Aggregation.

Note: We aim to aggregate sales(quantity sold) by year from the sample data used.

💡 This creates summarized tables for analysis.

Note: In Microsoft Power BI, data transformations such as grouping and aggregation do not alter the original dataset but create a refined version through applied steps in the Power Query Editor. Once these transformations are completed, selecting “Close & Apply” loads the processed data into the data model. This transformed dataset is then used in the Report View to build visuals, ensuring that analysis is based on clean, structured, and meaningful data rather than the raw source.

6. Pivoting and Unpivoting Columns

In Microsoft Power BI pivoting and Unpivoting are essential data shaping techniques in the Power Query Editor used to reorganize how your data is structured—either by spreading values across columns or consolidating them into rows.

Hint: Pivot turns rows into columns, Unpivot turns columns into rows

A. Unpivoting Columns
Unpivoting converts multiple columns into rows of one column.

What it does: Unpivot columns turns multiple columns into attribute-value pairs and Creates a more flexible, normalized structure

Use cases:

Preparing data for analysis and visualization
Making datasets compatible with charts and DAX calculations
Converting wide data into a long format

Why It Matters
Prepares data for visualization
Converts wide data → long format (best for Power BI)

💡 Most dashboards rely on unpivoted (normalized) data.

How to Unpivot Columns

Multi-select columns
Click on Transform
Click on Unpivot
Select Unpivot columns

Result

B. Pivot Column

Does the opposite of Unpivot columns

Converts row values from one column into separate column headers

Aggregates values where necessary

Example:
Turning a “Month” column into separate columns like Jan, Feb, Mar with their corresponding sales values.

Use cases:

Creating summarized reports

Converting long data into a wide format

Preparing data for dashboard visuals

How to Pivot Columns

Using the transformed data from the Unpivot section, It is observed that the "product sold" and "profit" tag are unpivoted into a single column. Hence this suits the scenario for pivoting.

Select column
Click on transform
Click on Pivot Column
Select Value from the values column drop down
Click OK.

Power BI coverts the distinct column values into headers as shown below:

7. Merging and Appending Queries in Microsoft Power BI

When working with multiple datasets in the Power Query Editor, merging and appending are essential techniques used to combine data in meaningful ways.

A. Merging Queries (Horizontal Combination)
Merging combines two tables based on a common column (like an ID), similar to a SQL join.

Use cases:

Adding customer details to a sales table
Combining related datasets from different sources
Enriching data with additional attributes

For this example, we will use the “Employees Demographics” table as the primary dataset. The objective is to merge it with the “Employee Salary” table in order to incorporate salary information into the dataset.

How to Merge Queries

Select a primary table (employee_demographics)
Click on Home Tab
Click on Merge Queries on the far right of the Home Tab
Choose either "Merge Queries" or "Merge Queries as New" base on preferred result. In this case we chose "Merge Queries as New"

Choose a related table(employee_salary)

Match them using a common field (e.g., Employee ID)

Expand the merged column to bring in needed field(s) and click OK

Result

Note;
Types of joins available:

Inner join

Left outer join (most common)

Right outer join

Full outer join

B. Appending Queries (Vertical Combination)
Appending queries is the process of combining two or more tables by stacking them on top of each other, resulting in a single table with more rows.

What it does:

Adds rows from one table to another
Keeps the column structure consistent
Matches columns based on their names

Use cases:

Combining monthly or yearly datasets into one table
Merging data from different departments or locations
Building a complete dataset from multiple sources

> Key idea:

Ensure the tables have similar or identical column structures
Appending increases the number of rows, not columns—making it ideal for consolidating similar datasets into a single, comprehensive table for analysis.

How to Append Queries

For this examples we will be making use of two similar structured tables, CodeSphere Hub Sales - 2019 and CodeSphere Hub Sales - 2020.

Select the primary table (CodeSphere Hub Sales - 2019).
Click on the Home Tab
Select Append Queries on the far right of the Home Tab.
Choose either "Append Queries" or "Append Queries as New" base on preferred result. In this case we chose "Append Queries as New"

Select the other table to append. In this case we are using(CodeSphere Hub Sales - 2020).

Click Ok.

Result

Note: This New query can be renamed and used further.

8. Date and Time Transformations in Microsoft Power BI

Date and time transformations in the Power Query Editor involve modifying, extracting, or formatting date/time values to make them more useful for analysis and reporting. These transformations involve Working with date and time data to:

A. Change formats (e.g., from text to date)
B. Extract specific components
C. Perform calculations using dates

A. Changing Data Types: This involves converting columns into proper Date, Time, or Date/Time formats to enable accurate analysis.

How to

Select the date related field
click on the left icon beside the header name
select appropriate date format that aligns with the field entries.

B. Extracting Date Parts
Break down a date into components such as: Year, Quarter, Month, Day, Day of Week.

How to

Select Date Column/Field.
Click on "Add Column Tab"
Click on the calendar icon
select desired component to be extracted

Result

Note: Power BI created an extracted field "Month" base on our selected preference of extraction.

C. Calculating Durations
This transformation involves finding the differences between dates (e.g., number of days between two events).

Case Study: In this scenario, the goal is to calculate the time difference between the stock date and the transaction date using the CodeSphere Hub Sales – 2019 dataset.

How to

Ensure Correct Data Types both columns (Stock Date and Transaction Date)data type should be set to Date.

Select column >> Transformation >> Data type >> Date

Add a Custom Column Go to Add Column tab → Custom Column

Enter a formula like:

= [Transaction Date] - [Stock Date]

And click OK.

Convert to Number of Days The result will be in duration format Select the Resulting Column → Go to Transform tab → Duration → Days

9. Adding Prefix and Suffix in Microsoft Power BI

Adding a prefix or suffix is a simple text transformation in the Power Query Editor used to modify column values by attaching additional text at the beginning or end.

What it means
Prefix: Text added before existing values
Suffix: Text added after existing values

Use cases

Creating standardized IDs (e.g., EMP-001)
Adding country or region codes
Formatting labels for better readability

For example, this simple transformation can be used to standardize values such as employee IDs or country codes.

Prefix “ID-” → ID-12345
Suffix “-NG” → 12345-NG

However, in this case study, we will apply a suffix to the Days column created from our earlier date transformation. This means each value will be formatted to include a label, such as 180-days, to improve clarity and readability.

How to

Select the column you want to modify
Go to Transform tab → Format
Choose: Add Prefix → enter text to appear before values 0r Add Suffix → enter text to appear after values

Enter desired text and click OK.

> Result

10. Data Profiling Techniques in Microsoft Power BI

Data profiling involves examining your dataset to understand its structure, quality, and overall condition before analysis. In the Power Query Editor, it helps you quickly detect errors, inconsistencies, and patterns.

There are 3 major data profiling tools used by professionals. These tools help professionals detect issues before analysis.

Column quality: This helps professionals quickly identify data issues in each column. It displays the valid values, errors, empty values
Column distribution: This useful for spotting duplicates or unusual patterns in column data. Shows how values are spread within a column, including:
Distinct values
Unique values
Frequency distribution (histogram view)
Column profile: This provides detailed statistics such as:
Minimum and maximum values
Average
Count of values
Data type distribution

How to Enable Data Profiling
Go to View tab in Power Query Editor
Enable:

Column Quality
Column Distribution
Column Profile

Use cases

Detecting missing or incorrect data
Identifying duplicates or outliers
Understanding data patterns before transformation
Key Insight

Data profiling helps you trust your data before working with it. By identifying issues early, you can clean and transform your dataset more effectively, leading to more accurate and reliable analysis.

11. Handling Missing or Null Values in Microsoft Power BI

Missing or null values are common in real-world datasets, and handling them properly is essential to ensure accurate analysis and reliable reporting.

What are Null Values?
Null values represent missing, undefined, or empty data in a column. If not addressed, they can lead to incorrect calculations and misleading insights.

Common Techniques for Handling Null Values

A. Removing Null Values: This is used to remove rows that contain nulls.
It is best when missing data is minimal and not critical

Using Column Filter (Most Common)

Select the column you want to check
Click the filter dropdown on the column header
Uncheck (null) or (blank)
Click OK

B. Replacing Null Values: This involve replacing null values with Default values (e.g., 0, “Unknown”) or Calculated values (e.g., average, median).

Using the Replace Value Feature

Select Column
Click on the transform ribbon
Select Replace Values

Input the value to find, and the value to replace with.
Click OK.

Note: The "replace with" value data type must align with the column data type.

C. Fill Down / Fill Up: Fill-Down and Fill-Up are simple yet powerful techniques in the Power Query Editor used to handle missing (null) values by propagating existing values within a column.

Fill Down: Uses the value from the row above
Fill Up: Uses the value from the row below

They are useful for structured datasets with repeated categories.

Steps:

Select the column containing null values
Go to Transform tab
Choose: Fill → Down or Fill → Up. Depends on what is appropriate for further analytic procedures.

Result

Conclusion

Data preparation is where raw data becomes valuable, reliable, and actionable.

In Power BI, the Power Query Editor gives you everything you need to:

Clean messy datasets
Transform structures
Combine multiple sources
Validate data quality

But beyond tools, what truly matters is your mindset.

A great analyst doesn’t just load data, they question it, shape it, and refine it.

Getting Data from Multiple Sources in Power BI: Building the Foundation of a Data Analytics Career

Dreez — Tue, 21 Apr 2026 11:25:09 +0000

Introduction

Every meaningful insight in data analytics begins long before dashboards and visualizations. It starts with how you access, understand, and prepare your data.

In Microsoft Power BI, data ingestion is not just a technical step; it is a core professional skill that separates entry-level users from confident, job-ready analysts.

Many beginners focus heavily on visuals and charts, colors, and layouts. But in real-world scenarios, the real challenge is different.

Data is most times scattered, inconsistent, incomplete, and often comes from multiple sources.

As a data analyst, you are expected to:

Work with Excel reports from finance
Pull structured data from SQL databases
Clean CSV exports from operational systems
Extract insights from APIs (JSON)
Integrate cloud-based sources like SharePoint or Azure

This is where Power BI becomes a powerful ally. With Power BI Get Data
functionality and Power Query Editor, you are equipped not just to connect to data, but to:

Explore and understand its structure
Detect quality issues early
Transform raw data into analysis-ready datasets

Connecting Data from Multiple Sources

Power BI provides a wide range of connectors that allow you to integrate diverse data sources into a single analytical model. In real-world analytics, data comes from multiple systems, including:

Excel and CSV files
SQL Server and MySQL databases
JSON APIs and web sources
PDFs and SharePoint folders
Cloud platforms like Azure

What You Will Learn in This Guide

This guide is designed with your career growth in mind. By the end, you will be able to Confidently connect to multiple data sources in Power BI such as

Excel
Text/CSV Files
PDF
JSON
SharePoint Folder
MySQL Database
SQL Server
Web Data
Azure Analysis Services

1. Connecting to Excel

Open Power BI Desktop

Navigate to Home → Get Data → Excel

Browse and Select your Excel file

In the Navigator window, choose tables or sheets

Click Load(to import directly) or Transform Data(to clean data before import)

💡 Best Practice: Always structure your Excel data as tables and avoid merged cells.

2. Connecting to Text/CSV Files

Open Power BI Desktop

Navigate to Home → Get Data → Text/CSV

Select your CSV file (e.g., data.csv promomtion)
Preview the dataset

Click Load or Transform Data

💡 Key Learning: Check column data types immediately (text vs numeric).

3. Connecting to PDF

Go to Home → Get Data → PDF → Connect

Select the PDF file and click open

Allow Power BI to detect tables

Select required table(s)

Click Load or Transform Data

💡 PDFs often require additional cleaning due to unstructured formats.

4. Connecting to JSON

Navigate to Home → Get Data → JSON → Connect

Select to load file or API endpoint

Open in Power Query

Expand nested structures into columns

Select the fields that you want to load into Power BI Desktop, clear the Use original column name as prefix checkbox, and then select OK.

Click the Alphabet/Numeral icon to the left of the column headers to verify/change data type

Review the selected data to ensure that you're satisfied with it, then select Close & Apply to load the data into Power BI Desktop.

💡 JSON requires understanding of hierarchical data structures.

5. Connecting to SharePoint Folder

Go to Home → Get Data → More

SharePoint Folder → Connect

Enter SharePoint site URL

Note; You don’t need to paste the full file path or link. Just enter the main website URL. Once you connect, you’ll be able to choose the specific list or data you want to load. If your link is too long, simply remove the extra part at the end and keep only the main site address (as shown in the example).

Authenticate using any medium you have access too and connect

Select files

Click Combine & Transform Data

💡 Widely used in corporate environments for collaborative data storage.

6. Connecting to MySQL Database

Navigate to Home → Get Data → MySQL Database → Connect

Enter server and database

Authenticate (Select a sign-in option, enter your username and password, and then select Connect.) When connecting to a database, you may see different authentication options:

Windows – Use your Windows login (the same one you use to sign in to your computer or Azure Active Directory).
Database – Use credentials specific to the database. For example, SQL Server can have its own username and password. If your database administrator gave you a separate login, enter it here.
Microsoft account – Use your personal Microsoft account. This is usually for connecting to Azure services or cloud database

Select tables

Click Load or Transform Data

💡 Databases provide structured, reliable data for analysis.

7. Connecting to SQL Server

Go to Home → Get Data → SQL Server

Enter server name (e.g., localhost)

Click OK

Authenticate (Select a sign-in option, enter your username and password, and then select Connect.) When connecting to a database, you may see different authentication options:

Windows – Use your Windows login (the same one you use to sign in to your computer or Azure Active Directory).
Database – Use credentials specific to the database. For example, SQL Server can have its own username and password. If your database administrator gave you a separate login, enter it here.
Microsoft account – Use your personal Microsoft account. This is usually for connecting to Azure services or cloud database

Expand database (e.g., parks_and_recreation)

Select table(s):

Click Transform Data (prepare data as desired)

💡 Professional Tip:
Always inspect data in Power Query before loading — never assume it is clean.

8. Connecting to Web Data

Navigate to Home → Get Data → Web

Enter URL and click OK

Select any of the detected tables you want

Click Load or Transform Data

💡 Ideal for APIs and publicly available datasets.

9. Connecting to Azure Analysis Services

Go to Home → Get Data → Azure → Azure Analysis Services

Connect

Enter server name

Choose Live Connection and click OK

Click Connect

💡 Used in enterprise environments with pre-built semantic models.

Conclusion

Connecting to multiple data sources in Power BI is more than a technical capability, it is a career-defining competency.

In today’s data-driven world, organizations rarely operate on a single source of truth. Data is distributed across systems, formats, and platforms. As an analyst, your value lies in your ability to bring that data together, make sense of it, and prepare it for decision-making.

Power BI simplifies this complexity, but it does not remove the responsibility. That responsibility belongs to you the analyst.

By mastering data connection techniques,** data preview and profiling, _early detection of data quality issues**_

You are building something far more important than reports. You are building analytical confidence and professional credibility.

💡

Final Career Perspective:
Tools will evolve. Interfaces will change. But the ability to understand and integrate data from multiple sources will remain one of the most valuable and transferable skills in your analytics journey.