Applied Steps and Query Settings — Power Query (Surgical, Slightly Theatrical)

You've already learned how to connect to data sources and dodge authentication gremlins. Now we take the data you hooked up and actually shape it — not with duct tape, but with the clean, auditable choreography of Power Query's Applied Steps and Query Settings.

"Applied Steps are your transformation scrapbook — every tweak, every cut, and every questionable pivot is recorded."

---

What this is (quick, no repeats of the basics)

Power Query stores every transformation you perform as an ordered list of Applied Steps (visible in the Query Settings pane). Each step is a named pointer to a table result — a node in the transformation chain. The Query Settings area also houses the query's Properties (name, description) and load behaviors (Enable Load / Include in report refresh).

Why it matters: these steps are reproducible, editable, and (when you mindfully manage them) efficient. They let you debug, optimize, and explain transformations to your future self or your team.

---

Anatomy of Applied Steps — what each part means

• Step name — an alias for the result (rename this; do not live in a world of "Changed Type1").
• Gear icon — edit parameters for that step when available.
• Formula bar — shows the underlying M expression for the selected step.
• Order — top-to-bottom execution order. It is NOT arbitrary. Move or delete steps carefully.

Small recipe analogy: each step is an instruction like "chop onions" — do it after fetching the onions, or you'll be crying for no reason.

---

Real example (M code) — look under the hood

let
    Source = Sql.Database("myServer","SalesDB"),
    dbo_Sales = Source{[Schema="dbo",Item="Sales"]}[Data],
    Filter2021 = Table.SelectRows(dbo_Sales, each [Year] = 2021),
    RemovedCols = Table.RemoveColumns(Filter2021,{"TempNotes","ExtraCol"}),
    ChangedTypes = Table.TransformColumnTypes(RemovedCols, {{"Amount", type number}, {"OrderDate", type date}})
in
    ChangedTypes

This corresponds to five Applied Steps: Source → dbo_Sales → Filter2021 → RemovedCols → ChangedTypes. The UI gives you the friendly checklist; the code is the precise instruction manual.

---

Query Settings (per query) — not just cosmetic

• Name & Description: crucial for documentation. Good names = faster debugging.
• Enable Load: toggles whether the query becomes a dataset in the report. Use this to make staging queries (Enable Load = off).
• Include in report refresh: if false, the query won't refresh even if others depend on it.

Pro tip: create reusable staging queries (Enable Load = off) to do heavy lifting and then Reference them in reporting queries. This keeps the model tidy and refresh behavior intentional.

---

Best practices — the tiny commandments

1. Name steps explicitly (FilterTo2021, KeepOrderColumns, EnsureTypes). Your future self thanks you.
2. Keep steps atomic — small, purposeful transformations make debugging easy.
3. Filter and remove columns early — reduce data volume asap. Less data = faster transformations.
4. Be mindful of "Changed Type". Power Query injects it automatically; consider replacing the auto step with an intentional type-change step so you control when type coercion happens.
5. Preserve query folding where possible — let the source do the heavy lifting (servers are built for this). Right-click a step and choose View Native Query to check folding.
6. Use staging queries and reference them (not duplicate) to avoid duplicated logic and to improve maintainability.
7. Document in Description — short purpose statements save meeting time.

---

Query Folding — the performance secret handshake

Query folding means Power Query can translate steps into native source queries (SQL, for instance). Folding keeps computation on the server and reduces data transfer.

Common folding breakers:

• Custom M functions operating row-by-row
• Certain transformations (e.g., invoking many Date/Time transforms inside each row)
• Adding an Index column usually breaks folding

How to check: right-click an earlier step — if "View Native Query" is available and shows SQL, folding is alive. If the option is disabled, folding is likely broken.

When folding breaks, try to:

• Reorder: filter/column-reduce before non-foldable steps
• Move non-foldable operations later in the chain
• Use native SQL (with care) if you need complex server-side logic

---

Debugging & editing tactics

• Disable / delete a step: see immediate effects. Useful to isolate errors.
• Insert steps: right-click a step and choose "Insert Step After" — the formula bar helps you shape the new step.
• Move Up / Move Down: occasional necessity, but beware of broken references.
• Advanced Editor: view or edit the entire let/in script when a multi-step change is easier as a whole.

Quick checklist when something breaks:

1. Select each Applied Step from top to bottom — where does the preview stop matching expectation?
2. Check the formula bar for unresolved column names (common after removing columns).
3. Look for automatic "Changed Type" steps that coerce unexpectedly.
4. Check Query Dependencies view to ensure source queries and staging queries link as you expect.

---

Table: UI vs Advanced Editor (cheatsheet)

UI action	What it writes in M	When to use UI vs Editor
Remove Columns (checkbox)	Table.RemoveColumns(...)	UI for quick tweaks; Editor for complex edits
Filter Rows	Table.SelectRows(...)	UI to test; Editor to parameterize
Merge Queries	Table.NestedJoin(...)	Use UI for join mapping; Editor to tune performance

---

Little thought experiments (read aloud)

• Why do people keep misunderstanding Applied Steps? Because they look small and harmless; but a single misplaced step can cascade into 10 broken visuals and a weird midnight debugging session.
• Imagine your refresh takes ages — what would you change first? (Hint: remove unneeded columns and filters, check folding, disable loads of staging queries).

---

Closing mic drop — key takeaways

• Applied Steps = the audit trail for your transformations. Respect them.
• Name, keep atomic, and order transformations to preserve folding and minimize data movement.
• Use Query Settings to control load behavior for staging vs reporting queries.
• When in doubt: step through the Applied Steps, inspect the formula bar, and remember that the Advanced Editor is your friend — not a scary wizard.

Challenge: pick a messy table, create a staging query (Enable Load = off), reduce it to only the necessary rows and columns, rename every step, and confirm folding for the initial filters. If you can do that in 15 minutes, you have officially leveled up.

Version note: builds on prior lessons on connections and the Power Query Editor fundamentals — this is where your connections start becoming clean, fast, and explainable.