How to Minimize Data Load from a Large Azure SQL Table in Power Query
You connect to a large Azure SQL Database table with over 100 million rows. You need to create a report that shows sales by month for the current year only. Which data reduction technique should you use in Power Query to minimize data load?
Quick Answer
The correct answer is to apply a date filter in Power Query on the source query so only current year data is imported. This technique works because Power Query performs query folding, pushing the filter down to Azure SQL Database as a WHERE clause in the native SQL query, meaning only the filtered rows—rather than the entire 100-million-row table—are transferred over the network and loaded into the Power BI data model. On the PL-300 exam, this scenario tests your understanding of query folding and source-level filtering as the most efficient data reduction strategy for large databases; a common trap is to load all data and filter later in the report, which wastes memory and refresh time. Remember the key principle: always filter as early as possible in the data retrieval process, ideally at the source query level. A useful memory tip is "fold it early, load it lightly"—if Power Query can fold the filter, the database does the heavy lifting.
⚠ Common exam trap
Watch out — candidates often assume visual-level filters or DAX calculated tables are sufficient for performance, but they fail to realize that data reduction must occur at the data source or during import to minimize memory and refresh time.
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
✓
In Power Query, apply a date filter on the source query so only current year data is imported.
Applying a date filter in Power Query at the source query level ensures that only rows from the current year are imported into the Power BI data model. This reduces the data volume from over 100 million rows to a fraction, minimizing memory usage and improving refresh performance. Power Query pushes the filter down to the Azure SQL Database using a WHERE clause in the SQL query, so only the filtered data is transferred over the network.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Import all data and then remove columns that are not needed.
Why it's wrong here
Still loads all rows.
- ✓
In Power Query, apply a date filter on the source query so only current year data is imported.
Why this is correct
Query Folding pushes the filter to the database.
- ✗
Load all data and filter using a visual-level filter in the report.
Why it's wrong here
This loads unnecessary data, causing slow performance.
- ✗
Use a calculated table in DAX to filter the data.
Why it's wrong here
Calculated tables are built after data load, so they don't reduce load.
Go deeper
Related to this question
About these practice questions
This PL-300 question is part of Courseiva's 217-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
2 more ways 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 are connecting to an Azure SQL Database from Power BI Desktop. The database contains a view that returns thousands of rows. You only need the last 100 rows for analysis. What is the most efficient way to reduce the data loaded?
easy- A.Write a native SQL query with a WHERE clause to limit rows
- B.Use DirectQuery mode and add a filter in the report
- C.Import all rows and then remove rows in Power Query
- ✓ D.Use the 'Keep Top Rows' transformation in Power Query after applying a sort
Why D: It uses Power Query's 'Keep Top Rows' transformation after sorting the view by the desired order (e.g., descending on a date column). This approach pushes the sort and row-limiting logic to the source database via query folding, ensuring only the last 100 rows are transferred over the network, which is the most efficient method for reducing data loaded.
Variation 2. You are connecting Power BI to an Azure SQL Database. The database contains a table 'Orders' with 10 million rows. You need to minimize the data load time and ensure that only the most recent 30 days of data are imported. Which approach should you use?
hard- A.Import the entire table and apply a date filter in Power Query Editor.
- ✓ B.Enable incremental refresh policy with a filter on the date column.
- C.Use DirectQuery mode and apply a date filter in the report.
- ✓ D.Use a Native Query that selects only the last 30 days of data.
Why B: Both a native query with a date filter (D) and an incremental refresh policy with a date filter (B) minimize data load by pushing filtering to the source and importing only the recent 30 days. Incremental refresh is not limited to ongoing refreshes; it can also control the initial load range.
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.