- What should you do to address the most likely query-folding problem?
Answer: Move the parameterized timestamp filters before the index step and confirm that the source receives the range predicates.
Whenever you see a question about incremental refresh in Power BI, the critical concept to understand is query folding — the ability of Power Query to push transformation steps back to the source database as native SQL. Incremental refresh depends on query folding to send date-range predicates to the source, so only the relevant partition rows are retrieved rather than the full table.
The problem here is step ordering. When you add an index column before filtering on the timestamp parameters, Power Query must first materialize the entire table to assign sequential index values — query folding breaks at that point. Any subsequent filter cannot be folded back to the source because the engine is already working with an in-memory result set. Moving the parameterized RangeStart/RangeEnd timestamp filters before the index step (answer A) ensures the source receives the WHERE clause directly, so only the targeted date range is transferred. You can verify this by right-clicking the filter step and checking "View Native Query."
B is wrong because RangeStart and RangeEnd must be DateTime parameters, not text — the service substitutes actual date values into them during partition refresh, and changing them to text breaks this mechanism entirely. C is a red herring; extending the retention period changes how many historical partitions are kept, not whether the source query is folded — it does nothing to fix full-table transfers. D is counterproductive: Table.Buffer forces evaluation into memory before folding can occur, which makes the folding problem worse, not better.
As a study tip: whenever incremental refresh isn't reducing data transfer, immediately suspect a step that breaks query folding — and always check step order relative to your parameter filters.
- What is the most accurate assessment of this design?
Answer: It is likely slower because buffering materializes the input and can prevent later filters from being folded to SQL Server.
When working with Power Query and large data sources, the critical concept to keep in mind is query folding — the mechanism that pushes transformation steps back to the source database as native SQL. Understanding how Table.Buffer interacts with this mechanism is exactly what this question tests.
Placing Table.Buffer early in a query forces Power Query to fully materialize the entire table into memory before any subsequent steps run. This breaks the query folding chain, meaning any filters you apply afterward cannot be translated into SQL WHERE clauses. Instead of SQL Server returning only the filtered rows, your engine pulls every row into memory first, then filters locally. For large tables, this is almost always a performance regression, making A the correct answer.
B is wrong on two counts: Table.Buffer does not persist between refreshes at all — it only lives for the duration of a single evaluation session. Each scheduled refresh starts fresh, so the claim of "eliminating repeated database queries" is simply false.
C is incorrect because Table.Buffer does far more than affect row ordering (that would be Table.Sort concerns). It explicitly breaks query folding, which has a real and measurable impact on evaluation behavior and performance.
D describes behavior that doesn't exist. Table.Buffer has no mechanism to instruct SQL Server to cache anything server-side. It is a client-side, in-memory operation entirely within the Power Query engine.
As a study tip: whenever you see Table.Buffer or List.Buffer in exam scenarios, immediately ask yourself "does this break query folding?" — that question will almost always point you to the right answer.
- Why is this proposal especially problematic for DirectQuery?
Answer: DirectQuery relies on source-executable queries, so a nonfoldable row function may be rejected or make interactive querying impractical.
When you see a question combining DirectQuery and M transformations, focus on one core principle: DirectQuery works by sending queries to the source database at report runtime, not by storing data locally. This means every transformation must be translatable ("foldable") into a source-native query — typically SQL. If a transformation can't fold, Power BI must retrieve raw data and process it in-memory, which is catastrophic for interactive performance at scale.
That's exactly why D is correct. A custom text-processing function that the connector cannot translate into a source expression breaks query folding. DirectQuery will either reject the transformation outright or be forced to pull every source row into the Power Query engine and apply the function locally — row by row. For large tables with frequent report interactions, this makes interactive querying completely impractical, since every visual click triggers this expensive operation.
A is wrong on two counts: DirectQuery doesn't require text functions to be evaluated locally by design, and there's no concept of permanently caching locally evaluated functions in this context. B describes Import mode behavior, where data is loaded at publication and stored in the model — DirectQuery never imports or stores table data, so there's no model-size tradeoff here. C fabricates a feature that doesn't exist; Power BI does not automatically rewrite unsupported M functions as DAX calculated columns, and DirectQuery has strict limitations on calculated columns anyway.
As a study tip, always ask yourself: "Does this operation fold to the source?" In DirectQuery, foldability isn't a nice-to-have — it's the foundation of the entire architecture.
- Which statement best describes the likely folding and performance behavior?
Answer: The fiscal-year filter can fold to SQL Server, while the cross-source merge is likely performed by the Power Query engine.
When you see a question about Power Query folding across multiple data sources, the key concept to anchor on is that query folding only works within a single connectable source. SQL Server supports folding; Excel does not. But critically, this limitation is surgical — it doesn't automatically destroy folding for every step in your query.
Power Query evaluates folding step by step. Steps that can be translated into native SQL (like filtering by fiscal year) will fold to SQL Server and execute there. Once you introduce a cross-source merge — pulling in an Excel file that SQL Server cannot access — Power Query's engine takes over for that specific operation and everything downstream. The fiscal-year filter, however, was already foldable before the merge was introduced, so SQL Server handles that filtering efficiently, reducing the rows returned. This makes A the correct answer: partial folding is real, and it's the expected behavior here.
B is wrong because it describes "fold breaking" as a cascading, total collapse — implying SQL Server must return the full table. That's a common misconception. Power Query is smarter than that; steps prior to the fold-breaking operation can still fold.
C is wrong because Power Query does not upload Excel data as temporary SQL tables. No such automatic feature exists, and this would require write permissions and significant overhead.
D is wrong because it overgeneralizes. Having two source types doesn't disable folding entirely — only the steps that require combining them are handled locally.
Your study tip: think of folding as a waterfall that can be interrupted mid-stream, not an all-or-nothing switch. Steps before a fold-breaking operation can still execute natively at the source.
- Which modification should you consider if you want later supported transformations to fold over the native query?
Answer: Invoke Value.NativeQuery with the connector's EnableFolding option set to true, and verify the resulting source statement.
When working with native queries in Power Query, understanding query folding is essential. Query folding is the ability for Power Query to push transformation steps back to the data source as native SQL. By default, when you use Value.NativeQuery to execute a raw SQL statement, Power Query treats the result as an opaque dataset — it doesn't know how to append additional predicates to your custom SQL, so subsequent filters run locally in memory instead.
To restore folding capability, you need to explicitly signal to the connector that it should attempt to fold subsequent M transformations back into the native query. This is done by passing [EnableFolding = true] as an option to Value.NativeQuery. With this flag set, the connector tries to wrap your native SQL as a subquery and attach additional clauses — like your region filter — to it, which you can verify by inspecting the resulting source statement in your monitoring tool. That's exactly what option B prescribes, making it the correct choice.
Option A is wrong because Table.Buffer forces evaluation of the entire result into memory, which prevents folding rather than enabling it — the opposite of what you want. Option C is wrong because converting the result to a list destroys the tabular structure that connectors use to translate predicates; lists simply cannot fold SQL WHERE clauses. Option D is wrong because disabling native database queries globally removes your ability to execute custom SQL entirely and does nothing to help Power Query reconstruct or fold the original logic.
As a study tip: on Power BI exam questions about native queries and folding, look for the EnableFolding option as the bridge between raw SQL execution and continued M-step optimization — it's the only mechanism designed specifically for this purpose.
- What best explains the difference?
Answer: Folding depends on connector and source capabilities, not solely on whether an M operation is conceptually translatable.
When you encounter Power BI questions about query folding, the key is understanding that folding is a capability negotiation between Power Query and the data source — it's never guaranteed simply because a transformation makes logical sense.
Query folding occurs when the Power Query engine can translate M operations back into native source queries (like SQL). Whether this happens depends entirely on two things: what the connector supports and what the source system can execute. SQL Server's connector is purpose-built to communicate query capabilities back to the engine, enabling it to push operations like grouping and aggregation to the database. A basic web API connector, by contrast, typically retrieves raw data and hands it to Power Query with no mechanism for translating subsequent steps into API-level logic.
D is correct because it captures this accurately: folding is gated by connector architecture and source capabilities, not by whether an operation is conceptually equivalent between sources.
A is wrong because step naming in M has no bearing on folding. The engine doesn't determine fold eligibility based on variable names — it queries the connector's declared capabilities.
B is wrong because primary keys are not a prerequisite for query folding. Grouping and aggregation can fold on sources without primary keys, and this claim misrepresents how SQL Server imports work entirely.
C is wrong because it overgeneralizes in both directions. Some web API connectors can support partial folding through custom parameters, and relational databases don't always fold every valid transformation — certain M steps break the fold chain regardless of source.
As a study tip: whenever you see a folding question, ask yourself "what does the connector support?" not "is this transformation logically possible?"
- Which statement most accurately describes the performance implication?
Answer: References preserve reusable logic, but they do not guarantee one shared materialization; separate folded source queries may be issued.
Whenever you see a question about Power Query references and performance, focus on how the engine decides to materialize intermediate results — because references behave differently than many developers expect.
In Power Query, creating a reference to a staging query means the dependent queries share the logic of that staging query, not necessarily a cached copy of its output. When query folding is active, Power Query may push each dependent query's filter all the way back to the SQL source as a separate folded statement. So instead of one trip to SQL Server that fetches staging rows once, you could see three distinct SQL queries — each combining the staging cleanup logic with its own filter. C is correct because it accurately describes this: references preserve reusable, readable logic, but they do not guarantee a single shared materialization of the staging data.
A is wrong because references do not block folding in dependent queries. Folding can and does propagate through references when the source supports it — that's actually one of their strengths.
B is wrong because Power Query does not automatically cache the staging result so only one query contacts the source. Without explicit caching (such as enabling query caching or using a buffer step), each dependent query may independently hit the source.
D is wrong because Power Query does not merge dependent filters with OR into one combined statement and then split results locally. That describes a behavior the engine simply doesn't perform.
Your study tip: remember that "reference = shared logic, not shared cache." If you need true single-materialization, look into query caching settings or intermediate staging tables — don't rely on references alone.
- How should you evaluate the colleague's conclusion?
Answer: Reject it, because the command's availability is not a universal folding test; use folding indicators or diagnostics supported by the connector.
When working with Power Query and query folding, it's important to understand that View Native Query is just one diagnostic tool — and it has known limitations. Query folding occurs when Power Query pushes transformations back to the source as a native query (like SQL). However, whether that native query can be displayed depends on the connector's capabilities, not solely on whether folding is happening.
The correct answer is B. The unavailability of View Native Query on the final step does not prove that no steps are folding. Many connectors support folding but cannot render a human-readable native query at every step. Power BI provides better diagnostic approaches — such as the folding indicators in the Query Editor (the step-level folding icons introduced in recent versions) and Query Diagnostics, which captures actual backend requests. These tools give you a more complete picture of what is and isn't being folded.
Choice A is wrong because it makes an overly confident conclusion from a single, limited signal. The command being unavailable does not mean the source table was retrieved in full — folding may still be occurring without the UI being able to surface it.
Choice C is wrong because it invents a rule that doesn't exist. Folded filters do not guarantee the command becomes available; connector support determines that, not the type of transformation.
Choice D is wrong in the opposite direction — it overclaims that every relational connector folds all transformations, which is false. Many transformations (custom columns, certain merges, etc.) commonly break the folding chain.
Study tip: On Power BI exam questions about folding, treat View Native Query as a helpful but incomplete hint — always pair it with folding indicators and diagnostics for a complete diagnosis.
- Which explanation is most consistent with the reported behavior?
Answer: The fifth step creates a folding boundary, so the database may send many rows before the final filter reduces them locally.
When you see a question about Power BI query performance and folding indicators, focus on where the folding boundary is and what happens to data volume at that boundary. Query folding means the database engine handles transformations server-side — when folding breaks, everything from that point onward executes locally in Power Query using whatever rows the database already sent.
Here, the fifth step breaks the folding chain. This means steps one through four are pushed to the database efficiently, but step five forces Power Query to pull a result set locally. The critical problem is that step six's highly selective filter hasn't folded — it runs locally after the data arrives. So the database may send a massive intermediate result set (potentially millions of rows), and only then does Power Query trim it down to 500 rows. That large data transfer explains the slow refresh despite the tiny final output — which is exactly what C describes.
A is wrong because folded steps don't get "reevaluated locally" after a boundary — the database still executes steps one through four server-side; the boundary just determines where the handoff happens, not whether earlier work is discarded.
B is wrong because row count tells you nothing about whether a filter folded. Local filters can absolutely reduce a result to any number of rows — there is no "1,000 row" threshold, which is a fabricated rule.
D is wrong because a partially folded query does not transfer only the final result rows. The whole point of the folding boundary problem is that pre-boundary rows are transferred in bulk before local steps reduce them.
Study tip: Always ask yourself, "What rows are being transferred across the network?" If a selective filter sits after the folding boundary, assume the worst — the full unfiltered dataset travels locally before reduction occurs.
- Which change is most likely to improve refresh performance by increasing query folding?
Answer: Move the date filter and column removal before the custom function, and then verify that those earlier steps fold.
Whenever you see a Power BI refresh performance question involving query folding, your first instinct should be to identify what breaks the fold and whether you can restructure steps to push more work back to the database engine.
Query folding is Power Query's ability to translate transformation steps into native SQL (or equivalent) that runs on the source database. The moment Power Query hits a step it cannot translate — like a custom M function your connector doesn't understand — folding stops for all steps after that point. Everything downstream must be pulled into memory and processed locally. In this scenario, the custom function appears before the date filter and column removal, so those critical row-reduction steps execute locally against all 180 million rows — hence the massive data transfer.
Answer A is correct because moving the date filter and column removal before the custom function allows those steps to fold to the database. The database then returns a small, pre-filtered, trimmed dataset, and the custom function operates on far fewer rows in memory. Verifying that earlier steps fold confirms the optimization actually worked.
Answer B is a red herring — parallel loading affects concurrency, not whether transformations are translated to SQL. Disabling it has no impact on folding.
Answer C reverses the logic entirely. Running the custom M function as the first step guarantees folding breaks at the very beginning, forcing all 180 million rows to be fetched before any filtering occurs — making performance worse, not better.
Answer D is similarly counterproductive. Table.Buffer explicitly forces all data into memory, which prevents folding on subsequent steps rather than enabling it.
Remember this rule: steps before a fold-breaking operation can fold; steps after cannot. On the exam, whenever you see a custom function or unsupported transformation, ask yourself what can be reordered to appear before it.
