1. What is Power Query?
Power Query is a tool in Excel used to import, clean, and transform data automatically
before analysis.
In simple words, Power Query helps us prepare data so that it is ready for analysis using
Pivot Tables, charts, or dashboards.
2. Why Do We Need Power Query?
In real life, data is often:
In different files
Poorly formatted
Containing blank rows or columns
Repeated every month or every day
Cleaning such data manually in Excel is time-consuming and repetitive.
Power Query solves this problem by allowing us to:
Clean data once
Save the steps
Reapply the same steps automatically when new data arrives
3. Where is Power Query in Excel?
Power Query is available under the Data tab.
Data → Get & Transform Data
This section includes options such as:
Get Data
From Excel
From Text / CSV
Transform Data
4. How Power Query Works
Power Query works in three basic steps:
Step 1: Get Data
Data can be imported from:
Excel files
CSV or Text files
Folders
Databases (advanced use)
Example:
Importing a monthly sales file from Excel.
Step 2: Transform Data
This step is used to clean and modify the data.
Common transformations include:
Removing blank rows
Removing unnecessary columns
Changing data types (text to number, date format, etc.)
Renaming columns
Removing duplicates
Splitting columns
All changes are done using menu options (no formulas).
Step 3: Load Data
After cleaning, the data is loaded:
Back into an Excel worksheet
Or into the Excel Data Model
The original data file is not changed.
5. Important Feature: Applied Steps
Every action performed in Power Query is recorded as an Applied Step.
These steps:
Appear on the right side of the Power Query window
Are applied in sequence
Can be edited or deleted
If the source data changes, clicking Refresh automatically reapplies all steps.
6. Example 1: Removing Blank Rows
Problem
A sales dataset contains blank rows between records.
Power Query Solution
Import the data
Use the option Remove Blank Rows
Load the cleaned data
Benefit
The dataset becomes clean and analysis-ready without manual deletion.
7. Example 2: Monthly Sales File
Every month, a company receives a sales file with:
Extra columns
Incorrect date format
Repeated structure
Using Power Query:
1. Clean the file once
2. Save the steps
3. Replace the file next month
4. Click Refresh
The cleaned data is generated automatically.
8. Difference Between Normal Excel and Power Query
Normal Excel Power Query
Manual data cleaning Automated cleaning
Uses formulas Uses menu options
Needs repetition One-time setup
Error-prone Consistent results
Changes data directly Keeps original data unchanged
9. What Power Query Is Used For
Power Query is mainly used for:
Data cleaning
Data preparation
Combining multiple files
Standardising data formats
10. What Power Query Is NOT Used For
Power Query is not used for:
Calculations like SUM or IF
Creating charts
Making dashboards
Its role ends before analysis begins.
11. Relationship Between Power Query, Pivot Tables, and
Dashboards
The typical workflow in Excel is:
1. Power Query – Clean and prepare data
2. Pivot Tables – Analyse data
3. Charts / Dashboards – Present results
Power Query ensures that the data used for analysis is accurate and consistent.
12. Advantages of Using Power Query
Saves time
Reduces manual effort
Ensures consistency
Handles large datasets easily
Ideal for repeated reporting tasks
Power Query is an Excel tool used to import, clean, and transform data automatically before
analysis.
Conclusion
Power Query simplifies the most time-consuming part of data analysis—data preparation.
By automating cleaning steps, it allows users to focus more on analysis and decision-making.
PRACTICAL QUESTION: POWER
QUERY
Question
The following data is provided in an Excel file containing sales details. The data has
formatting issues.
Raw Data (Before Cleaning)
Product Region Sales Date
Mobile Phones North 250000 01-01-25
Laptops South 05-01-25
Tablets East 180000 10-01-25
Mobile Phones North 250000 01-01-25
West 220000 15-01-25
Laptops South 300000 20-01-25
Required:
a) Import the data into Power Query
b) Remove blank rows
c) Remove duplicate records
d) Replace missing Sales values with 0
e) Load the cleaned data back into Excel
SOLUTION
Step (a): Import Data into Power Query
1. Select the dataset in Excel
2. Go to:
Data → Get & Transform Data → From Table/Range
3. Click OK
Explanation
Power Query opens in a new window where data cleaning is performed without affecting the
original data.
Step (b): Remove Blank Rows
1. Click on any column header
2. Go to:
Home → Remove Rows → Remove Blank Rows
Explanation
This removes rows where all values are blank.
Step (c): Remove Duplicate Records
1. Select all columns
2. Go to:
Home → Remove Rows → Remove Duplicates
Explanation
Duplicate rows are deleted, ensuring each record appears only once.
Step (d): Replace Missing Sales Values with 0
1. Select the Sales column
2. Go to:
Transform → Replace Values
3. Replace:
o Value to Find: (leave blank)
o Replace With: 0
4. Click OK
Explanation
Missing sales values are replaced with 0 to avoid errors during analysis.
Step (e): Load Cleaned Data Back to Excel
1. Click:
Home → Close & Load
Final Cleaned Data (After Power Query)
Product Region Sales Date
Mobile Phones North 250000 01-01-2025
Laptops South 0 05-01-2025
Tablets East 180000 10-01-2025
Laptops South 300000 20-01-2025
Power Query cleans data without changing the original file
Cleaning steps are saved and reusable
Data can be refreshed automatically
Ideal for repeated reporting tasks