Power Query for Data Cleaning in Excel: Complete Guide with Examples

—

by

Himanshu
in

⚡ 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.

Featured products