Training By Himanshu

Tag: Data Cleaning in Excel

  • 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

  • UNIQUE Function in Excel 365 – Explained with Examples

    The UNIQUE function in Excel 365 is a game-changing tool that helps you extract distinct or unique values from a list or range — instantly and dynamically.

    It’s part of Excel’s new dynamic array functions, introduced in Excel 365 and Excel for the web.

    📘 What is the UNIQUE Function?

    The UNIQUE function returns a list of unique values from a range, array, or table column. It removes duplicates automatically and updates dynamically if the source data changes.

    🧪 Syntax

    UNIQUE(array, [by_col], [exactly_once])
    ArgumentDescription
    arrayThe range or array to extract unique values from
    [by_col]Optional. Use TRUE for column-wise, FALSE (default) for row-wise
    [exactly_once]Optional. If TRUE, returns values that appear only once

    ✅ Example 1: Basic Unique List

    List of names in A2:A10:

    Ravi  
Priya  
Ravi  
Neha  
Priya  
Amit

    In another cell:

    =UNIQUE(A2:A10)

    ✅ Output:

    Ravi  
Priya  
Neha  
Amit

    ✅ Example 2: Unique Values That Appear Only Once

    Same list, but only those that appear exactly once:

    =UNIQUE(A2:A10,,TRUE)

    ✅ Output:

    Neha  
Amit

    ✅ Example 3: Unique Rows from a Table

    If you have multiple columns like Name and Department:

    NameDept
    RaviSales
    NehaHR
    RaviSales
    AmitFinance

    Use:

    =UNIQUE(A2:B5)

    ✅ Output:

    Ravi  Sales  
Neha  HR  
Amit  Finance

    🔄 Dynamic Behavior

    When new values are added to the source range, the UNIQUE function automatically updates its results — no manual refresh needed.

    🧠 Real-Life Use Cases

    • Remove duplicate customer names or email addresses
    • Count unique product types in sales data
    • Build dynamic dropdown lists with Data Validation
    • Filter one-time entries from logs or records

    🎓 Learn More Excel Magic

    Functions like UNIQUE, FILTER, SORT, XLOOKUP, and LAMBDA can transform your workflow.

    📘 For step-by-step guidance, real-world dashboards, and hands-on Excel automation:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Covers:

    • Dynamic Array Functions
    • Excel 365 Exclusive Features
    • Dashboard Projects
    • Form Controls, VBA & More

    🎯 Click Here to Enroll Now

    On sale products

  • Mastering Text Manipulation with RIGHT Function

    Mastering Text Manipulation with RIGHT Function

    The RIGHT function is another essential text manipulation function commonly used in programming languages and spreadsheet applications like Excel. Its primary purpose is to extract a specified number of characters from the right side of a text string. Similar to the LEFT function, the RIGHT function has a straightforward syntax, involving the text string and the number of characters to be extracted from the right.

    Syntax:

    RIGHT(text, num_chars)
    • text: This is the text string from which you want to extract characters.
    • num_chars: This argument specifies the number of characters to extract from the right side of the text string.

    Basic Usage:

    Let’s start by looking at the basic usage of the RIGHT function with a simple example.

    Example 1:

    =RIGHT("Hello, World!", 6)

    In this example, the text string is “Hello, World!” and we want to extract the last 6 characters from the right. The result of this formula would be “World!”

    Explanation:

    • The RIGHT function takes the text string “Hello, World!” as its first argument.
    • The second argument, 6, specifies that we want to extract 6 characters from the right side of the text.
    • As a result, the function returns “World!”, which is the last 6 characters of the input text.

    Additional Parameters:

    Similar to the LEFT function, the RIGHT function can also be used with dynamic or variable values. For example, you can reference a cell that contains the desired number of characters to be extracted.

    Example 2:

    =RIGHT(A1, B1)

    In this case:

    • A1 contains the text string, e.g., “Data Processing.”
    • B1 contains the number of characters to be extracted, e.g., 5.

    The result of this formula would be “ssing.”

    Nesting RIGHT Function:

    The RIGHT function can be nested within other functions to perform more complex text manipulations. Similar to the LEFT function, nesting involves using the result of one RIGHT function as the input for another.

    Example 3:

    =RIGHT(LEFT("Nested Example", 6), 4)

    In this example:

    • The inner LEFT function extracts the first 6 characters from the text “Nested Example,” resulting in “Nested.”
    • The outer RIGHT function then extracts the last 4 characters from the result of the inner function.
    • The final result is “sted.”

    Practical Examples with Nesting:

    Example 4:

    =RIGHT(CONCATENATE("First", " ", "Last"), 5)

    Here, the CONCATENATE function combines the strings “First” and “Last” with a space in between. The RIGHT function then extracts the last 5 characters from the concatenated result. The output is ” Last.”

    Example 5:

    =RIGHT(MID("Nested Example", 3, 6), 4)

    In this case:

    • The MID function extracts a substring from “Nested Example” starting from the 3rd character and spanning 6 characters, resulting in “sted E.”
    • The RIGHT function then extracts the last 4 characters from the result of the MID function, yielding “E.”

    Example 6:

    =RIGHT(IF(A1="Condition", "True Result", "False Result"), 6)

    Here, the IF function checks a condition in cell A1. If the condition is true, it returns “True Result”; otherwise, it returns “False Result.” The RIGHT function then extracts the last 6 characters from the result of the IF function.

    Summary:

    In summary, the RIGHT function is a powerful tool for text manipulation, complementing the capabilities of the LEFT function. Its ability to extract a specified number of characters from the right side of a text string makes it versatile for various tasks such as data cleaning, formatting, and analysis. Much like the LEFT function, when combined with other functions and nested within formulas, the RIGHT function can be part of more advanced and customized text processing operations. These examples illustrate its practical use in both basic and nested scenarios, showcasing its flexibility and utility in handling text data.