PL-300 Prepare the data Practice Question
A company uses Power BI to analyze sales data from a SQL Server database. The database contains a table 'Sales' with 10 million rows. The business analysts need to create daily reports that aggregate sales by region and product category. To optimize report performance, which data preparation technique should be applied?
⚠ Common exam trap
It's easy for candidates to assume removing columns or filtering rows is sufficient, but the question specifically targets aggregation of millions of rows, where source-side aggregation is the only scalable solution.
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
✓
Perform aggregation in SQL before importing.
Performing aggregation in SQL before importing reduces the data volume from 10 million rows to a much smaller aggregated result set. This minimizes memory consumption and speeds up report rendering in Power BI, as the heavy lifting is done on the SQL Server engine rather than in Power Query or the Power BI data model.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Increase the row limit in Power Query to load all rows.
Why it's wrong here
Increasing the row limit in Power Query only expands the number of rows shown in the preview pane or loaded if you explicitly load them; it does not perform any aggregation. Loading all rows into the data model increases the volume of data stored, slowing refresh times and inflating memory usage without addressing the need for higher-level summaries. This approach fails to reduce row count and therefore does not solve performance issues caused by large transaction tables.
- ✗
Remove unused columns from the query.
Why it's wrong here
Removing unused columns from the query reduces the width of the dataset by trimming attributes that are not needed for analysis, which can lower memory footprint and import time. However, it does nothing to reduce the number of rows, so if the underlying source contains millions of sales transactions, the row count remains exactly the same. This optimization is useful for column-level efficiency but cannot address row-volume problems that hinder aggregation performance.
- ✗
Import the entire table and aggregate in Power BI.
Why it's wrong here
Importing the entire table and then aggregating in Power BI means the full transactional detail is staged in the model before any grouping or summarization occurs. This consumes significant memory during both the refresh and query execution, as Power BI must process every row to compute the aggregation. While Power BI can handle such loads for smaller datasets, it is inefficient and often impractical for high-volume sales data, making it a poor substitute for performing aggregation earlier in the data pipeline.
- ✓
Perform aggregation in SQL before importing.
Why this is correct
Performing aggregation in SQL before importing is a classic pushdown optimization that leverages the database engine to pre-summarize the sales data, so only the aggregated results are transferred to Power BI. This dramatically reduces the row count and the size of the imported dataset, leading to faster refreshes, lower model memory usage, and quicker report responses. It also offloads compute from Power BI to the SQL server, which is generally more scalable for large fact tables.
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.