A missing salary doesn't mean Salary = 0, it means it wasn't provided.
1️⃣4️⃣ Replace Values
Standardize inconsistent entries:
North, NORTH, north, N → North
Use: Replace Values
1️⃣5️⃣ Trim and Clean Text
Transform " John Smith " → "John Smith"
Trim whitespace, Clean non-printing characters, Change case
1️⃣6️⃣ Split Columns
John-Smith → First Name: John, Last Name: Smith
Use: Split Column → By Delimiter → "-"
1️⃣7️⃣ Merge Columns
John + Smith → John Smith
Use: Merge Columns with space separator
2️⃣0️⃣ Add Custom Columns
Sales: 100,000, Cost: 70,000 → Profit = Sales - Cost = 30,000
Profit Margin = Profit / Sales
2️⃣1️⃣ Conditional Columns
IF Sales >= 100000 THEN "High" ELSE IF Sales >= 50000 THEN "Medium" ELSE "Low"
Similar to Excel's IF()
2️⃣2️⃣ Merge Queries (The most important concept)
Sales: Product ID, Sales
Products: Product ID, Product, Category
Use: Merge Queries → Match Product ID → This is like a JOIN in SQL.
SQL: SELECT * FROM Sales LEFT JOIN Products ON Sales.ProductID = Products.ProductID;
2️⃣3️⃣ Append Queries
Merge = Add columns by matching keys
Append = Add rows by stacking
Jan (1001, 1002) + Feb (1003, 1004) → 1001, 1002, 1003, 1004
2️⃣4️⃣ Group By
Region: North 50K, North 70K → Group by Region, Sum Sales → North 120K
Similar to SQL GROUP BY
2️⃣5️⃣ Pivot and Unpivot
This is critical for reports designed for humans:
Before:
Region | Jan | Feb | Mar
North | 50K | 60K | 70K
After Unpivot:
Region | Month | Sales
North | Jan | 50K
North | Feb | 60K
This structure is much better for analysis.
2️⃣6️⃣ Applied Steps = Your Superpower
Source → Changed Type → Removed Columns → Trimmed Text → Removed Duplicates → Filtered Rows → Added Profit → Merged Products
When new data arrives, just Refresh.
🧪 Practical Interview Challenge
Messy file with: Duplicate Order IDs, Extra spaces, Sales as text, Missing regions, Product info in another file
Strong approach:
1. Import into Power Query
2. Set correct data types
3. Trim and clean text
4. Investigate duplicates
5. Handle missing regions per business rules
6. Merge Product lookup table
7. Add Profit column
8. Filter invalid records
9. Review Applied Steps
10. Load cleaned dataset
🏆 Key Lesson
Instead of: > "How do I clean this file?"
Think: > "How do I build a repeatable process that cleans this type of data every time?"
That's the difference between manually manipulating spreadsheets and building a professional analytics workflow.
Remember:
Merge = Add columns by matching data
Append = Add rows
Group By = Summarize
Unpivot = Convert columns into rows
Applied Steps = Record your process
Refresh = Run the process again
Double Tap ❤️ For Part-11
8
1August 28, 2026 2K 8
