PL-300 Prepare the data Practice Question
You are importing data from a SQL Server database. The source table has a column 'ModifiedDate' of type datetime2. In Power Query, you want to ensure that only rows modified within the last 7 days are loaded. Which THREE steps should you take?
⚠ Common exam trap
A common mix-up: candidates think splitting a datetime column is necessary for date-based filtering, but Power Query's native filter on datetime2 works correctly and is more efficient, while loading all rows and using DAX is a common anti-pattern that wastes resources.
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
✓
Use a parameter for the date range and reference it in the filter.
Using a parameter for the date range and referencing it in the filter allows for dynamic, maintainable filtering in Power Query. This approach leverages Power Query's M language to apply a filter step that can be easily updated without modifying the query logic, ensuring only rows from the last 7 days are loaded during data refresh.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Load all rows and then use a DAX filter in the data model.
Why it's wrong here
Importing every row into the Power Query engine and then applying a DAX filter in the model forces the full table to be transferred, compressed, and stored in memory. Because DAX filters are evaluated only after data is loaded, they consume excessive memory and slow refresh, and they cannot push query logic back to SQL Server. To minimize footprint, the date filter should be applied during data acquisition, not in the semantic model.
- ✓
Use a parameter for the date range and reference it in the filter.
Why this is correct
A Power Query parameter, such as a date range or a scalar date value, can be referenced directly in the filter row step of a query. This makes the filter dynamic and easy to update without editing M code, and when the source is a SQL database, the filter step often folds into the generated SQL statement, reducing the amount of data imported. Parameterizing the date also supports scheduled refresh scenarios, where the filter value can be changed via the API or in the service, and it avoids hard-coded values scattered across steps.
- ✗
Split the column into date and time and then filter on the date part.
Why it's wrong here
Splitting a datetime column into separate date and time components is an unnecessary transformation that adds extra steps, increases query complexity, and can break query folding if the split is performed in M before the filter. A direct filter on the original datetime column using operators such as greater than or less than is sufficient and preserves the ability to push the predicate to the SQL Server. The extra split also consumes more processing time and may require type changes, all to achieve what a simple filtered comparison already provides.
- ✓
In Power Query, add a filter step using a custom column or the filter row feature.
Why this is correct
Adding a filter step in Power Query, either by using the row-filtering interface or an M expression with a custom column, filters data before it reaches the data model, which is correct. However, because the filter is applied after the data has been pulled from the database, it does not reduce network transfer and may be slower than a source-side filter when dealing with large tables. Even so, this approach is valid and often used when the source cannot be modified, and it still prevents unnecessary rows from consuming memory in the semantic model.
- ✓
Use a native SQL query with a WHERE clause to filter at the source.
Why this is correct
Using a native SQL query with a WHERE clause, for example through the SQL Server connection's 'Native query' option, applies the filter on the database server itself. This minimizes the amount of data sent over the network and takes advantage of indexes on the date column, making it the most efficient option for huge datasets. However, you must be careful to correctly parameterize the query in Power Query to avoid SQL injection risks and to ensure that any subsequent Power Query steps still support query folding.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PL-300 question from scratch — 217 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. 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.