PL-300 prepare the data explained
Prepare the data is worth 25–30% of PL-300, the joint-largest domain. It covers three things: connecting to sources and choosing how the data is stored, profiling and cleaning what arrives, and transforming it into tables the model can use. Almost all of it happens in the Power Query editor, and the questions test whether you know which transformation fits a situation, and what it does to rows, columns and refresh.
Get or connect to data
Storage mode
| Mode | What happens | Choose when |
|---|---|---|
| Import | Data is copied into the model and refreshed on a schedule | Data fits in the model and fast visuals matter |
| DirectQuery | Each visual sends a query to the source | Data is very large or must be near real time |
| Direct Lake | Reads Delta tables from OneLake in Microsoft Fabric | Your data already lives in a Fabric lakehouse or warehouse |
Import is the default answer unless the scenario gives a reason against it: data too large, freshness requirement, or rules that forbid copying data. DirectQuery is slower per interaction and limits some DAX and Power Query features, so scenarios with it usually mention a latency or size constraint.
Credentials, privacy levels and parameters
Data source settings hold credentials per source. Privacy levels (Private, Organizational, Public) stop Power Query from sending data from one source to another in a way that could leak it, which can cause the “formula firewall” error when you combine sources. A parameter holds a value, such as a server name, folder path or date, that queries reference, so you can switch from a development to a production source without editing steps.
A shared semantic model can also be a source: you connect a new report to it instead of rebuilding the model.
Profile and clean the data
Power Query offers three profiling views: column quality (valid, error, empty percentages), column distribution (distinct and unique counts) and column profile (statistics and value distribution). By default, profiling covers only the top 1000 rows. If a problem exists further down, switch to profiling the entire data set.
Typical cleaning tasks the exam describes:
- Nulls and blanks: replace values, fill down or up, or filter them out.
- Errors: remove errors, replace errors, or fix the cause. A text value in a column you are converting to a number is the classic cause.
- Inconsistencies: trim, clean, capitalisation, and replace values.
- Import errors: a column that does not exist any more, a changed delimiter, or a changed data type.
Order matters. If a column contains “N/A”, replace that value with null before changing the type to a whole number, or the conversion produces errors.
Transform and load the data
Shape
| Transformation | Use it to |
|---|---|
| Group By | Aggregate rows, such as total per customer |
| Pivot | Turn values of a column into new column headings |
| Unpivot | Turn column headings such as months into attribute-value rows |
| Transpose | Swap rows and columns |
| Parse JSON/XML, expand | Convert semi-structured data into a table |
Prefer Unpivot Other Columns when new columns can appear later: it keeps working when the source adds a month.
Combine
Merge adds columns from another query by joining on a key, with a join kind such as left outer, inner or full outer. Append adds rows from another query with the same columns. Merge is a horizontal operation and append is a vertical one; confusing them is the most common error.
Reference creates a new query that starts from the output of an existing one, so later changes in the original flow through. Duplicate copies the steps into an independent query. Use reference to derive several tables from one cleaned staging query.
Fact and dimension tables
A flat table with order lines and repeated customer and product columns should be split into a fact table of events and dimension tables of descriptive attributes, each with a unique key. Create a key by removing duplicates in a reference query, or add an index column. A star schema is smaller, faster and easier to relate.
Load behaviour
Disable Enable load for staging queries so they do not appear in the model, and disable Include in report refresh only when a query should not refresh with the rest.
Sample questions
Question 1. A column holds order quantities as text. A few values contain the text N/A. When you change the column type to whole number, those rows show errors. You want the rows kept, with no quantity. What should you do?
- A. Change the column type to text
- B. Replace the value N/A with null, then change the type to whole number
- C. Remove all rows that contain N/A
- D. Group the table by quantity
Show answer
Answer: B
Replace N/A with null before converting the type, so the conversion has no non-numeric values to fail on. Changing the column back to text keeps the problem for later. Removing the rows loses the orders. Group By does not repair values.
Want more questions like this? Full PL-300 practice tests →
Question 2. An Excel sheet has one row per product and a column for each month: Jan, Feb, Mar and so on. New month columns are added every month. You need one row per product and month. What should you use?
- A. Pivot Column on the month headings
- B. Transpose the table
- C. Unpivot Other Columns, keeping the product column
- D. Unpivot Selected Columns on the current month columns only
Show answer
Answer: C
Unpivot Other Columns selects the product column to keep and unpivots every other column, so months added later are included automatically. Pivot does the opposite of what is needed. Transpose swaps the whole table orientation without creating attribute-value pairs. Unpivot Selected Columns would miss new months.
Want more questions like this? Full PL-300 practice tests →
Question 3. You have a cleaned query named Sales. You need a Customer query built from it, so that any later change to the cleaning steps of Sales also changes Customer. What should you create?
- A. A reference to the Sales query
- B. A duplicate of the Sales query
- C. An append of Sales to itself
- D. Disable Enable load on Sales
Show answer
Answer: A
A reference query starts from the output of Sales, so changes to the earlier steps flow into it. A duplicate copies the steps into an independent query that does not follow later changes. Append stacks rows and does not derive a new table. Enabling load only controls whether a query loads into the model.
Want more questions like this? Full PL-300 practice tests →
What to practise
Take one messy Excel workbook and one CSV and fix them end to end: import, profile with the entire data set, fix types, replace nulls, unpivot months, split into fact and dimension queries with reference, and drive the folder path from a parameter. Then open Advanced Editor and read the M code you generated. After that, scenario questions become a matter of recognising steps you have already applied.