Concept
1. Staging queries
Instead of one giant query doing everything, split the work:
| Query | Job | Loads to |
|---|---|---|
stg_Sales |
folder import + cleaning (Lesson 2–3) | Connection Only |
dim_Products |
products.csv, trimmed, typed | Connection Only / Data Model |
dim_Stores |
stores.csv | Connection Only / Data Model |
fact_Sales |
references stg_Sales + merges | Table or Data Model |
chk_UnknownStores |
Left Anti check | Table (small) |
Benefits: each query is short and readable; a fix in staging flows to everything built on it; checks are separate from outputs.
Naming prefixes (stg_, dim_, fact_, chk_) keep the Queries pane tidy. You can also create groups (right-click → Move To Group).
2. Reference vs Duplicate
Right-click a query:
- Reference — new query that starts from the result of the original. Change the original, the reference follows. Use this to build on staging queries.
- Duplicate — an independent copy of all steps. Changes don't flow. Use for experiments.
3. Load To… options
Home → Close & Load To…:
| Option | When |
|---|---|
| Table | you want to see the rows (up to ~1 million) |
| PivotTable Report | summary only, no rows on a sheet |
| Only Create Connection | staging queries, or loading to the model |
| Add this data to the Data Model | Power Pivot / large data / relationships (Module 4) |
50,000-row facts are better in the Data Model than on a sheet: smaller file, faster pivots. To change later: Queries & Connections pane → right-click → Load To….
4. Query Dependencies view
Power Query Editor → View → Query Dependencies. A diagram shows sources (folders, files) → staging queries → outputs. Use it to understand someone else's workbook and to see what breaks if you delete a query.
5. Refresh settings
Data → Queries & Connections → right-click a query → Properties:
- Refresh every N minutes — for live sources while the file is open.
- Refresh data when opening the file — reports always current.
- Enable background refresh — keep working while it refreshes (turn off if a macro must wait for the refresh to finish).
- Refresh this connection on Refresh All — untick for slow queries you refresh only by hand.
Data → Refresh All (Ctrl + Alt + F5) refreshes queries first, then pivots. Sometimes pivots refresh before the query finishes when background refresh is on — refresh twice, or turn background refresh off for queries that feed pivots.
6. Performance basics
- Filter and remove columns early (Power Query can push filters back to databases).
- Don't load staging queries to sheets.
- Disable "Allow data preview to download in the background" (File → Options → Query Options → Data Load) for huge workbooks if the editor feels slow.
- Avoid a long chain of merges against queries that themselves reload big folders — Table.Buffer and model relationships are advanced fixes.
Common mistakes
Loading every staging query to a sheet (bloated file). Using Duplicate when Reference was intended (fixes don't flow). Pivots refreshing before queries finish.