PL-300 Prepare the data Practice Question
You are importing a large dataset from a CSV file using Power Query. The file contains 50 columns, but you only need 10 for your report. What is the most efficient way to reduce the amount of data loaded into the model?
⚠ Common exam trap
Many candidates confuse 'hiding' columns with actually removing them, or incorrectly assume SQL-like filtering can be applied to flat files, leading them to choose options that still load unnecessary data into memory.
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
✓
Remove the unnecessary columns in Power Query before loading.
Power Query processes data before it enters the Power BI model. Removing unnecessary columns at the query stage reduces the amount of data loaded into memory, improving performance and reducing storage. This is the most efficient approach as it minimizes the dataset size from the start, unlike post-load methods that still consume resources.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Remove the unnecessary columns in Power Query before loading.
Why this is correct
Removing unnecessary columns in Power Query before loading is the most efficient method because it reduces the number of columns imported into the VertiPaq in-memory engine. Power Query transformations that discard columns are applied during the read/load cycle, so the CSV parser only passes the selected columns to the model, reducing memory usage, disk footprint, and refresh time. This early reduction also improves compression ratios and query performance across reports that reference the table.
- ✗
Load all columns and then hide the unnecessary ones in the report.
Why it's wrong here
Hiding columns in the report layer does not remove them from the Power BI data model; they are still fully loaded, compressed, and stored in VertiPaq, consuming memory and contributing to refresh and evaluation overhead. The Hidden property only affects visibility in the Fields pane—users can't see them but they remain queryable via DAX and still occupy space. This approach addresses security/display needs, not data volume reduction, and is therefore not a valid technique for shrinking a large dataset.
- ✗
Use a SQL query to select only the needed columns if the data source supports it.
Why it's wrong here
CSV files are plain text files with no relational query engine, so you cannot issue a SQL query to push column selection down to the source as you could with SQL Server or another database. While query folding is a powerful performance feature for sources that support it (e.g., reducing columns/rows at the server), Power Query must read the entire CSV stream before any column removal can occur. Consequently, this option is inapplicable for CSV imports and would not achieve early column pruning.
- ✗
Create a DAX calculated table that selects only the needed columns.
Why it's wrong here
A DAX calculated table operates after the source data has already been loaded into the model, meaning the entire CSV dataset—including all unnecessary columns—is imported and stored in memory first. The calculated table then creates a new table by copying only the selected columns, which actually increases the total memory footprint because both the original table and the new table coexist in the model. This approach also adds a refresh dependency and additional evaluation cost, making it an inefficient and counterproductive way to reduce data volume.
Go deeper
Related to this question
About these practice questions
This PL-300 question is part of Courseiva's 524-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on PL-300
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. You have a Power BI report that uses a dataset with many columns. You want to reduce the dataset size by removing columns that are not used in any report visual. What is the best practice?
medium- ✓ A.Remove the columns in Power Query Editor before loading
- B.Use the Q&A feature to exclude columns
- C.Apply report-level filters to exclude the columns
- D.Hide the columns in the model view
Why A: Removing columns in Power Query Editor before they are loaded into the data model physically excludes them from the dataset, reducing its size and improving performance. This is the only method that prevents unused columns from consuming memory and storage in the in-memory VertiPaq engine.
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.