Power BI
Intermediate 13 min read Power BI

Power Query Transformations

Clean data once at the door instead of bandaging it in measures

Power Query (M) is the ingestion layer: promote headers, split, unpivot, merge and fold queries back into the source. Every transform is a step you can review and rename.

M languagePower Queryunpivotmergepromote headersquery foldingincremental refresh
Prerequisites: Power BI Fundamentals
Before we start

What you will learn

  • Read a Power Query step list and its M code
  • Unpivot a cross-tab into tidy rows
  • Explain query folding and why pushing to the source is fast
Introduction

What is this?

Power Query is the extract-transform-load step of a Power BI solution, written in the M language. Each transformation appears as an applied step you can reorder, rename or edit. Good practice pushes heavy work into the source (query folding) so only the results travel into the model.

Motivation

Why do you need it?

  • The model should be small and clean — Power Query is where dirt dies, not in DAX.
  • Steps are documented, re-runnable transformations instead of one-off scripts.
  • Folding lets SQL/Power BI push filtering and joins to the database, saving gigabytes of transfer.
  • Business users can audit 'what happened to this column' by reading the step list.
Think about it

Mental model

Imagine

A conveyor belt with workstations

Think of

The Power Query step list

Each workstation (step) moves the same batch: peel, chop, wash, pack. You can inspect the -peel- station alone, reorder stations, or remove 'wash' if the results change. The belt always shows the batch in transformed order.

Mechanics

How it works

  • A source connection opens (file, database, API, folder).
  • Your first step usually promotes headers and sets type for each column.
  • Every following action appends a step capturing exactly what changed.
  • Merge replicates a SQL join; Append concatenates tables vertically.
  • Folding translates the final M into the source language when the source allows it.
  • Each refresh re-runs the whole belt from the source snapshot.

A typical Power Query pipeline.

Source
Header/type
Filter/rename
Unpivot/Merge
Load
Hands-on

Examples

Unpivot a cross-tab with M

Unpivot a cross-tab with M
let
    Source = Csv.Document(File.Contents("sales_wide.csv")),
    Promote = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    Typed  = Table.TransformColumnTypes(Promote,
                 {"region", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}),
    Long = Table.Unpivot(Typed, {"region"}, "month", "amount")
in
    Long

The sample reads a region x month cross-table, fixes headers and types, then unpivots the month columns into a tidy region/month/amount long format that DAX and time intelligence can consume directly.

Real world

In production

  • A finance team unpivots raw month-wide columns into long-form rows so DAX time logic works — done once in Power Query, not repeatedly in every measure.
  • An operations pipeline merges a 20M-row log table with a dimension in SQL Server so folding keeps transfers tiny.
  • A catalog job strips malformed product names and re-ranges data types with a run-time step guard row count.
Guidelines

Best practices

  • Promote headers and set types in the first two steps for every import.
  • Rename steps with purpose (e.g. 'remove null stores') instead of 'Changed Type 2'.
  • Prefer folding-capable sources (SQL, warehouse) over local files for big data.
  • Validate final row count with a step that fails loudly if it drifts.
Watch out

Common mistakes

  • Loading raw wide data and 'fixing' it in the report with transforms hidden in DAX.
  • Merging in Power Query when the database view could do it — folding opportunity lost.
  • Renaming source columns so field names in the model drift silently week over week.
  • Building one monster query instead of a folder of small, testable queries.
Root cause analysis

Common issues

Error
Import hangs on a 10GB CSV
Root cause

No folding possible on flat files; import streams everything

Fix

Shift the transform into SQL or use an incremental-refresh partition.

Error
Merge returns null keys after refresh
Root cause

Trailing spaces or mixed types in the join columns

Fix

Trim, uppercase and coerce both key columns in shared steps.

Error
Web/API import breaks each month
Root cause

The response schema changes and the API output column vanished

Fix

Add a schema-validation step that errors clearly when a column is missing.

When things break

Debugging tips

  • Read the applied step list from bottom to top to trace which step broke a column.
  • Use Data Preview / Step Diagnostics to compare a column before and after one step.
  • Check whether the query folded by viewing the native query in the query editor.
  • Replicate a problem with a 10-row head extraction before debugging the full file.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which Power Query operation converts wide month-columns into long rows?

2

Merging in Power Query always sends the data to your local machine first.

Go deeper

Official documentation

Power Query M reference
docs
Report performance in Power Query
docs