CH
Coding Hub
Power BIBeginner9 min read read

Power Query Basics: Data Cleaning and Transformation

Learn ETL fundamentals in Power BI and Excel to reshape messy spreadsheets into analysis-ready models.

Coding Hub Technical Editorial Team(Software Engineering & Education)
•
Updated 2026-03-14

Overview & Context

Real-world business data is notoriously dirty: header rows are misplaced, numbers are stored as text strings, date formats are inconsistent, and spreadsheets are shaped for human printing rather than database analysis. Power Query is the transformation engine embedded inside Power BI and Excel. It records every cleaning action you take as a repeatable recipe, automating your data prep forever.

What You Will Learn
Understand the Extract, Transform, Load (ETL) pipeline in Power Query
Navigate the Power Query interface, Ribbon, and Applied Steps timeline
Clean dirty datasets: remove empty rows, trim whitespace, and enforce data types
Master the Unpivot Columns operation to normalize wide spreadsheet data
Understand how the underlying M language powers transformation steps
Table of Contents

1. The Applied Steps Mechanism

A visual, repeatable transformation recipe

In traditional spreadsheet workflows, cleaning data involves manual cell deletion that must be redone every week when fresh data arrives.

In Power Query, every button you click—whether removing a column, replacing a null value, or changing a data type—is appended as a sequential step in the **Applied Steps** pane on the right. When your source file updates, simply click **Refresh** and Power Query automatically executes the entire transformation pipeline in seconds.

2. Essential Data Cleaning Operations

1. **Use First Row as Headers:** Promotes the top row of data into column titles.

2. **Remove Top / Empty Rows:** Strips out spreadsheet title banners and blank spacer rows.

3. **Change Data Type:** Explicitly set columns to `Decimal Number`, `Whole Number`, `Text`, or `Date`. Leaving columns typed as `Any` causes severe memory bloat and prevents calculations.

4. **Text Cleaning (Trim & Clean):** Strips leading/trailing spaces and non-printable carriage returns.

3. Unpivoting: The Most Important Power Query Skill

Converting wide matrix tables into tall normalized datasets

Spreadsheets often display dates across columns (e.g. `Student`, `Jan`, `Feb`, `Mar`). This wide format is great for reading on paper, but relational engines like Power BI cannot filter or aggregate across multiple columns.

By selecting the date columns and clicking **Transform → Unpivot Columns**, Power Query instantly reshapes the data into two columns: `Attribute` (the month) and `Value` (the score). Now you have a normalized tall table ready for calculations.

4. Understanding the M Formula Language

Behind every visual button in Power Query lies code written in **M** (Power Query Formula Language). You can inspect the entire M script by opening the **Advanced Editor** in the Home ribbon.

powerquery
let
    // Step 1: Connect to source Excel spreadsheet
    Source = Excel.Workbook(File.Contents("C:\Data\StudentGrades.xlsx"), null, true),
    Grades_Sheet = Source{[Item="Grades",Kind="Sheet"]}[Data],

    // Step 2: Promote headers and set explicit types
    #"Promoted Headers" = Table.PromoteHeaders(Grades_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
        {"StudentID", Int64.Type},
        {"StudentName", type text},
        {"SemesterGPA", type number},
        {"EnrollmentDate", type date}
    }),

    // Step 3: Remove records with empty GPA
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [SemesterGPA] <> null)
in
    #"Filtered Rows"
Key note: Every step in M takes the output table of the preceding step as its input parameter, constructing a functional transformation pipeline.

5. Power Query Performance Rules

- Remove unneeded columns as early as possible in your steps. Every column loaded into memory increases file size.

- Filter out historical rows you do not need before applying complex transformations.

- Leverage **Query Folding**: when connecting to SQL databases, Power Query translates M steps back into native SQL `SELECT` statements, executing the heavy work on the database server.

Summary & Key Takeaways

  • 1Power Query is an ETL tool that records repeatable data cleaning recipes.
  • 2Applied Steps execute sequentially on every data refresh.
  • 3Unpivot wide cross-tabulated spreadsheets into tall normalized tables.
  • 4Remove unneeded columns early to maximize VertiPaq compression.
Frequently Asked Questions

Can I use Power Query in Microsoft Excel?

Yes. In Excel for Windows, Power Query is built natively into the Data ribbon under the 'Get & Transform Data' group.

What is Query Folding?

Query Folding is an optimization where Power Query translates transformations (like filtering and joining) into native SQL queries executed directly on the source database server before data is transferred to your local machine.