Data Loading & Query Optimisation
Configure how Power Query loads data into your model, understand query folding, and learn best practices for efficient data loading and refresh performance.
Getting data into the model efficiently
Think of a delivery truck route.
A smart driver plans the route before leaving — avoiding traffic, combining nearby stops, skipping unnecessary detours. The result: faster delivery, less fuel.
Power Query works the same way. Query folding pushes your transformations back to the data source (like letting the warehouse sort packages before loading the truck). Load settings control which queries actually enter your model and which are just staging steps. Together, they determine how fast your data refreshes.
Load settings: what enters the model?
Not every query in Power Query needs to load into your data model. Some queries are just staging steps — intermediate transformations that feed into other queries.
Nadia at Prism Agency (📊) has this query structure:
| Query | Purpose | Should It Load? |
|---|---|---|
RawGoogleAds | Import from API | No — staging only |
RawMetaAds | Import from API | No — staging only |
CleanedCampaigns | Append + clean both sources | Yes — this is the final table |
Dim_Clients | Client dimension | Yes |
How to disable loading:
- Right-click a query → uncheck Enable Load
- This keeps the query available as a data source for other queries but doesn’t add it to the model
Exam tip: staging queries
Staging queries that are only used as inputs to other queries should have Enable Load unchecked. This keeps your model lean and avoids loading duplicate data.
On the exam, if you see a scenario where “unnecessary tables appear in the model” or “the model is too large”, look for queries that should be staging-only (disable load) rather than loaded tables.
Query folding: let the source do the work
Query folding is when Power Query translates your M steps into a native query that runs on the data source.
How it works
| Without Folding | With Folding |
|---|---|
| Power BI downloads ALL 5 million rows | Power BI sends SELECT ... WHERE Region = 'North' GROUP BY ... |
| Filters and transforms happen in Power BI’s memory | Filters and transforms happen on the SQL Server |
| Slow refresh, high memory usage | Fast refresh, minimal data transferred |
Which steps fold?
| Folds (pushed to source) | Breaks folding |
|---|---|
| Filter rows | Add Index Column |
| Remove columns | Merge Columns (sometimes) |
| Sort rows | Custom M functions |
| Group By | Operations referencing other queries |
| Change data type | Table.Buffer() |
| Rename columns | Complex conditional columns |
Checking if folding occurs
Right-click any step in the Applied Steps pane:
- “View Native Query” is available → folding IS happening (you can see the SQL)
- “View Native Query” is greyed out → folding has broken at or before this step
Kenji at Apex Manufacturing (🏭) filters his 10-million-row factory data to only the current year. With folding, this sends WHERE Year = 2026 to SQL Server — only 800,000 rows travel to Power BI. Without folding, all 10 million rows download first, then Power BI filters locally.
Best practice: order your steps for maximum folding
Put foldable steps first (filters, column removal, type changes) before non-foldable steps (custom columns, index columns). Once folding breaks at a step, all subsequent steps run locally.
Think of it like a chain — once one link breaks, everything after it falls.
Example ordering:
- Filter to current year ← folds
- Remove unnecessary columns ← folds
- Change data types ← folds
- Add Index Column ← breaks folding
- Conditional Column ← runs locally (after the break)
Data load best practices
| Practice | Why It Matters |
|---|---|
| Disable load for staging queries | Reduces model size and refresh time |
| Filter rows early | Less data to process in later steps |
| Remove unused columns | Smaller model, faster VertiPaq compression |
| Maximise query folding | Push work to the source database |
| Use reference queries wisely | Share common cleaning steps without re-processing |
| Set appropriate data types before loading | Avoids implicit conversions in the model |
| Avoid loading duplicate data | If two queries load the same rows, consolidate them |
M code: the language behind Power Query
Every step you create in the Power Query UI generates M code. You can view and edit it in the Advanced Editor (View → Advanced Editor).
Dr. Ethan Moss at Bayview Medical Centre (🏥) occasionally edits M code directly for transformations the UI can’t handle — like custom date parsing for HL7 medical data formats.
You don’t need to memorise M syntax for the PL-300 exam, but you should:
- Know that M is the underlying language
- Be able to read simple M expressions
- Understand that the Advanced Editor shows the full query
- Know that custom M functions can be created for reusable transformations
Knowledge check
Kenji's Power Query has these steps in order: (1) Filter to 2026, (2) Add Index Column, (3) Remove 10 unused columns. He notices refresh is slow. What should he change?
Nadia has three queries: RawGoogleAds, RawMetaAds, and CleanedCampaigns (which appends both raw queries). All three are loading into the model. What should she do?
Next up: Star Schema and Relationships — connect your tables in the data model.