How to Clean and Standardize Data in Power BI Using Power Query

Introduction

You have already loaded your first dataset into Power BI. That was an important first step.

But if you look closely at the data you used, you will notice something very common in real-world datasets — it is not perfectly consistent. Some dates use a different format, text values may not always follow the same pattern, and Power BI may not correctly recognize numbers or dates.

Before any meaningful analysis can happen, this data must be standardized.

In this learning journey, this is Day 2, where we focus on preparing the same Day 1 dataset so it can be used reliably in all upcoming lessons. We are not changing the data source. We are making it analysis-ready so that future tables can connect to it properly.

What You Will Learn Today

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

• Understand why raw data must be cleaned before analysis
• Use Power Query Editor to standardize your dataset
• Fix inconsistent date formats
• Ensure numeric columns behave correctly
• Prepare your data so it can connect with future tables
• Build a repeatable cleaning process used in real jobs

Why This Step Matters for Your Career

In companies, data rarely arrives in perfect condition.
If you skip cleaning:

• Relationships between tables fail
• Calculations produce wrong results
• Dashboards show misleading insights

Analysts are expected to prepare data so that systems can trust it. Learning this now means you are practicing exactly what happens in real business environments.

The Dataset We Are Continuing From Day 1

We are using the same Excel file created earlier:

File Name: PowerBI_Learning_Project.xlsx
Sheet Name: Sales_Data

Do not create a new file. All future lessons will build on this one.

The Problem Inside This Data (And Why We Must Fix It)

Look at the Date column from Day 1:

• Some rows use 1/1/2024
• Others use 15-01-2024

To a human, both look fine.
To Power BI, they may be interpreted differently, which causes time-based analysis to fail.

Similarly:

• Sales must always be treated as numbers
• Quantity must be numeric for aggregation
• Text columns must be consistent for grouping

Today we will standardize all of this.

Step-by-Step: Open Power Query Editor

1. Open Power BI
2. Click Home → Transform Data

This opens Power Query, where all cleaning and preparation happens.

Think of this as a preparation room before analysis begins.

Step 1: Fix the Date Column (Most Important Step)

1. Select the Date column
2. Go to Transform → Data Type → Date

If Power BI shows an error:
3. Click Using Locale…
4. Choose:

• Data Type: Date
• Locale: English (India) or English (US)

This forces Power BI to interpret all formats correctly.

Result: Every row now follows one standard date structure, which will allow us to build time analysis later.

Step 2: Ensure Sales Column is Numeric

1. Select Sales
2. Change Data Type → Whole Number

If this is not corrected, totals and averages will not calculate properly.

Step 3: Ensure Quantity Column is Numeric

1. Select Quantity
2. Set Data Type → Whole Number

This prepares us to calculate metrics like total units sold.

Step 4: Standardize Text Columns

To avoid mismatches later when we connect tables:

Select each of these columns one by one:

• Product
• Category
• Region

Then apply:

Transform → Format → Capitalize Each Word

This ensures:

• “north” and “North” will not be treated as different values.

Step 5: Rename the Query Properly

On the right side (Query Settings):

Rename the query from default name to:

Sales_Data

This naming consistency is very important when we add more tables later.

Step 6: Apply Changes

Click:

Home → Close & Apply

Power BI now loads the cleaned data into the model.

You have created a structured dataset that future tables can safely connect to.

What You Just Built (Without Realizing It)

You did not just clean data.

You created what professionals call a data model foundation — a trusted table that will act as the central fact table in upcoming lessons.

All future data like targets, product details, and calendars will link to this table.

Quick Validation Check

Create a simple table visual:

Add:

• Region
• Sales
• Quantity

If totals appear correctly and dates group properly, your dataset is ready for expansion.

How This Connects to the Next Lessons

Now that Sales_Data is standardized:

We can safely introduce new supporting tables such as:

• Product reference tables
• Regional targets
• Calendar tables for time analysis

These tables will connect using shared fields like Product, Region, and Date.

Without today’s cleaning, those relationships would fail.

Conclusion

Today’s work may not feel flashy, but it is one of the most important steps in the entire Power BI workflow.

You transformed a raw dataset into a structured analytical foundation. This is exactly how analysts prepare data before building dashboards used by management.

You are now ready to start connecting data and unlocking deeper insights.

If this helped you understand how real data preparation works, continue following this learning journey. Share it with peers who are also building data skills, and stay connected as we move into the next stage — combining multiple tables into one analytical model. it with someone else who is also trying to learn, and continue with the next lesson when you are ready to build further.