Power BI
Intermediate 14 min read Power BI

Power Query Fundamentals

The transformations you'll actually use daily

Power Query is a data transformation DSL with a visual UI and M code — merge/append queries, split columns, replace values, add columns, and guard against fragile, hidden steps.

Power QueryMmergeappendsplitcustom columnssteps
Prerequisites: Power BI Overview
Before we start

What you will learn

  • Perform merge, append, split, replace and custom-column operations
  • Read and edit generated M to fix tricky cases
  • Document steps so refreshes and audits stay sane
Introduction

What is this?

Power Query is Power BI's transformation engine: a visual UI over M, a functional language. Everyday operations — merge queries (joins), append (union), split columns, replace values, conditional and custom columns — translate to M expressions that run on the full dataset at every refresh. Understanding M turns 'works now' into 'works forever'.

Motivation

Why do you need it?

  • Merge/append are how you build one model table from many sources.
  • The applied-steps model makes every transform auditable and reorderable.
  • M is debuggable: see exactly which step corrupted a column.
  • Mastering it prevents the silent 'refresh changed my data' disasters.
Think about it

Mental model

Imagine

A chef's mise en place checklist

Think of

Power Query steps

Each step — wash, cube, blanch — is recorded in order. Skip or reorder and the dish changes. You tweak a step, the whole menu's output shifts predictably, and you can always replay last night's exact procedure.

Mechanics

How it works

  • Queries are expressions reducing to a table; applied steps chain M expressions.
  • Merge performs a relational join with a chosen kind (left outer, full, inner) and key(s).
  • Append stacks tables with matching headers (union semantics).
  • Split/conditional/custom columns add or reshape columns via M functions.
  • Parameterization makes queries reusable (tables, letters, thresholds).

A Power Query step pipeline: shape, then load.

Source
Filter/split
Merge/append
Custom columns
Load
Hands-on

Examples

M for a merge and a conditional column

M for a merge and a conditional column
let
  Source   = Sql.Database("srv","dw"),
  Sales    = Source{[Schema="gold",Item="fact_sales"]}[Data],
  Cust     = Source{[Schema="gold",Item="dim_customer"]}[Data],
  Merged   = Table.NestedJoin(
               Sales, {"cust_key"},
               Cust, {"cust_key"},
               "Cust", JoinKind.LeftOuter),
  Expanded = Table.ExpandTableColumn(Merged, "Cust", {"region","tier"}),
  Flagged  = Table.AddColumn(Expanded, "is_high_value",
               each if [amount] >= 500 then "high" else "low")
in
  Flagged

A left join on the customer key, then attributes expanded, then a conditional column — the standard build of a model-ready import.

Real world

In production

  • A report merges two exported CSVs on an order key to avoid a warehouse round-trip for a small join.
  • An appender union's monthly export files, handling header normalization first.
  • A parameterized query lets the team switch the target month without editing M by hand.
Guidelines

Best practices

  • Do merges on the source or in the model, not in Power Query, when the source can.
  • Expand only needed columns from a merge (unchecked lists bloat memory).
  • Name steps and keep them lean; every step costs transform time.
  • Use parameters over hard-coded literals; document non-obvious logic in the step name.
Watch out

Common mistakes

  • Merging in Power Query when a warehouse view is cheaper — lost folding.
  • Expanding all merge columns, doubling memory per refresh.
  • Hidden steps (obsolete 'remove other columns') that corrupt later edits.
  • Using dynamic column numbers with labels that drift between exports.
Root cause analysis

Common issues

Error
Append fails on header mismatch
Root cause

One file's column order/name drifted

Fix

Normalize per-file headers (rename/promote) before appending.

Error
Merge multiplies rows
Root cause

Right-side key not unique

Fix

Dedupe the right table or change the join to match grain.

When things break

Debugging tips

  • Wrong after refresh? Step through Applied Steps and inspect the changed column at each dot.
  • Merge duplicates? Check join kind and key uniqueness on the right side.
  • Custom column errors visible? Show the error column to find offending rows.
Interviews

Interview checkpoint

Practice

Hands-on lab

Merge and Append with M Semantics
40 min · intermediate
In progress
Objectives
  • Replicate merge and append in SQLite
  • Spot the row-multiplication trap in a merge
  • Normalize headers before an append

Merge and Append with M Semantics

40 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which operation stacks two tables with the same shape vertically?

2

Every applied step in Power Query is reversible and reorderable.

Go deeper

Official documentation

Power Query M function reference
docs