Microsoft PL-300: Power Query and Data Quality

Data preparation is one of the largest scored areas in the current PL-300 Microsoft Power BI Data Analyst blueprint, and it deserves to be studied as an analytical control point rather than as a collection of Power Query clicks. The decisions made before data reaches a semantic model determine whether relationships behave correctly, whether measures calculate over the intended grain, whether refresh can be trusted, and whether a report is explaining the business or merely visualizing a damaged dataset.

Microsoft’s 2026 PL-300 outline gives substantial weight to preparing data, alongside modeling, visualization and analysis, and managing Power BI. Candidates working toward the Power BI Data Analyst Associate certification should therefore be able to connect source selection, storage mode, profiling, transformation, keys, loading, and refresh behavior into one coherent workflow.

When you practice with PL-300 scenarios, resist the temptation to identify the answer from one feature word. A useful question usually contains a business constraint that changes the technical choice. The candidate’s job is to find that constraint, predict what each transformation will do to the data, and choose the option that preserves meaning.

Begin with source, grain, and storage mode before opening Power Query

Data preparation starts before a transformation is created. Identify where the data lives, how fresh it must be, how large it is, who owns it, and what one row means. A table of order lines has a different grain from a table of orders. A customer snapshot has different update behavior from an event stream. If the grain is not explicit, later deduplication, merging, and relationship design become guesswork.

Storage mode belongs in the same early discussion. Import loads data into the Power BI model and usually provides excellent interactive performance, but freshness depends on refresh. DirectQuery leaves data at the source and sends queries back at run time, which changes performance, source dependency, and modeling constraints. Direct Lake is relevant in Microsoft Fabric scenarios where analytical data can be accessed from OneLake without the conventional import cycle.

The best choice follows requirements rather than preference. A modest departmental model with nightly freshness may fit Import well. A governed operational database that must remain the authoritative source for near-current values may justify DirectQuery. A Fabric solution built around curated OneLake data may make Direct Lake appropriate. Storage mode affects everything that follows, so it should not be treated as a setting chosen after transformations are already built.

Profiling reveals whether the source matches the business story

Power Query profiling tools help candidates examine column quality, distribution, distinct values, errors, nulls, and unexpected patterns. The goal is not to produce a visually clean profile; it is to discover where the source contradicts assumptions.

Suppose ProductCode is expected to be unique in a product table but profiling shows repeated values. That matters before a merge or relationship is created. Suppose a date column contains text that looks like dates but uses different regional formats. A type conversion can silently fail or misinterpret values. Suppose a numeric amount has a small number of extreme values. Those may be errors, or they may represent the most important transactions in the dataset.

Profiling should lead to a business question. Is the duplicate valid? Is null meaningful? Is the outlier possible? This is why a data analyst needs domain context. Cleaning data without understanding what a field represents can make the model more consistent while making it less true.

Types, nulls, and errors should be corrected at the earliest trustworthy point

Data types determine how Power BI stores, sorts, aggregates, and relates values. An identifier that happens to contain only digits may still be text if leading zeros are meaningful. A date stored as text cannot reliably support time intelligence until it is parsed correctly. Decimal and fixed-decimal choices can matter for financial values. Boolean fields should not be left as inconsistent “Yes,” “Y,” “1,” and “true” strings if the business meaning is genuinely binary.

Nulls require the same care. Replacing every missing value with zero can invent activity that did not occur. Dropping every row with a missing optional attribute can discard valid transactions. The correct treatment depends on whether null means unknown, unavailable, not applicable, not yet received, or invalid.

Errors are clues. Trace them to the first transformation that introduced the problem instead of deleting the rows at the end. A locale mismatch, schema change, unexpected text value, or failed expansion often explains why a conversion broke. Strong PL-300 reasoning fixes the assumption, not just the visible error symbol.

Merge, append, group, pivot, and unpivot all change structure in different ways

Append stacks rows from compatible tables. It is the natural operation when monthly extracts share a common schema and represent the same process. Merge adds columns by matching keys between tables. Confusing the two is easy to avoid if you ask whether the result should become taller or wider.

Merge scenarios need deeper validation because join type and key uniqueness affect row counts. If a sales table has one row per order line and the product lookup contains two rows for the same ProductCode, expanding the merge can duplicate sales rows. That is not a Power Query defect. It means the assumed lookup key is not unique at the required grain.

Grouping reduces rows by aggregation. Pivot turns values into columns. Unpivot turns repeated columns into attribute-value rows and is especially useful when a source has months or categories embedded in column names. Before any structural transformation, predict the output shape. After it, check row counts, key uniqueness, and totals. This habit is more valuable than memorizing where each command appears in the interface.

The relationship between prepared structure and later modeling is explored further in Power BI data modeling, because preparation should deliver tables whose grain and keys make the intended model possible.

Reference queries, duplicate queries, and load settings control reuse

A duplicate query creates an independent copy of the current transformation steps. A reference query starts from the result of another query and therefore creates an upstream-downstream dependency. The difference is important when building reusable staging logic.

Imagine a raw sales source that requires column selection, type correction, standardization, and basic quality checks. Several final outputs need the same cleaned base. Referencing the staging query allows those outputs to inherit the common cleanup. Duplicating the original query creates separate chains that can drift as future changes are made.

Not every helper query should load into the semantic model. A staging query can exist to centralize source preparation while only final fact and dimension queries are loaded. Disabling load does not remove the query from the transformation dependency graph; it simply prevents that intermediate result from becoming its own model table.

This pattern helps keep the model intentional. Users should see analytical tables designed for reporting, not every technical step required to produce them.

Keys and star-schema thinking belong in data preparation

A strong key is stable, consistently typed, and unique on the dimension side at the intended grain. Display names are often poor keys because they can repeat or change. Composite business keys may be required when uniqueness depends on multiple attributes. Surrogate keys can be useful, but candidates should understand whether they remain stable when source order or processing logic changes.

Preparation should also anticipate star-schema design. Facts represent measurable events at a defined grain. Dimensions describe the entities through which those events are filtered and grouped. If the data-preparation layer leaves conflicting product identifiers, mixed grains, duplicated dimensions, or inconsistent dates, the model cannot repair those issues cleanly later.

This is why changing DAX calculations is not the right response to duplicated fact rows caused by a bad merge. Measures operate on the model they receive. Data quality and structural correctness should be fixed upstream whenever that is where the defect originates.

Refresh failures should be classified before they are “fixed”

Power BI refresh problems can come from authentication, authorization, gateways, source reachability, privacy isolation, schema changes, parameters, query errors, or service constraints. Treating every failure as a credential problem wastes time and can hide the actual cause.

A disciplined troubleshooting sequence begins with environment and source: are you connecting to the intended server, file, semantic model, or Fabric item? Next check authentication, permissions, gateway or network reachability, and privacy boundaries. Then verify schema, expected columns, types, and query steps. Finally, confirm load behavior and downstream model impact.

The distinction between privacy and credentials is particularly important. Valid credentials do not guarantee that Power Query will combine two sources if privacy rules prevent the data movement implied by the query. Conversely, a query that works in Desktop but fails after publication may point to service connectivity or gateway configuration rather than transformation logic.

Broader PL-300 practical rehearsal should deliberately include broken refreshes, non-unique keys, malformed data, and unexpected source changes. Perfect demo data does not teach troubleshooting.

Readiness means you can validate the prepared result, not just produce it

A transformation is not finished because the preview looks tidy. After filtering, compare the remaining row count with expectations. After deduplication, record the rule that justified removal. After a merge, compare row counts and inspect unmatched keys. After append, reconcile the result to the contributing sources. After creating a dimension, test key uniqueness. After type conversion, inspect errors rather than assuming they are zero.

These checks turn Power Query into an auditable process. They also make scenario questions easier because you learn the failure signatures. Totals doubling after a merge suggest repeated matches. Categories disappearing after an inner join suggest unmatched keys. One regional file failing date conversion suggests locale or schema inconsistency.

A PL-300 readiness matrix is most useful when ratings come from this kind of evidence rather than from confidence alone. Candidates should also review common PL-300 preparation mistakes so that feature memorization does not crowd out hands-on validation.

The central idea is simple: prepare data so the model can be trusted. Source choice affects refresh. Storage mode affects query behavior. Profiling reveals defects. Transformations change structure and grain. Keys determine merge and relationship behavior. Load settings determine what reaches the model. When you can explain those dependencies and validate the result, data-preparation questions stop looking like isolated Power Query commands and start looking like variations on a workflow you understand.