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

Power Query is a tool in Power BI that helps you get, clean, and prepare your data before analysis. It has a graphical interface to connect to data sources (like Excel, CSV, or databases) and a Power Query editor to apply transformations easily.

Using Power Query, you can extract data, transform it by cleaning and organizing, and load it into Power BI for building dashboards. This process is known as ETL (Extract, Transform, Load).

Why is Power Query Important?

• It ensures your data is clean and organized before creating visuals.

• It saves time by automating repetitive cleaning tasks.

• It allows you to handle large, messy datasets without manual editing.

• It helps you build reliable dashboards with accurate data in Power BI.

Power query interface overview:

In power BI desktop click on transform data.

The power query editor will open with

Ribbon (Top Menu)

Contains tools for:

• Managing columns and rows.

• Sorting and filtering data.

• Transforming data types.

• Combining data (append and merge).

• Running queries.

Queries Pane (Left Sidebar)

Shows all the tables and queries you have loaded or created.

You can rename, duplicate, or manage these queries here.

Data Preview (Center)

• Displays a preview of your data so you can see:

• Column names and data types.

• Sample data rows.

• Changes applied instantly.

Applied Steps Pane (Right Sidebar)

Lists all steps you apply to your data, in order (e.g., removing columns, changing data types).

You can:

• Rename steps for clarity.

• Reorder or delete steps if needed.

  

Connecting to data using power query:

Open Power BI Desktop on your computer. Click “Get data from other sources”.

• Click **“Get Data”**on the Home ribbon.

• Choose your data source (Excel, CSV, database, web).

• Browse and select your file or connect to your data source.

• You will see a preview of your data in the Navigator.

• Select the tables or sheets you need.

• Click “Load” to import directly, or “Transform Data” to open Power Query for cleaning.

  

  

  

  

  

  

Basic Data Cleaning Using Power Query:

What is Basic Data Cleaning?

Basic data cleaning means fixing and organizing your data so it is ready for analysis. It involves removing unnecessary columns and rows you do not need, renaming columns to make them clear and easy to understand, and filtering out unwanted data like blanks or incorrect values. It also includes fixing data types, such as changing text to numbers or dates where needed. Cleaning your data in this way helps you create clear and accurate reports in Power BI.

My data set includes below columns:

• Order ID

• Order Date

• Customer Name

• Product

• Quantity

• Unit Price

• Region

• Total Sales

Remove Unnecessary Columns

1. Right-click the column you want to remove, e.g.,

2. Order ID

3. Select “Remove.”

   

   

Rename Columns for Clarity

• Double-click on the column name.

• Rename:

Customer Name → Customer

Order Date → Date

• Press Enter after renaming.

  

  

Filter Rows

Example: Show orders where Quantity > 1

1. Click the dropdown in the Quantity column.

2. Choose “Number Filters” → “Greater Than”.

3. Enter 1 and press OK.

   

   

   

   

Sorting data:

1. click on the dropdown.

2. select “Sort Ascending.”

   

   

Change Data Types

In the column headers, check the icons (ABC, 123, calendar).

If incorrect:

Click the icon → Select correct type:

Date for Order Date

Whole Number for Quantity

Decimal Number for Unit Price and Total Sales

Create New Columns

1. To calculate Total = Quantity * Unit Price:

2. Click “Add Column” → “Custom Column.”

3. In the formula box, type:

   1. \=[Quantity] * [Unit Price]

4. Name the column: Total

5. Click Ok.

   

   

Split Date

To analyze by year and month:

Select Order Date column.

Go to “Add Column” → “Date” → “Year”, then do the same for Month.

This creates two new columns: Year and Month.

Group By Region

1. To see total sales by region:

2. Select Region column.

3. Go to “Transform” → “Group By”.

4. In the dialog box:

Group by: Region

New column name: Region Total

Operation: Sum

Column: Total

1. Click OK

   

   

   

Once all steps are done:

Click “Close & Apply” on the top-left.

Your cleaned and transformed data will now appear in Power BI for reporting.