Power Query in Power BI: from messy source to tidy table
Power Query is the part of Power BI that fetches and cleans data before a report ever sees it. An author connects to a source, then removes, renames, splits and merges until the table has the right shape. Every action becomes a recorded step, so the same clean-up repeats at each refresh. Good preparation here makes the model smaller and the formulas simpler.
- Recorded, repeatable steps
- Folding explained
- Refresh habits that last
Last updated
Power Query in Power BI: what does it do?
It does the preparation work that people once did by hand in spreadsheets. Instead of deleting columns and fixing dates each month, an author performs those actions once in the editor. Then the editor replays them whenever the data refreshes.
The editor shows each action in a list named Applied Steps. You can click any step to see the table at that point, which makes problems easy to trace. Behind the list sits a language named M, although most users rarely write it directly.
The same technology appears in Excel and in dataflows. So skills learned in one place carry over, and a query can often move between them with little change.
How a query moves from source to model
Nearly every query passes through these four stages, whatever the source.
Connect
First choose a connector for the file, database or service. Microsoft documents a wide range of them.
Transform
Then set data types, remove unused columns and filter rows, so only useful data continues.
Combine
Next merge or append tables where the model needs them joined, such as monthly files into one history.
Load
Finally, load the finished tables to the model, while helper queries stay behind with load turned off.
What is query folding, and why should you care?
Query folding means the editor translates your steps into a query that the source runs. When you filter a database table to one year, a folded step asks the database for that year only. So far fewer rows travel across the network.
Folding matters most with large tables. Without it, the engine pulls every row and filters locally, which takes longer and uses more memory. Also, features such as incremental refresh depend on folding to work efficiently.
Not every step folds, and not every source supports it. Files do not fold at all, for instance. Therefore the usual advice is to place foldable steps, such as filters and column removal, early. Then put the steps that break folding afterward.
Power Query in Power BI habits that keep refresh reliable
These three habits prevent the most common refresh failures.
Name every step
A step named Removed Columns1 tells nobody anything. Rename steps as you go, so a later reader can follow the logic.
Set types on purpose
Automatic type detection guesses from a sample. Set each type yourself, because a wrong guess fails when new data arrives.
Reference, do not duplicate
When two tables share a starting point, reference one staging query. Then a fix in that query reaches both.
Where do dataflows fit with Power Query in Power BI?
A dataflow runs the same kind of query in the service instead of inside one report file. It stores the result, and then several semantic models can read it. As a result, a customer table gets cleaned once for the whole company.
That helps when many authors repeat the same preparation. Instead of five copies of a query that drift apart, one shared dataflow holds the logic. However, it adds another item to schedule and monitor, so it suits shared tables more than one-off ones.
For a single report with its own sources, queries inside the file remain the simpler choice. Decide per table, based on who else needs the data. For help connecting many sources into one dependable model, see Power BI data integration services.
Power Query questions from Power BI authors
Is Power Query the same in Excel and Power BI?
Largely, yes. Both use the same editor design and the same M language. However, the list of connectors differs between the two, and some features arrive in one before the other. So a query usually transfers, yet it is wise to test it after the move.
Do I need to learn M?
Not at first. The ribbon and right-click menus generate M for almost every common task. Still, reading M helps when a step needs a small adjustment, such as a custom condition. Many authors learn it gradually by looking at the code the editor writes for them.
What is the difference between reference and duplicate?
Duplicate copies a query with all its steps, and the copy then lives independently. Reference creates a new query that starts from the output of the original. Therefore a change to the original flows into a referenced query, while a duplicate stays as it was.
Why is my refresh slow?
Common causes are steps that stop folding, very wide tables and merges between large queries. Reading many files from a folder can also take time. First check which steps fold. Then remove unused columns early and filter rows before any merge, since less data makes every later step faster.
Can Power BI Agency fix a slow or fragile Power Query setup?
Yes. One of our consultants reviews each query, restores folding where possible and documents the result. The work happens online in your own tenant, with a demo each week and a scope fixed in writing. We are a division of Sataware Technologies and independent of Microsoft. Call (480) 485-7373 to book a free assessment.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.