Introduction to Power Query
What is Power Query?
Power Query is a tool in Excel that helps you import, clean, and transform data easily—without
writing any code.
Think of it as:
A washing machine for your data.
You load the dirty data → Power Query cleans it → You get neat, organized data.
Where do you find it?
In Excel → Data Tab → Get & Transform Data → Power Query
What can Power Query do?
• Remove duplicates
• Remove empty rows
• Fix inconsistent data
• Split text into columns
• Combine multiple files
• Filter data
• Automatically repeat tasks
Example
You have a list of customers with spelling mistakes, missing values, and extra spaces.
Power Query helps you clean all this with just a few clicks.
Introduction to Data Cleaning
What is Data Cleaning?
Data cleaning means:
Fixing wrong data, removing bad data, and organizing messy data.
Just like cleaning your room:
• Remove unwanted items
• Arrange things properly
• Fix broken items
Why is data cleaning important?
Because wrong or messy data → wrong results.
Removing Duplicate Data
What is a duplicate?
A duplicate is when the same information appears more than one time in the data.
Example:
Row Name City
1 Rahul Delhi
2 Sneha Mumbai
3 Rahul Delhi
4 Arjun Chennai
5 Meera Kolkata
6 Sneha Mumbai
7 Kiran Pune
8 Arjun Chennai
9 Rahul Delhi
10 Meera Kolkata
In this table:
• "Rahul – Delhi" appears 3 times
• "Sneha – Mumbai" appears 2 times
• "Arjun – Chennai" appears 2 times
• "Meera – Kolkata" appears 2 times
All these extra repeated rows are duplicates.
How to remove duplicates in Power Query?
Follow these steps:
1. Load your data into Power Query
Excel → Data Tab → From Table/Range
2. Select the columns you want to check (example: Name + City)
3. Click:
Remove Rows → Remove Duplicates
4. Power Query will keep only unique rows.
Why remove duplicates?
Prevents double counting
Example:
If Rahul appears 3 times, it wrongly looks like 3 customers, but actually it’s only 1 person.
Makes data correct and clean
Your reports become accurate.
Handling Missing (NULL) Values
What is a missing (NULL) value?
A missing value means the cell is empty, blank, or has no information.
It is like:
• Leaving a question unanswered in a form
• A phone number missing
• A city name not provided
In Power Query, missing values appear as null.
Example: 10 Rows (with missing/null values)
Here is a table with 10 rows. Some rows have empty / missing values in Name, Age, or City:
Row Name Age City
1 Rahul 25 Delhi
2 Sneha Mumbai
3 22 Chennai
4 Arjun
5 Meera 24 Kolkata
6 Kiran 27
7 Rahul Hyderabad
8 30 Pune
9 Sneha 26 Mumbai
10 Arjun
In this 10-row table, you can see:
• Age is missing in rows: 2, 4, 7, 10
• Name is missing in rows: 3, 8
• City is missing in rows: 4, 6, 10
These empty spaces are missing/null values.
How to handle missing values in Power Query
You can handle missing values in common ways:
1. Remove rows with missing data
If the row is useless, delete it.
Steps:
• Home → Remove Rows → Remove Blank Rows
Example:
Row 4 and Row 10 (both missing Age + City) can be removed.
2. Replace missing values with something meaningful
Use:
Home → Replace Values
Example replacement:
• Missing Age → replace with 0 or Unknown
• Missing Name → replace with “Not Provided”
• Missing City → replace with “Unknown City”
Example:
Row 2 Age becomes: “Not Provided”
Correcting Inconsistent Data
What is inconsistent data?
Inconsistent data means the same information is written in different formats, even though it should
be the same.
It includes:
• Different spellings
• Different capital letters
• Extra spaces
• Different styles (Hyderabad / HYD / hyd)
This makes data confusing and inaccurate.
Example: 10 Rows (with inconsistent data)
Here is a table with 10 rows where the City column has many inconsistent formats:
Row Name City
1 Rahul hyderabad
2 Rahul Hyderabad
3 Rahul HYDERABAD
4 Sneha hyderabad
5 Sneha Hyderabad
6 Arjun HYdErAbAd
Row Name City
7 Arjun hyd
8 Meera HYDERABAD
9 Meera hyderbad
10 Kiran Hyderabad.
Problems you see here:
• Different capital letters (hyderabad / HYDERABAD / HYdErAbAd)
• Extra spaces (“ hyderabad”)
• Short forms (hyd)
• Spelling mistakes (hyderbad)
• Extra symbols (Hyderabad.)
This is inconsistent data.
Why correct inconsistent data?
Makes data uniform and clean
Everything looks the same and easier to understand.
Helps in accurate analysis
If "hyderabad" and "HYD" exist separately, Excel treats them as different cities, which is wrong.
Prevents mistakes in charts, counts, and reports
How to correct inconsistent data in Power Query?
Power Query has built-in tools to fix these easily.
1. Trim – Removes extra spaces
Example:
“ hyderabad ” → “hyderabad”
Power Query:
➡ Transform → Format → Trim
2. Clean – Removes hidden/invisible characters
Power Query:
➡ Transform → Format → Clean
3. Change case (lower, upper, proper)
Helps make all text look the same.
• Lowercase → hyderabad
• Uppercase → HYDERABAD
• Proper Case (Title Case) → Hyderabad
Best for city names: Proper Case.
Power Query:
➡ Transform → Format → Capitalize Each Word
4. Replace wrong spellings
You can manually fix spelling mistakes.
Example:
Replace "hyderbad" with "Hyderabad".
Power Query:
➡ Transform → Replace Values
5. Convert short forms to full names
Example:
• "hyd" → "Hyderabad"
Again:
➡ Transform → Replace Values
Result After Cleaning Inconsistent Data
After applying:
• Trim
• Clean
• Proper Case
• Replace Values
Your cleaned table becomes:
Name City
Rahul Hyderabad
Rahul Hyderabad
Rahul Hyderabad
Sneha Hyderabad
Sneha Hyderabad
Arjun Hyderabad
Arjun Hyderabad
Meera Hyderabad
Meera Hyderabad
Kiran Hyderabad
Now the data is:
• Clean
• Consistent
• Uniform
Perfect for analysis.
Text-to-Columns (Splitting Data)
What is Text-to-Columns?
Text-to-Columns is a feature that splits one column into multiple columns based on a separator
(comma, space, hyphen, etc.).
It is useful when:
• Full name and last name are in one cell
• City and State are in one cell
• Email contains username and domain
• Data is mixed together
Text-to-Columns helps you separate them neatly.
Example: 10 Rows Before Splitting
Here is a table with 10 rows, where Full Name is in one column:
Row Full Name
1 Rahul Sharma
2 Sneha Reddy
3 Arjun Kumar
4 Meera Singh
5 Kiran Varma
6 Lokesh Babu
7 Kavya Rao
8 Sunil Das
9 Priya Nair
10 Rohit Gupta
Currently, First Name + Last Name are combined in one cell.
Why use Text-to-Columns?
To separate data neatly
Example:
"Rahul Sharma" → Rahul | Sharma
To make analysis easy
You can search, filter, or count based on First Names or Last Names.
To remove confusion
Data becomes structured and readable.
How Text-to-Columns works?
It splits data based on:
• Space
• Comma
• Hyphen
• Semicolon
• Custom delimiter
Example:
Rahul,Sharma → split by comma
How to use Text-to-Columns in Power Query
In Power Query:
1. Select the column you want to split
2. Go to:
Home → Split Column → By Delimiter
3. Choose the delimiter (Space / Comma / Custom)
4. Choose how to separate (into rows or columns)
5. Click OK
Result After Splitting (Clean 10-Row Table)
Using Space as the delimiter, the result becomes:
Row First Name Last Name
1 Rahul Sharma
2 Sneha Reddy
3 Arjun Kumar
4 Meera Singh
5 Kiran Varma
6 Lokesh Babu
7 Kavya Rao
8 Sunil Das
Row First Name Last Name
9 Priya Nair
10 Rohit Gupta
Now the data is:
• Neat
• Separated
• Ready for analysis
Flash Fill (Magic Auto-Fill)
What is Flash Fill?
Flash Fill is an Excel feature that automatically detects patterns and fills the remaining cells for you.
It is like Excel saying:
“I understand what you are trying to do… let me complete the rest!”
You type one or two examples → Excel completes the whole column.
No formulas.
No coding.
Just pattern recognition.
Shortcut: Ctrl + E
When is Flash Fill used?
You can use Flash Fill to:
• Extract First Name from Full Name
• Extract Last Name
• Join text together
• Format phone numbers
• Separate email username and domain
• Convert text patterns automatically
Example: 10 Rows Before Flash Fill
Suppose you have 10 rows of Full Names, and you want to extract First Name only:
Row Full Name First Name (to extract)
1 Rahul Sharma
2 Sneha Reddy
3 Arjun Kumar
4 Meera Singh
5 Kiran Varma
6 Lokesh Babu
7 Kavya Rao
8 Sunil Das
9 Priya Nair
10 Rohit Gupta
How Flash Fill works (Step-by-Step)
Step 1:
In the first cell under First Name, type:
Rahul
Step 2:
Move to the next cell and press Ctrl + E.
Step 3:
Excel will automatically fill all remaining first names:
Row Full Name First Name
1 Rahul Sharma Rahul
2 Sneha Reddy Sneha
3 Arjun Kumar Arjun
4 Meera Singh Meera
5 Kiran Varma Kiran
Row Full Name First Name
6 Lokesh Babu Lokesh
7 Kavya Rao Kavya
8 Sunil Das Sunil
9 Priya Nair Priya
10 Rohit Gupta Rohit
Flash Fill learned the pattern and completed the work.
More Flash Fill Examples (Simple)
1. Extract Last Name
You type:
Sharma
Flash Fill fills:
• Reddy
• Kumar
• Singh
• Varma
• etc.
2. Join Two Columns
First Name: Rahul
Last Name: Sharma
You type:
Rahul Sharma
Flash Fill joins the rest.
3. Extract Username From Email
Email: [Link]@[Link]
You type:
[Link]
Flash Fill extracts usernames for all rows.
4. Format a Phone Number
Input:
9876543210
You type:
987-654-3210
Flash Fill formats all numbers the same way.