Transforming Data means cleaning, organizing, and modifying raw data into a structured format suitable for analysis and reporting.
In Excel, data transformation is mainly done using Power Query (Get & Transform Data).
Why Transform Data?
- Clean messy datasets
- Remove errors and duplicates
- Standardize formats
- Prepare data for Pivot Tables
- Improve data accuracy
- Automate repetitive cleaning tasks
Where to Transform Data
Go to:
Data Tab → Get & Transform Data → Get Data
After importing data, click Transform Data to open the Power Query Editor.
Common Data Transformation Tasks
Remove Unnecessary Columns
- Select column
- Right-click → Remove
Keeps dataset clean and relevant.
Filter Rows
- Use filter dropdown
- Remove blanks
- Filter specific values
Change Data Type
Ensure correct data types:
- Text
- Whole Number
- Decimal Number
- Date
- Currency
Incorrect data types can cause calculation errors.
Split Columns
Example:
Full Name → Split into First Name & Last Name
Steps:
- Select column
- Click Split Column
- Choose delimiter (comma, space, etc.)
Merge Columns
Combine multiple columns into one.
Example:
First Name + Last Name → Full Name
Remove Duplicates
Select column → Remove Rows → Remove Duplicates
Replace Values
Example:
Replace “N/A” with blank
Replace “Karachi City” with “Karachi”
Group By
Summarize data directly in Power Query.
Example:
Total Sales by Department.
Pivot and Unpivot Columns
- Pivot: Convert rows into columns
- Unpivot: Convert columns into rows
Useful for restructuring data.
Applied Steps Panel
Every transformation is recorded in the Applied Steps panel.
Benefits:
- Track changes
- Edit steps
- Delete steps
- Automatically reapply when refreshed
Load Transformed Data
After transformation:
- Click Close & Load
- Data loads into worksheet
- Or load into Data Model
Benefits of Transforming Data
- Automated cleaning
- Saves time
- Reduces manual errors
- Repeatable process
- Handles large datasets efficiently
Best Practices
- Remove unnecessary columns first
- Set correct data types early
- Keep column names clear
- Avoid manual edits after loading
- Always test refresh
Conclusion
Transforming Data in Excel is essential for accurate analysis and reporting. Using Power Query, you can clean, reshape, and automate data preparation efficiently, making your workflow more professional and reliable.