PL-300 · Question #72
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer…
The correct answer is B. From Query Editor, add a SELECT statement that uses a WHERE clause to the source definition. D. From Query Editor, filter the table by Order_date. To efficiently retrieve only the latest 100,000 orders from a large SQL Server table, either push the filter to the source database or apply it within the Power Query Editor.
Question
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series. You have a Microsoft SQL Server database that contains the following tables. The following columns contain date information:
- Date[Month] in the mmyyyy format
- Date[Date_ID] in the ddmmyyyy format
- Date[Date_name] in the mm/dd/yyyy format
- Monthly_returns[Month_ID] in the mmyyyy format
The Order table contains more than one million rows. The Store table has a relationship to the Monthly_returns table on the Store_ID column. This is the only relationship between the tables. You plan to use Power BI Desktop to create an analytics solution for the data. You are modeling the data in Power BI. You need to import only a sample of the data from the Order table. What are two possible ways to achieve the goal? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.
Options
- AFrom Query Editor, create a custom column that uses a custom column formula.
- BFrom Query Editor, add a SELECT statement that uses a WHERE clause to the source definition.
- CIn the Power BI model, create a calculated table.
- DFrom Query Editor, filter the table by Order_date.
- EFrom Query Editor, create a column by using Column From Examples.
How the community answered
(49 responses)- A4% (2)
- B84% (41)
- C10% (5)
- E2% (1)
Why each option
To efficiently retrieve only the latest 100,000 orders from a large SQL Server table, either push the filter to the source database or apply it within the Power Query Editor.
Creating a custom column is a data transformation technique for generating new data, not for filtering or reducing the number of rows loaded.
Adding a SELECT statement with a WHERE clause directly to the source definition in Query Editor allows for query folding, pushing the filter operation to the SQL Server database. This is the most efficient method for large tables, as only the filtered 100,000 rows are transferred over the network and processed by Power BI.
Creating a calculated table in the Power BI model happens *after* the entire source data is loaded into the model, meaning all 1M+ rows would already be loaded, making it inefficient for initial data reduction.
Filtering the table by Order_date in Power Query Editor is a valid way to reduce the dataset size before loading into the Power BI model. While it may not always achieve full query folding for complex filters, it still reduces the data processed and stored in Power BI compared to loading all millions of rows.
Creating a column by using Column From Examples is a technique for deriving new columns based on patterns, not for filtering rows from the dataset.
Concept tested: Data filtering strategies and query folding in Power Query
Source: https://learn.microsoft.com/en-us/power-query/query-folding
Topics
Community Discussion
No community discussion yet for this question.