Back to all blogs
    6 min read

    🧹 Power Query and Data Cleaning

    #powerbi#power-query#data-cleaning#etl

    🧹 The Prep Kitchen: Power Query Editor

    Now that you have connected to your data (as we did in 02 - Data Connectivity and Storage Modes), you will often find that raw data is messy. It might have blank rows, misspelled names, or dates formatted as text.

    You cannot cook a great meal with dirty ingredients. This is where Power Query comes in.

    What is Power Query Editor? Power Query is a separate window that opens on top of Power BI Desktop. It is the built-in data transformation and preparation engine. You use it to perform ETL:

    • Extract: Pulling the data from your source.
    • Transform: Cleaning, splitting, and filtering the data.
    • Load: Pushing the cleaned data into the main Power BI Data Model.

    ⚙️ The Anatomy of Power Query

    When you click "Transform Data", the Power Query Editor opens. It has three main areas:

    1. Queries Pane (Left Side): Lists all the tables (queries) you have imported.
    2. Data Preview (Center): A spreadsheet-like view showing exactly what your data looks like right now.
    3. Query Settings & Applied Steps (Right Side): This is the most important feature! Power Query records every single click you make (e.g., "Removed Column", "Filtered Rows") as an Applied Step.
      • Why is this amazing? You never write over the original data. If you make a mistake, you just click the 'X' next to the step to undo it. And when your data refreshes next month, Power Query will automatically replay all your recorded steps!

    🕵️‍♂️ Data Profiling: Inspecting Your Data

    Before you clean data, you must understand what is broken. Power Query has three built-in "Profiling" tools that act like an X-ray for your columns (Found under the View ribbon):

    Profiling ToolWhat it tells you
    Column QualityShows percentages of Valid (🟢 Green), Error (🔴 Red), Empty/Null (⚫ Dark Grey), Unknown (🟢 Dashed Green), and Unexpected Error (🔴 Dashed Red) values.
    Column DistributionA mini bar chart showing the frequency of values. Tells you the number of Distinct (total unique) and Unique (values that appear exactly once) items.
    Column ProfileProvides deep statistics at the bottom of the screen (Min, Max, Average, Standard Deviation) for the selected column.
    The Top 1000 Rows Trap

    By default, to save memory, Power Query only profiles the first 1,000 rows of your data. If your errors are hiding in row 5,000, you won't see them! Look at the bottom-left corner of the screen and click "Column profiling based on top 1000 rows" and change it to "entire data set".

    🧽 Common Cleaning Transformations

    Here are the most common tasks you will perform in the prep kitchen:

    1. Cleaning Rows

    • Handle Nulls/Empties: Use the filter drop-down on a column to uncheck (null) to filter out blank rows.
    • Remove Duplicates: Right-click a column header and select "Remove Duplicates" to keep only unique records (great for Customer ID tables).
    • Remove Errors: If a text value accidentally gets mixed into a number column, it will show as an Error. Right-click to "Remove Errors" or "Replace Errors" with a default number like 0.
    • Sort Rows: Click the arrow on a column header to sort A-Z or Ascending/Descending.

    2. Cleaning Columns

    • Correct Data Types: Click the tiny icon on the left side of the column header (e.g., ABC or 123). Make sure dates are set to Date 🗓️ and money is set to Fixed Decimal Number 💲.
    • Rename, Remove & Reorder: Double-click a header to rename it. Right-click and select "Remove" to delete columns. Click and drag a column header to reorder it.
    • Trim, Clean & Extract: Right-click a text column -> Transform -> Trim. This removes invisible leading and trailing spaces. You can also use "Extract" to pull specific text (like everything before a delimiter).
    • Replace Values: Right-click a column and select "Replace Values" to swap out a specific word or number across the entire column.

    3. Advanced Transformations

    • Split Column: Split a "First/Last Name" column into two columns using a space delimiter.
    • Merge Columns: Highlight two columns, right-click, and "Merge Columns" to combine them.
    • Add Index Column: Creates a new column starting from 0 or 1, giving every row a unique ID number.
    • Custom & Conditional Columns: Add a "Conditional Column" to create an IF/THEN rule (e.g., IF [Age] > 65 THEN "Senior" ELSE "Adult"). Add a "Custom Column" to write your own math/logic formula across columns.
    • Group By: Similar to an Excel Pivot Table. Group your data (e.g., group by "Country") and aggregate a column (e.g., Sum of Sales).

    🏋️ Practice Drill

    Let's perform some ETL!

    Task:

    text
    // Try answering these:
1. Remember that Excel file you connected to in the previous chapter's drill?
2. Open Power BI Desktop, click **Get Data**, select that Excel file, and click the sheet name.
3. This time, instead of Load, click **Transform Data**.
4. Welcome to the Power Query Editor!
5. Go to the **View** tab at the top and turn on **Column Quality** and **Column Distribution**.
6. Try double-clicking a column header to rename it. Notice how a new step appears in the "Applied Steps" pane on the right!
    💡 Click for Solutions
    • Did you see the new window open? (Power Query runs in its own separate window).
    • Did you see the green bars appear under your column headers when you turned on Column Quality?
    • Did you see the "Renamed Columns" step appear on the right? If you click the 'X' next to that step, the name will instantly revert to the original!

    If you successfully renamed a column and saw the Applied Step generated, you have mastered the basics of Power Query! When you are done, always click "Close & Apply" in the Home ribbon to push the cleaned data into the main Power BI Desktop model.

    ← 🔌 Data Connectivity and Storage Modes | Next Topic → 🔀 Combining and Reshaping Data