In Short: The Recorded Macro That Grew Up
Power Query is the most used and least named tool in the Microsoft data world: the connect-clean-transform-load layer inside Power BI Desktop, Excel and Fabric's Dataflows Gen2. You point it at a source, click through transformations - remove columns, fix types, split, merge, unpivot - and it records every step. On every refresh, the same steps replay against fresh data.
That recording is the whole revolution, quietly delivered: the Tuesday-morning ritual of manually fixing the export becomes a script nobody has to remember, which is the first rung of every data quality ladder.
What It Actually Does
- Connects to a very long list of sources: files, databases, APIs, SharePoint, web pages, and everything in between
- Transforms through a visual editor: filtering, reshaping, pivoting and unpivoting, merging queries (joins), appending (unions), extracting from text, handling errors
- Types and cleans: data types enforced early, nulls handled deliberately, locale quirks (dates, decimals) tamed at the door
- Loads the result into the Power BI model, an Excel table, or - via Dataflows Gen2 - OneLake destinations that many downstream consumers share
Underneath, every step is code in M, a functional language you can read in the Advanced Editor. Most practitioners live happily in the buttons; the ones who peek at M gain the last 20% - dynamic sources, parameterised logic, the transformations the ribbon cannot express.
The Two Habits That Separate Professionals
Push work upstream. Power Query supports query folding: for database sources, steps are translated into the source's own SQL where possible, so the database does the heavy lifting instead of your refresh. Keeping steps foldable - and knowing which steps break folding - is the single biggest performance lever, and the reason experienced authors watch step order like hawks.
Shape here, calculate in DAX. Structure, cleaning and combining belong in Power Query; business calculations under filters belong in DAX measures. Teams that blur this line get slow refreshes and bloated models; teams that respect it get semantic models that stay fast and legible.
From Personal Tool to Team Infrastructure
Power Query's career path mirrors its users'. It starts personal: one analyst, one report, one recorded cleanup. Dataflows Gen2 make it shared: the same editor producing reusable tables in OneLake, so five reports stop maintaining five copies of the same cleaning. And at scale, the logic graduates to pipelines and notebooks in Fabric proper - versioned, tested, monitored - typically when volumes, consumers or compliance outgrow the refresh-and-hope model.
The graduation is a feature, not a failure: the analyst who learned transformation thinking in Power Query is halfway to data engineering, and the logic they encoded travels. In our Excel-to-Power BI journeys, Power Query is consistently the bridge skill - familiar enough to adopt, structured enough to matter.
Sources and Further Reading
Frequently asked
The data preparation engine inside Power BI, Excel and Fabric's Dataflows: you connect to a source, apply transformation steps (filter, split, merge, pivot, type) through a visual editor, and the result loads into your model or destination. Every step is recorded, so the same cleaning replays on every refresh.
No, and the division of labour matters: Power Query shapes data before it lands in the model (structure, cleaning, combining); DAX calculates over the modelled data at query time (measures, business logic). Rule of thumb: transform as far upstream as possible, calculate in DAX.
The functional language underneath Power Query: every click in the editor writes an M step. Most users never write M directly, and the advanced ones edit it for the cases the buttons cannot express. It is worth reading even if you never write it.
Power Query as a standalone, reusable cloud artefact in Fabric: the same editor, but the output lands in OneLake destinations for many consumers instead of one report's model. They are how Power Query skills scale from personal prep to team infrastructure.
When volumes make refreshes crawl, when logic needs testing and versioning discipline, or when many consumers depend on the output. That is the graduation point to Fabric pipelines, notebooks and medallion layers - carrying the same transformation logic to an engineering-grade home.
