Memory hook: Clean before modeling; fold work toward the source.
Must remember
Power Query transforms data using M and an ordered query-step pipeline. Select connectors, credentials and privacy levels deliberately. Parameters make source paths or filters reusable without embedding environment differences everywhere. Privacy levels influence safe combination of sources; disabling them casually can expose private data through another source.
Import stores data in the semantic model and needs refresh. DirectQuery sends supported queries to the source, trading freshness/source governance for source load and latency constraints. Direct Lake reads supported Fabric lake data through its engine/model rather than ordinary Import refresh; availability, capacity and fallback behavior depend on the model/source configuration. A live connection to an existing shared semantic model reuses its governed definitions.
Profile column quality, distribution and statistics over a representative scope; preview profiling can cover only a subset unless changed. Null, empty text, errors and zero mean different things. Resolve types, locale-dependent date/number parsing, duplicate keys and inconsistent values before loading. Replacing every missing value with zero can corrupt averages and business meaning.
Merge joins tables horizontally by keys; append stacks compatible rows. Grouping aggregates; pivot turns values into columns; unpivot turns repeated measure columns into attribute/value rows. Expand nested records/lists to transform semi-structured input. Keep dimension keys unique and validate unmatched fact rows.
A referenced query depends on another query’s transformation logic; a duplicate starts an independent copy. Referencing does not guarantee a single cached source execution. Disable load for staging queries that should not become model tables. Query folding pushes supported transformations to the source; inspect where it stops, especially before filtering a large dataset.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Monthly columns need one date/value pair per row | Unpivot. |
| Combine this year and last year’s same-shaped records | Append. |
| Enrich transactions with customer attributes | Merge on validated keys. |
Traps
- Null is not automatically zero.
- A reference query is not a guarantee of source-result caching.
Active recall
1. Merge versus append?
Join columns by keys versus stack rows.
2. What is query folding?
Pushing supported transformation work to the data source.
3. Why check locale?
The same text can parse into different dates or numbers.
4. Import versus DirectQuery?
Stored model data refreshed periodically versus queries against the source.
5. Why disable load for staging?
To avoid unnecessary model tables while keeping reusable transformation logic.