⚡ Power Query in Excel: Automate Data Cleaning

🔹 What is Power Query?

• Power Query is an ETL (Extract, Transform, Load) tool in Excel (also in Power BI).
• It helps you:
  • Import data from multiple sources (Excel, CSV, SQL, Web, etc.).
  • Clean and transform data (remove blanks, split columns, merge tables, etc.).
  • Automate repetitive tasks — once you build steps, you can refresh anytime to reapply them.

Shortcut to open: Data Tab → Get & Transform Data → Launch Power Query Editor.

---

🔹 Why Use Power Query for Data Cleaning?

1. Reproducible → Steps are recorded, no need to repeat manually.
2. Error Reduction → Automates processes, avoids human mistakes.
3. Time-Saving → One-click refresh updates transformed data.
4. Handles Large Data → Better than formulas for huge datasets.

---

🔹 Common Data Cleaning with Examples

1️⃣ Remove Duplicates

• Scenario: You have a sales list with repeated customer IDs.
• Power Query Step: Home → Remove Rows → Remove Duplicates.
• ✅ Result: Only unique records remain.

---

2️⃣ Remove Blank/Null Values

• Scenario: A dataset has missing entries in “Email” column.
• Step: Home → Remove Rows → Remove Blank Rows.
• ✅ Result: All empty records deleted.

---

3️⃣ Change Data Types

• Scenario: Date column imported as text.
• Step: Transform → Data Type → Date.
• ✅ Result: Column correctly recognized for calculations.

---

4️⃣ Split Column

• Scenario: “Full Name” column → “Himanshu Dhar”.
• Step: Home → Split Column → By Delimiter (Space).
• ✅ Result: First Name = Himanshu, Last Name = Dhar.

---

5️⃣ Merge Queries (Joins)

• Scenario: Two tables:
  • Table 1 → Customer details
  • Table 2 → Sales transactions
• Step: Home → Merge Queries → Match on Customer ID.
• ✅ Result: Combined dataset (like VLOOKUP but more powerful).

---

6️⃣ Append Queries

• Scenario: Monthly sales files Jan.xlsx, Feb.xlsx, Mar.xlsx.
• Step: Home → Append Queries → Stack them into one table.
• ✅ Result: One consolidated dataset.

---

7️⃣ Remove Columns / Keep Columns

• Scenario: You only need Customer Name & Sales Amount from 10-column table.
• Step: Home → Choose Columns → Select relevant ones.
• ✅ Result: Dataset trimmed to necessary info.

---

8️⃣ Unpivot Columns

• Scenario: Sales report: ProductJanFebMarLaptop100150120
• Step: Transform → Unpivot Columns.
• ✅ Result: ProductMonthSalesLaptopJan100LaptopFeb150LaptopMar120

---

9️⃣ Replace Values

• Scenario: Customer field has “NA” instead of blank.
• Step: Transform → Replace Values (“NA” → null).
• ✅ Result: Clean data with standard blanks.

---

🔟 Group Data (Summarization)

• Scenario: Sales by Region.
• Step: Home → Group By → Region → Sum of Sales.
• ✅ Result: Pivot-like summary inside Power Query.

---

🔹 Real-Life Example (End-to-End)

👉 Imagine you receive monthly sales files from different branches:

• Step 1: Import all files (Folder option).
• Step 2: Append Queries to combine them.
• Step 3: Remove duplicates and null values.
• Step 4: Split “Customer Name” into First/Last name.
• Step 5: Merge with Customer Master file for full details.
• Step 6: Unpivot Month columns for analysis.
• Step 7: Group data by Region → Total Sales.

Now, whenever new monthly files are added → just Refresh All → Data updates automatically. 🚀

---

🎯 10 Interview Questions & Answers on Power Query

Q1. What is Power Query in Excel?
👉 Power Query is a data connection and transformation tool that helps automate importing, cleaning, and reshaping data.

Q2. How is Power Query different from Excel formulas?
👉 Formulas work inside sheets, but Power Query builds step-by-step transformations that are refreshable and can handle large datasets more efficiently.

Q3. Can Power Query handle multiple file imports at once?
👉 Yes, using the Folder option you can import all Excel/CSV files from a directory and consolidate them.

Q4. What is the difference between Merge and Append in Power Query?
👉 Merge = Combine tables side by side (like JOIN/VLOOKUP).
👉 Append = Stack tables on top of each other (like UNION).

Q5. What is “Unpivot” in Power Query?
👉 Unpivot converts column headers into rows, making data tidy for analysis.

Q6. How do you handle missing or null values in Power Query?
👉 By removing rows, replacing null with default values, or filling down/up.

Q7. Can Power Query perform calculations?
👉 Yes, you can create Custom Columns using formulas in M language (Power Query’s scripting).

Q8. What is the difference between Power Query and Power Pivot?
👉 Power Query = Data Cleaning & Shaping.
👉 Power Pivot = Data Modeling & Analysis with DAX.

Q9. Is Power Query case sensitive?
👉 Yes, transformations and M language functions are case sensitive.

Q10. Give a practical example where you used Power Query.
👉 Example: Consolidating 12 monthly sales reports, cleaning customer names, and preparing a pivot-ready dataset that refreshes automatically.

---

✅ With this, you can confidently explain Power Query in interviews and also showcase practical knowledge.