PL-300 Prepare the data Practice Question
You are a business analyst at a manufacturing company. You receive weekly CSV files from different plants. Each file contains columns: PlantID, Date, ProductID, UnitsProduced, and ScrapUnits. However, the files sometimes have missing values in the ScrapUnits column, and occasionally there are duplicate rows (same PlantID, Date, ProductID). You need to prepare a clean dataset for reporting. The requirements are: 1. Combine all CSV files from a folder into a single table. 2. Replace null values in ScrapUnits with 0. 3. Remove duplicate rows based on PlantID, Date, and ProductID, keeping the first occurrence. 4. Ensure the data types are appropriate (e.g., Date as date, UnitsProduced as whole number).
Which sequence of Power Query steps should you use?
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Connect to folder, combine files, replace nulls, remove duplicates, set data types.
It follows the proper Power Query order: connect to the folder and combine the CSV files first, then replace null values in ScrapUnits with 0, then remove duplicate rows based on PlantID, Date, and ProductID keeping the first occurrence, and finally set the data types (Date as date, UnitsProduced as whole number). Replacing nulls before removing duplicates ensures that duplicate detection and the retained first row are based on complete data rather than nulls that could later change values. Setting data types last avoids type-conversion errors on text values that may still contain nulls or duplicates. Option A replaces nulls before combining, which is inefficient and may not apply consistently across all files; Option B sets data types before replacing nulls, which can cause conversion errors; Option C removes duplicates before replacing nulls, so null ScrapUnits values could affect which row is kept.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Connect to folder, replace nulls in each file, combine files, remove duplicates, set data types.
Why it's wrong here
Applying 'Replace Nulls' before combining files in a folder means the transformation must be edited inside the 'Transform Sample File' step, which Power Query then runs once per source file. This multiplies the maintenance effort and creates a risk that different files with missing or differently typed columns will produce inconsistent null replacements or errors. Combining first produces a single table, so you can replace nulls exactly once over the entire dataset using one step, which is simpler and more robust.
- ✗
Connect to folder, combine files, set data types, replace nulls, remove duplicates.
Why it's wrong here
If you set data types immediately after combining files, Power Query wraps the combined table in type-conversion functions that enforce strict types, such as Int64 or DateTime. Replacing nulls afterward with a value that does not match the already-assigned type (for example, placing 'N/A' into a number column) results in a DataFormat.Error. Similarly, replacing nulls before you define typed columns allows the replacement value to influence correct type inference, so null-handling should precede the 'Change Type' step.
- ✗
Connect to folder, combine files, remove duplicates, replace nulls, set data types.
Why it's wrong here
Removing duplicates before standardizing nulls can leave duplicate rows that only appear identical after missing values are normalized, because nulls are treated as a distinct grouping value in Power Query. Also, if a duplicate-key column contains nulls, duplicates removal might not deduplicate as expected until nulls are replaced with a consistent placeholder. The correct sequence is to replace nulls first, then remove duplicates, and only then set data types so that the schema conversion applies to a fully cleaned, deduplicated table.
- ✓
Connect to folder, combine files, replace nulls, remove duplicates, set data types.
Why this is correct
This order is the correct Power Query pipeline for folder-based data: combine the files into one table, replace null values with a chosen sentinel, remove duplicate rows, and finally set the data types. Combining first means all transformations are performed on the aggregated result rather than repeated per file, saving compute and avoiding sample-file issues. Replacing nulls before deduplication ensures duplicates are evaluated on normalized values, and setting types last prevents any conversion from interfering with null handling or duplicate detection.
Go deeper
Related to this question
About these practice questions
One of 524 original PL-300 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PL-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PL-300 exam.