Power Query Data Transformation

By the end of this lesson, you will be able to write basic M language expressions in Power Query to clean and reshape data for efficient analysis in Power BI.

What it is

Power Query is a data connection technology that enables you to discover, connect, combine, and refine data across a wide variety of sources. The underlying language used by Power Query is called M. When you interact with the Power Query Editor interface, you are essentially generating M code steps. These steps transform raw data into a structured format suitable for visualization. Key concepts include queries (the transformation logic), steps (individual operations like filtering or renaming), and types (ensuring data integrity).

Why it matters

• Performance: Transforming data at the source reduces the load on the Power BI engine during report rendering.
• Reproducibility: M code records every step, allowing you to audit exactly how raw data became your final table.
• Automation: Once defined, transformations run automatically upon refresh, eliminating manual Excel cleanup tasks.
• Data Quality: You can enforce strict typing and remove nulls or duplicates before they corrupt visualizations.

Syntax or steps

M code follows a functional programming style where each step takes the previous result as input. A typical query starts with a source, followed by chained transformations. The general structure looks like this: let Source = ..., Step1 = ..., Result = ... in Result Common functions include Table.SelectRows, Table.RenameColumns, and Table.TransformColumnTypes.

Example

let
    // 1. Connect to CSV file
    Source = Csv.Document(File.Contents("C:\Data\Sales.csv"), [Delimiter=",", Columns=5]),
    
    // 2. Promote first row to headers
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    
    // 3. Filter out rows where SalesAmount is null
    FilteredRows = Table.SelectRows(PromotedHeaders, each ([SalesAmount] <> null)),
    
    // 4. Change column types for accuracy
    ChangedType = Table.TransformColumnTypes(FilteredRows, {{"Date", type date}, {"SalesAmount", Currency.Type}})
in
    ChangedType

Explanation:

• Csv.Document loads the raw text file into a generic table.
• Table.PromoteHeaders converts the first row into actual column names.
• Table.SelectRows uses an anonymous function (each) to keep only rows where SalesAmount is not empty.
• Table.TransformColumnTypes ensures dates are treated as dates and amounts as currency, enabling proper sorting and math later.

Common mistakes

• Hardcoding paths: Using absolute file paths breaks queries when shared. Use relative paths or parameters instead.
• Ignoring Type Errors: If a column contains mixed text and numbers, casting to type number may fail silently or throw errors. Always inspect data quality first.
• Over-filtering: Removing too many rows early can hide data issues. Keep a "staging" query if you need to debug why records disappeared.
• Case Sensitivity: M is case-sensitive. [SalesAmount] is different from [salesamount]. Ensure column names match exactly after promotion.

When to use it

Compare Power Query with DAX (Data Analysis Expressions):

Feature	Power Query (M)	DAX
Best For	Row-level cleaning, merging tables, changing data shape.	Aggregations, calculated columns, complex business logic.
Execution Time	During data refresh (ETL).	During report interaction (Query Engine).
Impact on Size	Reduces model size by removing unnecessary rows/columns.	Can increase memory usage if overused.

Use Power Query for structural changes and DAX for analytical calculations.

Practice

Guided Exercise: Open Power Query Editor. Load a sample CSV. Add a step to rename the column "OldName" to "NewName" using Table.RenameColumns. Challenge: Write a step to split a full name column ("John Doe") into two separate columns ("First Name", "Last Name") using Table.SplitColumn. Hint: Look up the syntax for Table.SplitColumn which requires a delimiter parameter.

Quick check

Question: Why should you perform data filtering in Power Query rather than using a filter visual in Power BI? Answer: Filtering in Power Query removes the data from the model entirely, reducing file size and improving performance. Filtering in a visual only hides data temporarily but keeps it loaded in memory.

Summary

Power Query transforms raw data into a clean, typed, and optimized state using the M language. By handling structural changes during ETL, you ensure faster report performance and maintainable data pipelines.