One tool, mastered end to end.

1 Extract, Transform, Load

Extract connects to a source (a CSV, a database, an API). Transform is Power Query's job — fixing types, renaming columns, removing duplicates, splitting fields, filtering junk rows. Load sends the clean result into your data model.

2 Applied Steps

Every click you make in Power Query — change a type, rename a column, remove duplicates — becomes an Applied Step in a visible, re-orderable list on the right of the editor. That list is what makes a refresh work: next month's export gets the exact same cleanup replayed on it automatically.

3 Common transforms

Change Type → Whole Number / Decimal / Text / Date
Trim / Clean → strip whitespace and invisible characters
Split Column → e.g. "J. Ahmed" into First Name + Last Name
Remove Duplicates, Remove Rows With Errors
Unpivot Columns → turn wide monthly columns into tidy rows

These five cover the majority of real-world cleanup work — memorise the menu locations, not the theory.

4 Query folding

When your source is a database, Power Query can sometimes push your steps back to the database itself instead of pulling everything in first — this is query folding, and it's the difference between a refresh taking 3 seconds or 3 minutes.

1 Star schema

A fact table holds the events you're measuring — here, Jobs (one row per repair). Dimension tables describe things you group and filter by — here, Customers. Connect them with a relationship, and you have a star: the fact table in the middle, dimensions around it.

2 Cardinality & cross-filter direction

Jobs → Customers is many-to-one: many jobs can point at one customer. Cross-filter direction decides which side's filters flow through the relationship — usually single-direction, from the "one" side (Customers) down to the "many" side (Jobs).

3 Why not one big flat table?

You could copy each customer's name and city onto every one of their job rows. It would work — until a customer moves city and now half their old jobs say the wrong place. A modeled star schema stores each fact once, in one place.

1 Measures vs. Calculated Columns

A Measure calculates on the fly, based on whatever's currently filtered on screen. A Calculated Column computes once per row and stores that fixed value forever. Rule of thumb: if the number should react to filters/slicers, it's a Measure.

2 SUM vs. SUMX

Total Revenue = SUM(Jobs[Price])                  -- straight column total
Total Revenue (X) = SUMX(Jobs, Jobs[Price])        -- same result, evaluated row by row

SUM totals one column directly. SUMX (and the other X functions) walk the table row by row, evaluating an expression fresh each time — needed when what you're summing isn't a plain column, e.g. SUMX(Jobs, Jobs[Price] - Jobs[Cost]).

3 Row context vs. filter context

Row context is "which row am I on right now" — it exists inside iterators like SUMX. Filter context is "which rows are currently visible" — set by slicers, filters, and whatever's on the report page around your Measure. Most of what a Measure does is react to filter context.

1 CALCULATE fundamentals

High Value Revenue =
CALCULATE(
    SUM(Jobs[Price]),
    Jobs[Price] > 100
)

CALCULATE evaluates its first argument under a modified filter context — the extra arguments add, replace, or remove filters compared to what's currently on the report page.

2 ALL() — removing filters on purpose

Revenue % of Total =
DIVIDE(
    SUM(Jobs[Price]),
    CALCULATE(SUM(Jobs[Price]), ALL(Jobs))
)

ALL(Jobs) strips away whatever filters are currently applied to Jobs, giving you the unfiltered grand total to divide against — the classic "percent of total" pattern.

3 VAR ... RETURN

Margin % =
VAR TotalRevenue = SUM(Jobs[Price])
VAR TotalMargin = SUM(Jobs[Price]) - SUM(Jobs[Cost])
RETURN
    DIVIDE(TotalMargin, TotalRevenue)

Name an intermediate result once with VAR, then reuse it as many times as you like in RETURN — clearer to read, and often faster since it's calculated only once.

1 Choosing the right visual

Comparing categories → bar chart. Trend over time → line chart. One headline number → KPI card. Many categories of similar size → never a pie chart (past 4-5 slices they become unreadable — a sorted bar chart wins every time).

2 Slicers and layout

A slicer is a visible, clickable filter control on the page itself — no settings panel required. Put your KPI cards at the top (the headline), supporting charts below (the explanation) — match how people actually scan a page.

3 Bookmarks

A bookmark captures the current state of filters and visuals so a button can jump straight back to it — the basis of "guided" report navigation (e.g. a "Reset filters" button).

1 Workspaces vs. Apps

A Workspace is where you and collaborators build and edit. An App is the polished, read-only version you publish out to a wider audience — with its own curated navigation.

2 Scheduled refresh & gateways

The Service lives in the cloud; your source data might not. An on-premises data gateway is the secure bridge that lets a scheduled refresh reach into your private network to pull fresh data.

3 Row-Level Security

RLS lets you publish one report while each viewer only sees the rows relevant to them — a regional manager sees their region, not everyone's.