Microsoft Power BI Quiz: Query Folding And Performance
10 questions · exam conditions
0:00
Query Folding And PerformanceQuestion 1 of 10

You are reviewing a query against a cloud data warehouse. The View Native Query command is unavailable for the final step. A colleague concludes that no steps in the query are folding.

How should you evaluate the colleague's conclusion?

Accept it, because an unavailable View Native Query command proves that Power Query retrieved the complete source table.
Reject it, because the command's availability is not a universal folding test; use folding indicators or diagnostics supported by the connector.
Accept it only if the query contains filters, because folded filters always make the View Native Query command available.
Reject it, because every relational connector folds all transformations even when the generated statement cannot be displayed.
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Query Folding And Performance

Practice Query Folding And Performance in Microsoft Power BI with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Query Folding And Performance, giving you a quick way to practice the rules, question types, and explanations that matter most for Microsoft Power BI.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

You are reviewing a query against a cloud data warehouse. The View Native Query command is unavailable for the final step. A colleague concludes that no steps in the query are folding.

How should you evaluate the colleague's conclusion?

  1. Accept it, because an unavailable View Native Query command proves that Power Query retrieved the complete source table.
  2. Reject it, because the command's availability is not a universal folding test; use folding indicators or diagnostics supported by the connector. (correct answer)
  3. Accept it only if the query contains filters, because folded filters always make the View Native Query command available.
  4. Reject it, because every relational connector folds all transformations even when the generated statement cannot be displayed.
Explanation: 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.

Question 2

A fact table contains eight years of data in Azure SQL Database. You configure incremental refresh by creating RangeStart and RangeEnd parameters. The query first adds an index column and then filters the transaction timestamp using the parameters. Policy validation succeeds, but the initial refresh still transfers the full table.

What should you do to address the most likely query-folding problem?

  1. Move the parameterized timestamp filters before the index step and confirm that the source receives the range predicates. (correct answer)
  2. Replace RangeStart and RangeEnd with text parameters so the service can substitute partition names during refresh.
  3. Keep the current order and increase the incremental refresh retention period so fewer source queries are generated.
  4. Add Table.Buffer before the index step so the service can reuse the buffered table for all incremental partitions.
Explanation: 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.

Question 3

A query loads sales from SQL Server, filters the sales rows by fiscal year, and then merges them with a small product classification file stored in Excel. The Excel file is not available to SQL Server. The merge is performed in Power Query.

Which statement best describes the likely folding and performance behavior?

  1. The fiscal-year filter can fold to SQL Server, while the cross-source merge is likely performed by the Power Query engine. (correct answer)
  2. The Excel merge causes every preceding SQL step to unfold, so SQL Server must always return the complete sales table.
  3. The merge can fold to SQL Server because Power Query automatically uploads small Excel sources as temporary database tables.
  4. Neither step can fold because a query that references two source types is evaluated entirely by the Power Query engine.
Explanation: 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.

Question 4

A developer inserts Table.Buffer immediately after connecting to a large SQL table. The developer expects the buffer to cache the source rows and allow subsequent filters to run faster during every scheduled refresh.

What is the most accurate assessment of this design?

  1. It is likely slower because buffering materializes the input and can prevent later filters from being folded to SQL Server. (correct answer)
  2. It is likely faster because the buffer persists across scheduled refreshes and eliminates repeated database queries for the table.
  3. It has no effect because Table.Buffer changes only row ordering and does not influence evaluation or query folding.
  4. It is likely faster because Table.Buffer directs SQL Server to cache the generated statement before applying source predicates.
Explanation: 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.

Question 5

A model uses DirectQuery against a relational database. A developer proposes an M transformation that calls a custom text-processing function for every source row. The connector cannot translate the function into a source expression.

Why is this proposal especially problematic for DirectQuery?

  1. DirectQuery requires all text functions to be evaluated locally, but locally evaluated functions are cached permanently after first use.
  2. DirectQuery imports the transformed table at publication, so the function increases model size but not report-query latency.
  3. DirectQuery automatically rewrites unsupported M functions as DAX calculated columns evaluated by the report visual.
  4. DirectQuery relies on source-executable queries, so a nonfoldable row function may be rejected or make interactive querying impractical. (correct answer)
Explanation: 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.

Question 6

A Power BI Desktop query imports 180 million rows from a relational database. The current steps are: connect to the source, add a custom column by calling an M function that the connector cannot translate, filter to the last 30 days, and remove unused columns. Refresh is slow, and database monitoring shows that nearly all rows are transferred.

Which change is most likely to improve refresh performance by increasing query folding?

  1. Move the date filter and column removal before the custom function, and then verify that those earlier steps fold. (correct answer)
  2. Keep the step order unchanged, but disable parallel loading so the custom function receives rows in a stable sequence.
  3. Move the custom function to the first step so Power Query can evaluate it before requesting rows from the database.
  4. Apply Table.Buffer immediately after connecting so subsequent filters are evaluated against a consistent in-memory snapshot.
Explanation: 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.

Question 7

A query against a database has six transformation steps. Folding indicators report that the first four steps fold, the fifth step does not, and the sixth step applies a highly selective customer filter. The query returns only 500 rows, but refresh remains slow.

Which explanation is most consistent with the reported behavior?

  1. The first four folded steps are reevaluated locally once any later step fails to fold, so none of their source work is retained.
  2. The final row count proves the customer filter folded, because local filters cannot reduce a query to fewer than 1,000 rows.
  3. The fifth step creates a folding boundary, so the database may send many rows before the final filter reduces them locally. (correct answer)
  4. A partially folded query always transfers only the final result rows, although local processing may increase model compression time.
Explanation: 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.

Question 8

Two queries contain the same sequence of M operations: filter rows, select columns, group by customer, and calculate a sum. One query uses SQL Server; the other uses a basic web API connector. The SQL query folds through the grouping step, but the web API query does not.

What best explains the difference?

  1. The web query would fold identically if its M step names matched the default names generated for SQL Server.
  2. Grouping folds only when the source contains primary keys, and SQL Server automatically creates them for every imported query.
  3. Web API sources cannot apply any server-side filters, while relational sources always fold every valid M transformation.
  4. Folding depends on connector and source capabilities, not solely on whether an M operation is conceptually translatable. (correct answer)
Explanation: 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?"

Question 9

On a supported relational connector, a query uses Value.NativeQuery to return orders from a SQL statement. A later M step filters the result to one region. Source monitoring shows that the region filter is not included in the database request.

Which modification should you consider if you want later supported transformations to fold over the native query?

  1. Wrap the Value.NativeQuery result in Table.Buffer so Power Query can append the region predicate to the SQL statement.
  2. Invoke Value.NativeQuery with the connector's EnableFolding option set to true, and verify the resulting source statement. (correct answer)
  3. Convert the native query result to a list before filtering so the connector can translate the predicate independently.
  4. Disable native database queries globally so Power Query can reconstruct the original SQL statement from the M steps.
Explanation: 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.

Question 10

A staging query connects to a SQL table and performs several foldable cleanup steps. Three load-enabled queries reference the staging query and then apply different foldable filters. The developer assumes Power Query will retrieve the staging result once and share it among all three queries during every refresh.

Which statement most accurately describes the performance implication?

  1. References prevent folding in dependent queries because each reference is evaluated only after the staging query is loaded.
  2. References always cache the staging rows once, so only the first dependent query can send a statement to SQL Server.
  3. References preserve reusable logic, but they do not guarantee one shared materialization; separate folded source queries may be issued. (correct answer)
  4. References combine all dependent filters with OR into one source statement and then divide the returned rows locally.
Explanation: 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.