Data preparation for Microsoft PL-300 Power BI Data Analyst: Concepts, Scenarios, and Study Priorities

 

Data preparation is the point where a Power BI solution either gains a reliable analytical foundation or inherits problems that will surface later as confusing relationships, inconsistent measures, slow refreshes, or misleading visuals. For PL-300 candidates, this is not a minor setup task. Microsoft’s current English study guide, with skills measured as of April 20, 2026, assigns 25-30 percent of the exam to Prepare the data. The objectives cover how you connect to sources, evaluate and clean incoming data, transform it into model-ready structures, and control how queries load. The practical lesson is simple: if the inputs are poorly understood, every later layer becomes harder to reason about.

A strong candidate therefore studies data preparation as a sequence of decisions rather than as a menu of Power Query buttons. You should be able to explain why a storage mode fits a requirement, what a profiling result implies, why a merge changes row counts, when a reference query is safer than a duplicate, how an incorrect data type creates downstream errors, and why a staging query should or should not load into the semantic model. The exam can test the visible action, but good preparation focuses on the logic that makes the action correct.

This deep dive stays intentionally narrower than a general PL-300 study guide. It follows the current Prepare the data blueprint and connects each objective to operational scenarios. The goal is not to memorize a fixed click path. It is to develop a repeatable way to inspect a source, select an appropriate connection approach, diagnose quality problems, reshape data, validate the result, and recognize the trade-offs that matter in a real Power BI workflow.

Start with the current PL-300 data-preparation blueprint

The current PL-300 guide groups Prepare the data into three areas. Get or connect to data includes identifying and connecting to sources or shared semantic models, changing source settings such as credentials and privacy levels, choosing among Direct Lake, DirectQuery, and Import, and creating or modifying parameters. Profile and clean the data covers evaluating statistics and column properties, resolving inconsistencies and unexpected or null values, and fixing data import errors. Transform and load the data covers data types, derived columns, grouping, pivoting and unpivoting, semi-structured data, fact and dimension shaping, query reuse, merging, appending, keys, and load configuration.

Those objectives form a pipeline, not three isolated lists. Connection choices constrain what transformations are practical. Profiling determines which transformations are necessary. Transformation choices determine the grain and key quality that the model receives. Load configuration determines whether helper queries become visible tables or remain behind the scenes. When you study the objectives in that order, many scenario questions become easier because you can identify which stage of the pipeline is actually failing.

Microsoft also states that the bullets under each measured skill illustrate how the skill is assessed rather than defining every possible related topic. That matters for preparation. A candidate who learns only the named commands may still be weak when a scenario asks about an effect such as query folding, privacy isolation, refresh behavior, or unexpected row multiplication. The safest approach is to understand the current objective and the operational reason behind it.

Treat source selection as an architectural decision

Power BI can connect to files, databases, cloud services, Fabric items, and existing semantic models, but the presence of a connector does not make every source equally appropriate. Begin by asking what the analytical requirement actually is. Is the team building a new model from raw operational data, reusing a governed enterprise semantic model, or connecting to a Fabric lakehouse or warehouse that already contains curated Delta tables? Does the business need near-current data, or will scheduled refresh satisfy the requirement? Is the source small enough to copy efficiently into an Import model? Are there governance rules that favor reuse over duplication?

A shared semantic model is often the better answer when the organization already maintains trusted definitions, relationships, measures, and security centrally. Reusing that model can reduce duplicate logic and inconsistent business definitions. Connecting directly to raw data may be appropriate when the analyst needs a new grain, transformations that the governed model does not expose, or a separate solution boundary. The exam is less about declaring one pattern universally superior than about matching the connection choice to the requirement presented.

Import, DirectQuery, and Direct Lake are different operating models

Import stores a copy of selected data in the Power BI semantic model. Visual interactions generally benefit from in-memory processing, but the model must be refreshed to reflect source changes. Import is often a strong default for self-service analytics when data volume is manageable and the business can tolerate refresh-based freshness. It also gives broad transformation and modeling flexibility. The trade-off is that the model contains a replicated copy and refresh consumes time and resources.

DirectQuery leaves the data in the underlying source and sends queries back when visuals need results. That can help when copying the full data volume is undesirable or when freshness requirements make scheduled import less attractive. The cost is that report responsiveness now depends much more directly on the source, network, translated query, and model design. Some transformations and modeling features are constrained. A question that emphasizes current source data, a large operational store, and a well-tuned backend may point toward DirectQuery; a question that emphasizes maximum interactivity and flexible shaping often favors Import.

Direct Lake is a Fabric-oriented option for semantic models over supported OneLake-backed Delta tables. Microsoft describes Direct Lake as using the VertiPaq engine while avoiding the need to import the full dataset into the semantic model in the same way as Import. It is particularly relevant when the analytical data is already curated in Fabric and scale or latency makes a conventional import cycle less attractive. For PL-300 reasoning, the key distinction is not to treat Direct Lake as simply another label for DirectQuery. DirectQuery federates queries to the source, while Direct Lake is designed to work directly with supported OneLake data in a different storage architecture.

When a scenario includes a storage-mode decision, write down four facts before choosing: where the data resides, how fresh it must be, how much data must be analyzed, and what performance or transformation expectations exist. This prevents a common error: choosing the mode you use most often rather than the one the scenario supports.

Scenario signal	Likely direction	What to verify	Common trap
Modest dataset; fastest interaction; scheduled freshness is acceptable	Import	Refresh window, model size, transformation needs	Choosing DirectQuery only because the source is a database
Large Fabric Delta data; low-latency access; avoid full replication	Direct Lake	Fabric source suitability, model behavior, capacity context	Treating Direct Lake as identical to DirectQuery
Operational source must remain in place; near-current queries required	DirectQuery	Source performance, supported transformations, concurrency	Ignoring latency and backend load
Trusted enterprise model already defines measures and security	Shared semantic model	Whether required fields and logic already exist	Duplicating governed logic unnecessarily

Credentials, privacy levels, and source settings are part of data preparation

Changing a data source path is not enough if the connection also depends on credentials, privacy classification, or gateway configuration. PL-300 explicitly includes source settings, credentials, and privacy levels in the data-preparation domain. That means candidates should understand what happens when a source is reachable in Power Query Editor but later fails during refresh because the service uses different credentials or cannot reach an on-premises system through the required gateway path.

Privacy levels matter most when Power Query combines data from more than one source. Microsoft documents Public, Organizational, and Private levels as controls that help isolate sources and prevent unwanted data transfer between trust zones. A Private source is intentionally restrictive. In some combinations, privacy rules can block or buffer operations that would otherwise be technically possible. A candidate should therefore avoid treating a privacy warning as a random Power Query nuisance. The warning is evidence that the engine is protecting data movement across source boundaries.

A useful troubleshooting habit is to separate authentication, authorization, reachability, and privacy. Authentication asks whether the credential is valid. Authorization asks whether that identity can read the required object. Reachability asks whether the refresh environment can contact the source. Privacy asks whether Power Query is allowed to combine the sources in the proposed way. These failure classes may produce similar symptoms to an inexperienced analyst, but they require different fixes.

Use parameters to separate logic from environment-specific values

Parameters are valuable because they allow a query design to stay stable while a value such as server name, database, file path, cutoff date, or environment identifier changes. The PL-300 blueprint explicitly includes creating and modifying parameters, so study them as a design tool rather than as a dialog box. A parameter can make a solution easier to move between development and production, reduce the number of hard-coded values, or support controlled filtering during development.

The exam-relevant distinction is between a parameter that changes a meaningful input and a transformation that merely happens to contain a constant. If multiple queries use the same environment-specific server value, a parameter can centralize that dependency. If a date cutoff is used to reduce the development dataset, a parameter can make the scope clear and adjustable. The candidate should also understand that parameters do not automatically solve refresh or credential problems; they organize inputs, while the referenced source still needs valid connectivity and permissions.

Profile before you clean

The most important data-quality habit in Power Query is to inspect before changing. Microsoft explicitly expects candidates to evaluate data statistics and column properties. Profiling gives you evidence about nulls, errors, distinct values, distributions, minimums, maximums, and type behavior. Without that evidence, cleaning becomes guesswork. Removing null rows might hide an upstream extraction problem. Replacing every error with zero might corrupt a financial measure. Trimming text may be correct, but only if the whitespace is actually noise rather than meaningful formatting.

Start with grain. Ask what one row is supposed to represent. A sales table might be one row per order line, while a customer extract should be one row per customer. If that expectation is unclear, you cannot reliably interpret duplicates. A repeated customer key in an order-line table may be normal; the same repeated key in a customer dimension candidate may indicate a quality problem. Data quality is therefore contextual. Power Query tells you what values exist, but the business grain tells you whether those values are valid.

Then inspect candidate keys and categorical fields. A product table with 50,000 rows and only 49,998 distinct ProductID values deserves investigation. A Country field containing ‘United States’, ‘USA’, ‘U.S.’, and trailing-space variants may fragment reporting even though every row imports successfully. A date column with a handful of values in 2099 may be technically valid but operationally suspicious. The strongest candidates do not equate ‘no import error’ with ‘good data’.

Classify nulls before deciding what to do with them

Null values can represent several different conditions: data not collected, data not applicable, data not yet known, extraction failure, or a legitimate absence. Those meanings lead to different transformations. Replacing all null numeric values with zero changes the semantics if null means ‘unknown’. Filling a missing category with ‘Unassigned’ may be useful for reporting, but only if the business accepts that bucket. Removing rows with null foreign keys may improve relationship integrity but could also discard transactions that should be investigated.

For exam scenarios, look for language that defines the business rule. If rows with missing CustomerID must be excluded from customer-level analytics, filtering may be justified. If missing discount means no discount was applied, replacing null with zero may be correct. If a blank date means the event has not happened yet, replacing it with today’s date would invent a fact. The correct Power Query action follows the meaning of the field, not the visual convenience of eliminating blanks.

Resolve import errors by locating the first broken assumption

Import errors often appear after a type conversion or schema change. A text value inside an expected numeric column can produce conversion errors. A renamed or removed source column can break later steps that still reference the old name. A file-combine pattern can fail when one incoming file has a different header or extra preamble rows. The fastest repair is rarely to delete the final erroring step. Instead, locate the earliest step where the query’s assumption stops matching the incoming data.

This is why Applied Steps should be read as a dependency chain. If Change Type fails after a source update, ask whether the source type changed, whether locale affects parsing, or whether an unexpected value has appeared. If an expansion step fails after a merge, verify that the nested table still contains the expected columns. If a parameterized path points to a new environment, confirm that the schema is equivalent. Troubleshooting becomes much more reliable when you identify the broken assumption rather than patching the visible symptom.

Choose data types deliberately because type is behavior

A data type is not cosmetic metadata. It affects parsing, aggregation, relationships, sorting, storage, and available transformations. A numeric-looking identifier such as a postal code or product code may need to remain text because arithmetic is meaningless and leading zeros are significant. A date stored as text may sort incorrectly and block time-aware operations. Decimal numbers, fixed decimal values, integers, dates, datetimes, and text have different behaviors that matter both during Power Query transformations and later in the model.

Type conversion also has an ordering effect. If a source uses locale-specific dates or decimal separators, converting before resolving locale assumptions can create errors. If you split a composite text field into numeric pieces, the correct sequence may be to split first and convert second. If a join key uses text in one query and integer in another, the mismatch can prevent expected matches. A good PL-300 answer often depends on recognizing that a transformation is logically correct but applied at the wrong stage.

Design transformations so every step has a reason

Power Query makes it easy to accumulate a long list of steps: rename, remove, split, replace, filter, group, merge, sort, add column, and more. The presence of many steps does not indicate a well-prepared dataset. Each step should contribute to a stated goal such as establishing correct grain, enforcing a business rule, reducing unnecessary data, standardizing a key, or reshaping a source into a model-friendly form. If you cannot explain why a step exists, it is a candidate for simplification.

Transformation order matters for both correctness and efficiency. Filtering unnecessary rows and removing unused columns early can reduce the amount of data processed later. Standardizing keys before a merge can improve match quality. Grouping too early can destroy detail that a later calculation needs. Sorting may be irrelevant if no downstream operation depends on order. The correct sequence is the one that preserves required information while moving the data toward a clear analytical grain.

Understand query folding as a performance clue

Query folding occurs when Power Query can translate compatible transformations into operations executed by the data source. It is not a separate PL-300 objective bullet, but it is operationally important because it explains why two queries with the same final shape can perform differently. Filtering a large SQL table in a foldable way can allow the source engine to return fewer rows. A non-foldable custom transformation may force more data to be retrieved and processed elsewhere.

Do not turn this into a rule that every step must fold. File sources do not offer folding in the same way as many database sources, and some necessary transformations naturally break it. The useful exam-level reasoning is that source capabilities and transformation order can affect performance. When a scenario describes a very large source and a slow refresh after a particular step, query folding is one of the diagnostic ideas worth considering.

Know what grouping, pivoting, unpivoting, and transposing actually change

Grouping changes grain by aggregating rows according to one or more keys. If an order-line table is grouped to one row per order, detail is intentionally lost. That can be correct for a summarized staging table, but dangerous if line-level analysis is later required. Before grouping, state the target grain and which measures can be aggregated safely. The same principle applies to removing duplicates: it is only correct if you know what uniqueness is supposed to mean.

Pivoting turns values from a column into new columns, often creating a wider structure. Unpivoting does the reverse by turning repeated measure columns into attribute-value rows. For analytical models, unpivoting is frequently useful when a source spreadsheet stores months, regions, or categories as separate columns. A table with Jan, Feb, Mar columns can become rows with Month and Amount, which is easier to extend and analyze. Transpose swaps rows and columns and is usually a structural repair for sources that were laid out for human reading rather than analysis.

The exam can make these operations sound similar because all of them reshape data. Focus on the target structure. If repeated columns should become row values, think unpivot. If row values should become separate columns, think pivot. If the entire orientation is reversed, think transpose. If many rows must become one summarized row per key, think group and aggregate.

Convert semi-structured data into an explicit table

JSON, XML, lists, records, and nested structures may expose data that is not immediately tabular. Microsoft includes converting semi-structured data to a table in the current blueprint. The skill is not merely clicking an expand icon. You need to recognize nesting, decide which fields are relevant, expand records or lists in the correct order, and verify whether expansion changes row counts.

Consider an API result where each order record contains a list of line items. Expanding the order records may still leave one nested list per order. Expanding that list changes the grain to one row per order line. If each line item contains a product record, a further expansion may add product attributes. At each stage, the candidate should know what one row represents. Without that discipline, nested expansion can produce duplicated header-level values and unexpected row multiplication that looks like a modeling issue later.

Shape raw sources into fact and dimension tables

The current PL-300 blueprint explicitly includes creating fact tables and dimension tables during data preparation. That means star-schema thinking begins before Model view. A transactional source may contain order date, customer name, customer segment, product description, product category, quantity, and sales amount in every row. Loading that flat structure unchanged is possible, but it repeats descriptive attributes and makes relationship design less clear.

A preparation workflow can separate the transaction grain from descriptive entities. The fact table retains the keys and numeric events needed at the chosen grain. Dimension queries produce one row per entity with descriptive attributes. A customer dimension should have a stable customer key and one row per customer. A product dimension should have one row per product. The transformation work may include selecting columns, removing true duplicate entity rows, standardizing keys, and validating that the fact-side keys have corresponding dimension members.

Do not confuse normalization for transactional systems with the dimensional shape preferred for analytics. PL-300 candidates should be able to recognize that a star schema often simplifies filtering and business reasoning. The data-preparation stage should therefore deliver tables with explicit grain and appropriate keys rather than leaving every structural decision for the modeling stage.

Reference and duplicate queries solve different reuse problems

A duplicate query creates a separate copy of the query’s current steps. Future changes to the original do not automatically become part of the duplicate’s transformation chain. A reference query starts from the result of another query, so it depends on the upstream query. This difference is central when building reusable staging logic.

Suppose a raw Sales query performs source connection, column selection, type cleanup, and standardization. You need one fact table and one small aggregation for validation. Referencing the cleaned staging query can keep both downstream outputs aligned with the same upstream cleanup. Duplicating the raw query may create two independent chains that can drift. On the other hand, duplication can be appropriate when you intentionally want an independent branch that should no longer depend on the original query’s later changes.

The blueprint asks candidates to identify when to use reference or duplicate queries and understand the resulting impact. The key is dependency. Reference creates a logical upstream-downstream relationship. Duplicate creates a copy. Study how that affects maintenance, refresh behavior, and the risk of inconsistent transformation logic rather than memorizing which command sits in the context menu.

Merge and append are not interchangeable

Merge combines columns by matching rows on one or more keys. Append combines rows by stacking compatible tables. When separate monthly sales extracts share the same columns and represent the same business process, combine them vertically so the consolidated query preserves one consistent row structure across periods. If a Sales table needs ProductCategory from a Product table, merge is appropriate because the result adds attributes based on a relationship between keys.

Merge scenarios require more reasoning because join type and key quality affect the result. A left outer merge preserves every row from the first query and brings matching data from the second. An inner merge keeps only matches. If the right-side key is not unique, expanding the merged table can multiply rows in the left-side dataset. That is not a Power Query bug; it is evidence that the assumed relationship was not one-to-one on the lookup side.

Before merging, validate data types, normalization, and expected uniqueness of the join columns. Leading spaces, case differences, inconsistent formatting, or mismatched types can reduce matches. After merging, compare row counts and unmatched-key counts with expectations. This simple validation habit is one of the strongest defenses against silently corrupted analytical data.

Create keys that support relationships rather than merely looking unique

PL-300 includes identifying and creating appropriate keys for relationships. A good key is stable, consistent, and unique on the dimension side at the intended grain. A display name is often a poor key because names can change or repeat. A concatenated key may be necessary when uniqueness is defined by multiple business attributes, but it should be constructed consistently in every participating query.

If a source lacks a durable identifier, an index column can sometimes support a surrogate key inside a controlled transformation, but candidates should understand its limitations. Regenerating an index after a different sort or source-order change can alter assignments. In enterprise data platforms, surrogate keys are often managed upstream. In PL-300 scenarios, the important reasoning is to identify what columns uniquely define the dimension member and ensure the fact table carries a matching value.

Configure query loading to keep the semantic model intentional

Not every Power Query query should become a visible table in the model. Staging queries, helper queries, parameter-driven source functions, or intermediate transformations may exist only to support final outputs. Configuring load appropriately keeps the semantic model focused and avoids exposing redundant tables that confuse users or consume unnecessary model resources.

A common pattern is Raw Source -> Clean Staging -> Fact or Dimension outputs. The staging query can centralize source cleanup while final queries apply entity-specific shaping. If the staging result is not intended for analysis, disabling its load can be appropriate. The candidate should still understand dependencies: disabling load does not mean the query disappears from the transformation chain. It means the query is not loaded as its own model table.

Scenario 1: combine monthly files without breaking grain

Imagine a retailer receives one CSV file per month. Each file contains OrderID, LineNumber, ProductCode, CustomerCode, OrderDate, Quantity, UnitPrice, and Region. The files are stored in one folder, but one month includes an extra notes column and another contains a malformed date. The business needs one fact table at order-line grain. The correct approach is not to import twelve files separately and append them manually every month.

Start with a folder-based ingestion pattern that can combine files using consistent transformation logic. Inspect the sample-file transformation and verify that the schema assumptions tolerate the harmless extra column while still surfacing genuinely missing required fields. Profile OrderID plus LineNumber as the likely business grain. Repair the malformed date according to the actual source rule rather than replacing it blindly. Standardize ProductCode and CustomerCode before using them as relationship keys. Confirm that the combined row count approximately equals the sum of source rows after any documented exclusions.

Then split descriptive entities only where the source supports reliable dimension creation. If ProductCode repeats with conflicting ProductName values, do not simply remove duplicates and declare the issue solved. That conflict is a data-quality finding. If the customer extract is supplied separately and contains one row per customer, merge only when you need attributes in staging; otherwise the better model may be to load Customer as a separate dimension and relate it later. The preparation goal is a trustworthy fact table, not the widest possible table.

Scenario 2: choose a storage mode before optimizing transformations

A Fabric-based analytics team has a large curated Delta table in OneLake, and the business wants low-latency access without repeatedly copying the full dataset into a conventional import model. That scenario naturally raises Direct Lake. By contrast, a departmental analyst with a modest SQL dataset, complex local transformations, and nightly freshness can often justify Import. A solution that must query a governed operational database with near-current values and cannot replicate the dataset may point toward DirectQuery.

Notice the order of reasoning. First identify source location, scale, freshness, and analytical behavior. Then choose the mode. Only after that should you optimize the transformation design. If you start with a favorite storage mode and force the scenario to fit it, you may miss the main PL-300 decision. Storage mode changes what data is cached, where queries execute, how freshness is achieved, and which limitations matter.

Scenario 3: diagnose row multiplication after a merge

A Sales query contains 1.2 million order-line rows. After merging Product on ProductCode and expanding Category, the row count increases to 1.23 million. The candidate should not immediately blame Power Query performance. The key diagnostic question is whether ProductCode is unique in the Product query. If several Product rows share the same code, one Sales row can match multiple Product rows and expand into several output rows.

The fix depends on the business truth. If duplicates are exact redundant records, a justified deduplication may restore uniqueness. If the same ProductCode legitimately represents different versions or effective dates, the join key is incomplete and may need additional attributes. If the Product table is not actually a dimension at the intended grain, the model design must change. The exam-relevant skill is to connect an unexpected row-count change to join cardinality and key quality rather than applying a cosmetic cleanup.

Scenario 4: separate a privacy problem from a credential problem

Suppose a query combines an internal SQL source with a local workbook containing sensitive employee mappings. The SQL credentials are valid, but refresh is blocked when the sources are combined. A weak troubleshooting approach resets credentials repeatedly. A stronger approach recognizes that privacy isolation may be preventing the combination because the sources are classified differently. The next step is to inspect the privacy levels and confirm the organization’s intended trust boundary.

Now change the scenario: the same query works in Desktop but fails after publication because the service cannot authenticate to the on-premises SQL source through the expected connection. That points toward credentials, gateway, or reachability rather than a Power Query transformation problem. Learning to classify failures this way makes data-preparation questions much easier because you stop treating every refresh problem as the same problem.

Build a data-preparation troubleshooting sequence

When a prepared query produces the wrong result, troubleshoot in a fixed order. First verify source and environment: are you connecting to the correct file, server, database, semantic model, or Fabric item? Second verify authentication, authorization, reachability, and privacy. Third verify schema: are the expected columns and nested fields still present? Fourth verify types and locale assumptions. Fifth verify grain and keys. Sixth verify transformation order, merge behavior, and row counts. Finally verify which queries load and whether downstream model tables receive the intended shape.

This order prevents expensive detours. If the wrong database is selected, rewriting a merge step is wasted work. If the key is non-unique, changing DAX will not fix duplicated fact rows. If the date is still text, troubleshooting time intelligence is premature. Many Power BI problems appear in the report layer but originate in the data-preparation layer. PL-300 rewards candidates who can move backward through the pipeline and find the earliest incorrect assumption.

Use validation checks after every structural transformation

A transformation is not complete when Power Query displays a preview without errors. After filtering, record expected row reduction. After deduplication, record what uniqueness rule justified the removal. After a merge, compare pre- and post-merge row counts and count unmatched keys. After append, confirm that the result equals the expected total rows from the contributing datasets, accounting for any intentional filters. After creating a dimension, test key uniqueness. After changing a type, inspect errors rather than assuming conversion succeeded everywhere.

These checks are useful for the exam because they train causal reasoning. If a question says totals doubled after a merge, you immediately think about repeated matches. If categories disappear after an inner join, you think about unmatched keys. If a date conversion fails only for one regional file, you think about locale or source-format inconsistency. Validation turns Power Query from a sequence of actions into an auditable transformation process.

Prioritize the PL-300 data-preparation skills that combine concepts

Some objectives are easier to study in isolation, but the most valuable study time goes to combinations. Storage mode plus refresh behavior is more useful than memorizing storage-mode definitions. Profiling plus business grain is more useful than memorizing where Column quality appears. Merge plus key uniqueness plus row-count validation is more useful than learning join types separately. Reference queries plus staging plus load configuration is more useful than remembering the difference between two context-menu commands.

A practical priority order is: first, source and storage-mode decisions; second, profiling, types, nulls, and import errors; third, merge versus append and key quality; fourth, reshape operations such as pivot, unpivot, grouping, and semi-structured expansion; fifth, reference versus duplicate and staging patterns; sixth, load configuration and refresh-oriented troubleshooting. This order reflects how often the earlier concepts influence everything that comes after them.

If you already know where your weak domains are, use a domain-by-domain readiness matrix to decide whether data preparation deserves more of your remaining study time. The value of a readiness matrix is not the score itself; it is the evidence behind the score. For this domain, that evidence should include successful source choices, defensible transformation decisions, and validation of outputs rather than simple recognition of feature names.

Turn each objective into a decision drill

For Get or connect to data, write scenarios where the same business requirement could plausibly use Import, DirectQuery, Direct Lake, or an existing semantic model. Force yourself to reject the alternatives with specific reasons. Add a credential failure, a privacy-level conflict, and an environment parameter change. For Profile and clean the data, create a small source with duplicated candidate keys, nulls with different meanings, type errors, suspicious outliers, inconsistent text values, and malformed dates. Diagnose before cleaning.

For Transform and load the data, build exercises that change grain deliberately. Group order lines to order totals, then explain what detail was lost. Unpivot month columns into rows. Expand nested JSON records and watch row counts. Create a clean staging query, then branch it with references into fact and dimension outputs. Append monthly files. Merge a lookup with both unique and non-unique keys so you can see row multiplication. Disable load for helper queries and confirm that only final analytical tables appear in the model.

Once the focused concepts are clear, broader practical rehearsal helps test whether you can move through the complete workflow under time pressure. The most useful labs are not demonstrations where every source is clean. They are exercises with intentionally broken data, ambiguous source choices, and validation checkpoints that force you to explain why the final result is trustworthy.

What exam-style data-preparation reasoning usually looks like

PL-300 scenario questions often contain more information than you need. A storage-mode question may mention report design details that do not affect the connection decision. A merge question may include several transformation steps, but the decisive fact is that the lookup key is duplicated. A privacy question may look like a credential failure until you notice that two differently classified sources are being combined. Train yourself to identify the constraint that changes the answer.

Look for verbs and outcome language. ‘Keep the latest source values without importing a full copy’ is different from ‘maximize report interactivity with nightly refresh’. ‘Stack monthly extracts’ signals append, while ‘add customer attributes by CustomerID’ signals merge. ‘Turn Jan, Feb, Mar columns into Month and Amount rows’ signals unpivot. ‘Create one summarized row per product’ signals group and aggregate. ‘Reuse cleaned staging logic so downstream queries inherit future changes’ signals reference rather than duplicate.

Also watch for destructive fixes. Removing every row with an error can make a query load successfully while deleting valid business events. Removing duplicates without defining a key can hide a many-to-many problem. Converting identifiers to numbers can strip leading zeros. Replacing nulls with zero can invent values. The correct answer is often the one that preserves business meaning while solving the technical problem.

A compact readiness checklist for the data-preparation domain

A stronger readiness signal is being able to inspect an unfamiliar source and, before touching Power Query, write down its intended row meaning, plausible key columns, data-quality hazards, and type requirements. You can choose among Import, DirectQuery, Direct Lake, and shared semantic-model reuse from scenario constraints. You can explain credentials and privacy as different concerns. You can use parameters without confusing them with credentials or refresh. You can profile data, classify nulls, trace import errors to the first broken assumption, and explain why a particular cleaning step preserves business meaning.

You should also be able to predict the structural result of group, pivot, unpivot, transpose, merge, append, and nested-data expansion before clicking anything. You can explain how reference and duplicate queries differ, why a staging query might have load disabled, what makes a relationship key appropriate, and why row counts must be checked after joins. Most importantly, you can validate a prepared dataset rather than assuming success because the query refreshes.

Finish data preparation before trusting the model

Good Power BI modeling depends on prepared data with known grain, consistent types, reliable keys, and documented quality decisions. Data preparation is therefore not an introductory phase to rush through on the way to DAX and visuals. In the current PL-300 blueprint it is a major scored domain, and in real analytics work it is the control point where source problems can be prevented from contaminating every downstream result.

Study the domain as a connected system. Source choice affects refresh and transformation behavior. Profiling reveals the defects that cleaning must address. Transformation changes grain and structure. Keys determine whether merges and relationships behave as expected. Load configuration determines what reaches the semantic model. When you can explain those dependencies and validate the results, data-preparation questions stop feeling like isolated Power Query trivia and start looking like variations on a workflow you already understand.