Almanac
Microsoft/power-biPower Platform

Consultant KB for Microsoft Power BI: semantic models, DAX and modelling, Power Query and dataflows, reports and visuals, the service and workspaces, capacity and performance, embedding and integration, governance and security, ALM and deployment, and licensing, plus cross-cutting decision guides. Scoped to Power BI, with Microsoft Fabric covered where it touches Power BI directly. Implementation notes, configuration decisions and the gotchas that bite on real projects. Populated by the daily author agent from the Power BI release plans, docs repo and product blog, plus the author's own consultant notes.

power-bi/03-power-query-and-dataflows/feature-power-query-and-m.md
feature-power-query-and-m.mdv1 · history
CurrentApplies to BothUpdated last monthSource Microsoft Learn ↗

What it does

Power Query is the data preparation engine that sits between your source and your model. You connect, click transformations in the editor, and Power Query writes the M code that does the work — every step you take is recorded as a named applied step in a single M script.

Key facts

  • Two front ends, one engine: Power Query for Desktop (Power BI Desktop, Excel) and Power Query Online (dataflows, Fabric Data Factory). Microsoft's line is that the experience is near-identical in both.
  • The applied steps list is a friendly rendering of the M script. Rename a step in the UI and the identifier changes in the code — they're the same thing.
  • M is case-sensitive and functional. Step names with spaces become quoted identifiers like #"Removed other columns".
  • Microsoft counts over 350 transformation types available across connectors, and connectivity to hundreds of sources.
  • The Advanced Editor (View tab, or Home > Query group) is where you see and edit the whole script. Script view in the formula bar shows the same thing per-step.
  • M evaluation is lazy. Steps whose output isn't needed for the final result may never be evaluated, and the engine can reorder steps during optimisation.
  • Where multiple sources are combined, the data privacy firewall gets involved and can change how — or whether — a query evaluates.

When to use / skip

Power Query is where the shape of the model gets decided, so it's not optional, but the amount you do in it is a real choice. Do the cheap, folding-friendly work here: filtering, column selection, type setting, renaming, merging dimension lookups. Push heavy joins, deduplication and business logic back into the warehouse if there is one, and push presentation logic forward into DAX. The trap is treating Power Query as a general-purpose ETL tool because it's the only tool the maker has access to — that's how you end up with a 40-step query nobody can safely change.

Configuration decisions

  • Where the transformation layer actually lives: source view, Power Query, dataflow, or a mix — and who owns each.
  • Whether you set explicit data types early and only once, or let the auto-detected "Changed Type" steps accumulate.
  • Whether column selection is done with "Remove other columns" (explicit allow-list, survives new source columns) or "Remove columns" (fragile).
  • How much M you're willing to hand-write, given the client's ability to maintain it after you leave.
  • Whether "Enable load" stays on for intermediate queries or they become disabled staging queries.

Gotchas

  • The "Changed Type" step Power Query adds automatically references columns by name. Rename or drop a column upstream and the query breaks at that step with a message that points nowhere useful.
  • Steps generated by the UI hard-code values — filter on last month and the M contains that literal date. It'll still be there in eighteen months.
  • The data preview is a sample. A query that looks clean in the editor can fail on refresh when it meets rows the preview never showed you.
  • Merging or appending across two different sources triggers privacy-level evaluation, which can silently kill folding or throw a firewall error that has nothing to do with your logic.
  • Query names become table names in the model. Renaming after reports are built is a bigger job than it looks.

Consultant notes

  • Insist on step naming from day one. "Filtered Rows2" tells the next person nothing; "Exclude cancelled orders" tells them everything. It costs nothing at build time.
  • Technical debt in Power Query is invisible to the client because it doesn't show up in the report. Make it visible — walk them through a query in a handover session and let them see the 40 steps.
  • If the client has a data warehouse team, get the transformation conversation done early. Duplicating logic in Power Query that already exists in the warehouse is the most common avoidable rework on these projects.
  • Comments in M are cheap (// for a line). Use them for anything where the "why" isn't obvious from the step name.

Worth another look if the client stands up a warehouse or Fabric lakehouse — a lot of query logic should move upstream at that point.

Was this accurate?
Related
Calculation groups02-dax-and-modellingDirectQuery storage mode01-semantic-modelsExternal tools09-alm-and-deploymentQuery folding03-power-query-and-dataflowsStar schema modelling02-dax-and-modellingParameters and query reuse03-power-query-and-dataflows