PL-300 Model the data Practice Question
Exhibit
Refer to the exhibit.
```
let
Source = Sql.Database("server1", "AdventureWorks"),
SalesTable = Source{[Schema="dbo",Item="Sales"]}[Data],
FilteredRows = Table.SelectRows(SalesTable, each [OrderDate] >= #date(2022,1,1) and [OrderDate] <= #date(2022,12,31)),
GroupedRows = Table.Group(FilteredRows, {"ProductID"}, {{"TotalRevenue", each List.Sum([Revenue]), type number}}),
SortedRows = Table.Sort(GroupedRows,{{"TotalRevenue", Order.Descending}}),
Top10 = Table.FirstN(SortedRows,10)
in
Top10You are reviewing a Power Query M script used to create a table in Power BI. The script imports data from SQL Server, filters for orders in 2022, groups by ProductID to sum revenue, sorts descending, and takes the top 10. However, the table loads slowly. You need to improve performance. Which change should you make?
⚠ Common exam trap
A common mix-up: candidates assume buffering (Table.Buffer) or combining steps improves performance, when in reality the key performance gain comes from pushing transformations to the source database (query folding) to minimize data movement.
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
✓
Modify the script to use a native SQL query that performs the filtering and aggregation on the server side.
Pushing filtering, grouping, and aggregation to SQL Server via a native query reduces the volume of data transferred to Power BI and leverages the database engine's optimized execution. This minimizes memory and processing overhead in Power Query, directly addressing the slow load time caused by performing these operations on imported data.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Add a Table.Buffer step before the filter to speed up subsequent operations.
Why it's wrong here
Buffering the entire table with Table.Buffer forces a full in-memory materialization before the filter runs, increasing memory pressure and startup latency without reducing the row count that the filter must process. Since no computation is pushed to the source, the filter still evaluates over every buffered row, and the buffer can even break query folding so that later steps lose the ability to delegate work to the database. This makes the step counterproductive for improving performance on large data models.
- ✗
Remove the sorting step because it is unnecessary for the final table.
Why it's wrong here
Sorting is typically a relatively cheap operation compared to grouping, which must aggregate and count across a large dataset; removing it would only shave off a small fraction of the total processing time. The persistent performance bottleneck is the Group By step that materializes aggregated results, not the ordering of rows. Moreover, the sort may be required for a stable final table layout or for specific DAX functions, so dropping it could introduce inconsistencies without addressing the root cause.
- ✓
Modify the script to use a native SQL query that performs the filtering and aggregation on the server side.
Why this is correct
Rewriting the M script to embed filtering and aggregation in a native SQL query pushes all heavy lifting to the source database engine, which is optimized for set-based operations and can use indexes, statistics, and parallelism. Only the aggregated, filtered result set is sent to Power Query, drastically reducing data transfer and memory usage while also enabling full query folding so the output is computed server-side. This is the correct approach because it minimizes the data volume that Power Query must load and process.
- ✗
Combine the filter, group, and sort into a single step using Table.Buffer.
Why it's wrong here
Wrapping the filter, group, and sort in Table.Buffer does not combine them into an efficient execution plan; it simply caches the result of each nested step in memory, which can consume excessive RAM on a large dataset. More importantly, buffering often breaks query folding, because the buffer materializes the data and forces all subsequent operations to run locally instead of being delegated back to the SQL source. This approach would likely degrade performance by pulling full raw rows into Power Query and then doing the grouping in the less-efficient M engine.
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.