In the previous articles in the Excel series, we looked at the most basic features and tools available in Excel for data analysis. We looked at functions, pivot tables and pivot charts, data formatting techniques and charts. I hope you managed to practice these tools with the practice tasks outlined in the articles.
Now its time to move on to some intermediate level Excel features helpful for data analysis. The first thing I’m going to look at is data cleaning and transformation. One of the major tools available in Excel for data cleaning is power query.
What is Power Query?
Power Query is a built-in tool in Excel that allows you to import, clean, and transform data efficiently without writing formulas. Below are some of the benefits of using power query,
✅ Connect to multiple data sources (Excel, CSV, databases, APIs)
✅ Automate repetitive data-cleaning tasks
✅ Handle missing values, duplicates, and text manipulations
✅ Merge and append datasets
✅ Perform ETL (Extract, Transform, Load) operations
In this article, we are going to look at power query from data loaded through Excel,
To open Power Query:
Go to the Data tab > Click "Get Data" > Choose "From Table/Range"
If your data isn’t formatted as a table yet, Excel will prompt you to create one. Click "OK."
The Power Query Editor will open, where you can see your data in a structured way.
Now we have the power query window opened. Lets first look some common data cleaning operations using power query,
1️⃣ Removing Duplicates
If your dataset contains duplicate entries, you can remove them easily
✔️ Click on the column(s) where duplicates exist
✔️ Go to the Home tab → Click Remove Duplicates
✔️ Power Query automatically keeps only unique values
2️⃣ Handling Missing Data (Null Values)
If your dataset has empty or missing values:
✔️ Click on the column where null values exist
✔️ Go to Transform → Click Replace Values
✔️ Enter a default value (e.g., “N/A” or “0”)
Alternatively, you can:
✔️ Click on Remove Rows → Remove empty or null values
3️⃣ Splitting Columns (Text-to-Columns on Steroids)
Sometimes, data is stored in a single column but should be split (e.g., "John Doe" into "First Name" & "Last Name").
✔️ Click the column → Go to Transform → Split Column
✔️ Choose By Delimiter (e.g., space, comma, dash)
✔️ Excel automatically creates new columns
4️⃣ Merging (Joining) Datasets
If you have data from multiple sources and need to merge them:
✔️ Go to Home → Click Merge Queries
✔️ Select the common column (like an ID or Name)
✔️ Choose the Join Type (Inner, Outer, Left, Right)
✔️ Click "OK" to combine the datasets
5️⃣ Unpivoting & Pivoting Data (Rearrange Data Easily)
Sometimes, data is stored in a wide format but needs to be long format for analysis.
✔️ Select the columns → Go to Transform → Click Unpivot Columns
✔️ This will turn your wide data into a more usable format
6️⃣ Exporting Cleaned Data Back to Excel
Once you’re done with transformations:
✔️ Click Close & Load → Choose "Load to Excel Table"
✔️ The cleaned data is now in your worksheet
Below is my cleaned dataset after exporting to Excel.
You can try following the above steps to work on the below practice exercise. Power Query is truly an amazing feature of Excel. You can clean and prepare your data without writing any functions or queries in a really fast manner.
Practice Exercise
Create the following dataset in Excel first and then use power query to solve the questions.
Tasks:
1️⃣ Remove duplicates
2️⃣ Fill missing age values (use average age)
3️⃣ Split "Name" into "First Name" & "Last Name"
4️⃣ Remove "$" from Salary column
5️⃣ Export cleaned data back to Excel
Hope this article will help you to learn and use power query for your data cleaning and transformation tasks.