Prepare the Data
This domain covers the full data preparation pipeline in Power BI Desktop and the Power BI service, from connecting to sources through profiling, cleaning, and transforming data in Power Query. The official domain range is 25–30%. DataCertPrep allocates 28% of practice questions to this domain. Questions test precise knowledge of connector behavior, storage mode trade-offs, transformation logic, query folding mechanics, and refresh configuration. Expect scenario-based questions where a requirement (performance, privacy, freshness) drives a specific technical choice.
Connectors and Storage Modes
Import, DirectQuery, Dual, and Live Connection
Storage mode determines how Power BI stores and retrieves data. The choice affects performance, data freshness, model size, and available features.
| Mode | Data stored in Power BI? | Refresh needed? | Row limit | DAX full support | Notes |
|---|---|---|---|---|---|
| Import | Yes (compressed in-memory) | Yes (scheduled or manual) | Capacity-dependent model size; check current limits | Full | Fastest query performance; data is a snapshot |
| DirectQuery | No (query sent live) | No scheduled import; freshness depends on source and refresh behaviour | No hard row limit | Restrictions depend on expression and model design | Performance depends on source; aggregations recommended |
| Dual | Both cached and live | Yes (for cache) | Same as Import for cache | Full when using cache | Used for dimension tables in composite models |
| Live Connection | No | No | N/A | Source model plus supported report-level measures | Connects to SSAS, Azure AS, or Power BI semantic model |
Composite models allow mixing Import and DirectQuery tables in a single report. A Dual table serves both modes: it acts as Import when queried alongside Import tables and as DirectQuery when queried alongside DirectQuery tables.
Live Connection is distinct from DirectQuery: source tables and relationships remain defined in the remote semantic model. Supported tabular live connections can still have report-level measures.
Common Traps
- Confusing Live Connection (no model editing allowed) with DirectQuery (you can add measures and calculated columns in DirectQuery mode).
- Assuming DirectQuery always returns real-time data — it queries the source at report interaction time, but the source itself may have latency.
- Calculated tables use Import storage; distinguish restrictions on a DirectQuery table from features of a composite model.
- Dual mode is only meaningful in composite models; a standalone table set to Dual behaves as Import.
- Model size limits depend on the licence, capacity and storage configuration. Check current capacity documentation rather than memorising a universal limit.
Worked Scenario
Requirement: A sales report must always show today's transactions from a SQL database, but a product dimension (10,000 rows, rarely changes) should load fast. What storage modes?
Reasoning: Set the transaction fact table to DirectQuery for freshness. Set the product dimension to Dual so it caches locally for fast slicing while still working correctly in the composite model when joined to the DirectQuery table.
Dataflows and Shared Semantic Model Reuse
Dataflows
A dataflow is a Power Query process that runs in the Power BI service (or Fabric) and outputs data to Azure Data Lake Storage Gen2. It promotes reuse: multiple datasets can connect to the same dataflow instead of each repeating identical transformations.
| Feature | Dataflow (Standard) | Dataflow Gen2 (Fabric) |
|---|---|---|
| Authoring location | Power BI service (workspace) | Microsoft Fabric |
| Output storage | Internal CDM / ADLS Gen2 | OneLake (Fabric lakehouse) |
| Incremental refresh | Supported | Supported |
| Computed entities | Premium/PPU only | Available broadly |
| Linked entities | Premium/PPU only | Replaced by referencing |
Computed entities transform the output of another entity within the same dataflow — requires Premium or PPU in standard dataflows.
Linked entities reference an entity from one dataflow into another workspace's dataflow — also Premium/PPU only.
Shared Semantic Models
A shared (published) semantic model lets multiple reports connect via Live Connection, avoiding duplication of measures and relationships. This is the preferred enterprise pattern. Reports using a shared model cannot modify the model structure.
Common Traps
- Dataflows do not replace datasets — they are a staging/prep layer; a dataset still imports from the dataflow.
- Computed entities require Premium or PPU; this is a frequent distractor.
- Connecting a Power BI Desktop file to a published dataset uses Live Connection, not DirectQuery.
- Dataflows run on a schedule independent of dataset refresh — the dataset refresh must be scheduled after the dataflow completes.
Sign in free to read the full guide
10 more sections (about 2,696 words) continue below. The first domain's study guide is free for every PL-300 learner with a free account.