Training By Himanshu

Tag: Power Query

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

    ⚡ 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

  • 1-Day Excel Interview Prep Plan: How to Master Key Skills Overnight

    If you have just one day to prepare for an Excel-related interview, your goal isn’t to learn everything — it’s to refresh the essentials, cover high-frequency questions, and get hands-on practice so you can answer with confidence.

    Here’s a step-by-step crash plan (8–10 hours total):

    ⏰ Hour 1: Understand the Job Role

    • Check the job description → Which Excel skills do they want? (e.g., data analysis, reporting, dashboards, VBA, Power Query).
    • Identify focus areas → If it says MIS, focus more on reporting formulas. If Data Analyst, focus more on lookup, filters, and pivot tables.
    • Quickly note down:
      • Core functions mentioned
      • Tools (Pivot Table, Power Query, Macros, SQL, etc.)
      • Business context (sales reports, financial data, etc.)

    ⏰ Hours 2–4: Formula Mastery

    Focus on 10–12 key formulas you will almost certainly be tested on:

    Formula / FunctionWhy ImportantQuick Example
    VLOOKUP / XLOOKUPMerge datasets, fetch related data=XLOOKUP(101, A2:A100, B2:B100, "Not Found")
    INDEX + MATCHFlexible lookups=INDEX(Sales, MATCH("Apple", Product, 0))
    IF + IFSConditional logic=IF(B2>5000,"High","Low")
    SUMIF / SUMIFSConditional totals=SUMIFS(Sales, Region, "East", Product, "Apple")
    COUNTIF / COUNTIFSCount with conditions=COUNTIFS(Region,"West", Sales, ">5000")
    TEXT functions (LEFT, RIGHT, MID, TRIM, LEN)Clean & extract text=LEFT(A2,5)
    FILTERDynamic filtering=FILTER(A2:D100, Region="North")
    UNIQUERemove duplicates=UNIQUE(Product)
    Date functions (YEAR, MONTH, EOMONTH, TEXT)Date-based analysis=TEXT(A2,"MMM-YYYY")

    Action:

    • Open Excel and type small practice datasets (10–15 rows).
    • Try each formula 3–4 times until you can do it without looking up syntax.

    ⏰ Hours 5–6: Pivot Tables & Data Cleaning

    • Create 2–3 quick Pivot Tables:
      • Sales by Region and Month
      • Top 5 products by revenue
    • Practice:
      • Sorting, filtering
      • Grouping dates
      • Adding calculated fields
    • In Power Query:
      • Remove duplicates
      • Split columns
      • Change data types
      • Merge two tables

    ⏰ Hours 7–8: Practice Real Problems

    • Download any sample dataset (e.g., sales data, HR data from Kaggle or random CSV).
    • Do these exercises:
      • Find top performer by sales
      • Monthly sales trend
      • Count customers who purchased more than 3 times
      • Merge customer table with orders table
      • Create a simple dashboard (Pivot + Slicer)

    ⏰ Hour 9: Review Common Interview Questions

    Technical Qs:

    1. Difference between VLOOKUP and INDEX+MATCH?
    2. How to remove duplicates without affecting original data?
    3. How do you handle missing data in Excel?
    4. How to extract month name from a date?
    5. What is the difference between Absolute and Relative cell references?

    Scenario Qs:

    1. “You have sales data; find the top 3 regions by revenue.”
    2. “Find customers who purchased in Jan but not in Feb.”
    3. “Your report shows wrong totals—how do you troubleshoot?”

    ⏰ Hour 10: Mock Drill

    • Set a 30-min timer.
    • Ask a friend (or yourself) to give you 5 tasks on a dataset.
    • Solve them without Google — this simulates test conditions.
    • After the drill, check your answers and note mistakes.

    💡 Last-Minute Tips for the Interview

    • Think out loud → Even if you don’t know the answer, walk through your approach.
    • Show shortcut keys (Ctrl+T for tables, Alt+N+V for Pivot Tables) — looks impressive.
    • Focus on accuracy first, speed later — wrong answers ruin trust.

    Featured products

  • Top 10 Excel Functions Every Data Analyst Must Master

    When Rohan, a 26-year-old commerce graduate from Pune, started preparing for his first data analyst interview, he quickly realized one thing – Excel is not just a spreadsheet tool, it’s a career-making skill.

    He had always used Excel for basic sums and formatting, but during mock interviews, he froze when asked,

    “Can you combine INDEX and MATCH to find a sales figure for a product in a given month?”

    That day, Rohan decided – No more guesswork. I will master the top Excel functions recruiters expect.
    Here’s what he learned, with examples from his practice sessions.

    1. VLOOKUP / XLOOKUP – Rohan’s ‘Data Detective’ Tool

    One day, Rohan had two datasets – one with Product Names, another with Sales Values.
    Instead of scrolling endlessly, he used:

    =XLOOKUP("Mango Juice", A2:A100, B2:B100, "Not Found")

    Result: Sales value for Mango Juice in seconds.
    Lesson: Lookup functions save hours in data matching.

    2. INDEX + MATCH – Rohan’s Upgrade

    During an interview test, the product name was in column C, and sales were in column A.
    VLOOKUP couldn’t help (it needs the lookup column first).
    Rohan used:

    =INDEX(A2:A100, MATCH("Mango Juice", C2:C100, 0))

    Lesson: INDEX+MATCH works in any direction and is interview gold.

    3. TEXT Functions – Cleaning Rohan’s Messy Data

    His dataset had customer IDs like " AB1234 " with spaces.
    He cleaned it using:

    =TRIM(A2)

    And extracted first 2 letters for state code:

    =LEFT(A2, 2)

    Lesson: TEXT functions like LEFT, RIGHT, MID, TRIM, and LEN are must-haves for messy datasets.

    4. IF + IFS – Decision Maker

    When given sales targets, Rohan categorized them:

    =IF(B2>=100000, "Top Performer", "Needs Improvement")

    For multiple conditions:

    =IFS(B2>=100000, "Top Performer", B2>=50000, "Average", TRUE, "Low")

    Lesson: IF helps classify data instantly.

    5. SUMIF / SUMIFS – Finding Patterns

    To know the total sales for “Mango Juice” in the “East” region:

    =SUMIFS(Sales, Product, "Mango Juice", Region, "East")

    Lesson: SUMIFS is perfect for quick conditional aggregations.

    6. COUNTIF / COUNTIFS – Counting What Matters

    In one dataset, Rohan needed to know how many orders were above ₹5,000:

    =COUNTIF(Sales, ">5000")

    Lesson: COUNT functions are quick ways to spot trends in large datasets.

    7. FILTER – Rohan’s Shortcut to Relevant Data

    Instead of applying Excel’s manual filter, Rohan extracted all sales for the “North” region with:

    =FILTER(A2:D100, Region="North")

    Lesson: Dynamic, criteria-based extraction beats manual filtering.

    8. UNIQUE – Finding Distinct Customers

    When asked for the number of unique buyers, Rohan did:

    =UNIQUE(CustomerName)

    Lesson: UNIQUE quickly deduplicates lists for better analysis.

    9. Date Functions – Time Travel in Excel

    Rohan needed monthly trends. He used:

    =TEXT(OrderDate, "MMM-YYYY")

    For month-end date:

    =EOMONTH(OrderDate, 0)

    Lesson: Date functions help slice and dice time-based data.

    10. Power Query + Power Pivot – Rohan’s Secret Weapon

    By now, Rohan could clean data in Power Query, load millions of rows, and use DAX for calculated measures.
    In one interview, he impressed the panel by transforming raw CSV files into a dashboard-ready table in 10 minutes.

    Rohan’s Takeaway

    “Excel isn’t about knowing formulas by heart—it’s about knowing which function to use when, and how to combine them.”

    Master these 10 functions, and you’re not just prepared for a data analyst job—you’re prepared for real-world problem solving.

    Top rated products