This lesson explains Power Query's Applied Steps and Query Settings: what they are, how to read and edit them, performance implications (query folding), debugging tactics, and best practices for maintainable ETL with Power Query. It combines conceptual guidance, an M code example, and practical checklists to help you shape reproducible, efficient queries.
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 P...
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...
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 — to...
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(Filter202...
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...
Best practices — the tiny commandments Name steps explicitly (FilterTo2021, KeepOrderColumns, EnsureTypes). Your future self thanks you. Keep steps atomic — small, purposeful transformations make debugging easy. Filter and remove columns early — reduce data volume asap. Less data = faster tra...
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 tr...
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 broke...
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...
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? ...
11 study modes available based on your content