Microsoft Power BI Quiz: Applied Steps And Query Folding
10 questions · exam conditions
0:00
Applied Steps And Query FoldingQuestion 1 of 10

A Power BI Desktop query imports 80 million rows from SQL Server. Its Applied Steps are: Source, Navigation, Added Custom, Filtered Rows, and Removed Columns. Added Custom invokes an M function that cannot be translated to SQL. Filtered Rows retains approximately 2% of the source rows. Refresh is slow, but the custom calculation must be preserved.

Which change is most likely to reduce the amount of data retrieved from SQL Server without changing the final result?

Move Filtered Rows before Added Custom, and verify that the filter folds at that earlier step.
Move Removed Columns before Navigation, and verify that column removal folds into the source query.
Keep the order unchanged, but enable background preview downloads for the query in Power Query.
Insert Table.Buffer before Added Custom so that SQL Server processes the remaining steps together.
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Applied Steps And Query Folding

Practice Applied Steps And Query Folding 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 Applied Steps And Query Folding, 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

A Power BI Desktop query imports 80 million rows from SQL Server. Its Applied Steps are: Source, Navigation, Added Custom, Filtered Rows, and Removed Columns. Added Custom invokes an M function that cannot be translated to SQL. Filtered Rows retains approximately 2% of the source rows. Refresh is slow, but the custom calculation must be preserved.

Which change is most likely to reduce the amount of data retrieved from SQL Server without changing the final result?

  1. Move Filtered Rows before Added Custom, and verify that the filter folds at that earlier step. (correct answer)
  2. Move Removed Columns before Navigation, and verify that column removal folds into the source query.
  3. Keep the order unchanged, but enable background preview downloads for the query in Power Query.
  4. Insert Table.Buffer before Added Custom so that SQL Server processes the remaining steps together.
Explanation: When a Power BI query contains a step that breaks query folding (like a custom M function), all subsequent steps must execute locally in Power Query rather than being pushed back to SQL Server. Understanding this is critical: the goal is to let SQL Server do as much filtering as possible before the fold-breaking step is reached. In this scenario, Added Custom (the fold-breaking step) currently sits before Filtered Rows, meaning SQL Server sends all 80 million rows to Power Query before any filtering occurs. Option A fixes this by moving Filtered Rows before Added Custom — if the filter folds at that earlier position, SQL Server applies the WHERE clause natively and returns only ~1.6 million rows (2% of 80M) to Power Query. The custom calculation then runs on that dramatically smaller dataset. The final result is identical because the filter logic hasn't changed, only its execution location. Option B is tempting but flawed: Removed Columns affects column projection, not row filtering. Even if column removal folds, you'd still retrieve all 80 million rows — just with fewer columns. This helps bandwidth marginally but doesn't address the core bottleneck. Option C is a distractor about a UI performance setting that affects preview rendering in the Power Query editor, not actual data refresh volume. It has no impact on how much data SQL Server sends during a real refresh. Option D misunderstands Table.Buffer — it caches data in memory within Power Query and actually prevents folding for downstream steps, making the problem worse, not better. Your study tip: whenever a fold-breaking step exists in a query, always ask "can I move any filters upstream of that step so SQL Server handles row reduction first?"

Question 2

You create a Power Query query against Azure SQL Database by using a native SQL statement. You must add ordinary Power Query filters after the native statement and want those filters to be pushed back to Azure SQL whenever the connector supports doing so.

Which implementation should you use?

  1. Run the statement through Sql.Database with the Query option and assume every later step will fold automatically.
  2. Use Value.NativeQuery against the database target and set EnableFolding to true in its options record. (correct answer)
  3. Convert the statement into an M custom function and invoke the function before applying the filters.
  4. Buffer the native-query result before filtering so Power Query can inspect the complete result locally.
Explanation: When working with native SQL queries in Power Query, you need to understand query folding — the mechanism that pushes transformation steps back to the data source as SQL, rather than pulling all data into memory first. The critical question here is: how do you enable folding after a native SQL statement? Value.NativeQuery is the correct tool when you want to execute a raw SQL statement against a source and preserve the ability to fold subsequent M steps back to that source. By passing [EnableFolding=true] in its options record, you explicitly tell the engine to attempt pushing downstream filters back to Azure SQL as additional SQL clauses. This is option B, and it's the only approach that achieves both goals simultaneously. Option A is tempting but flawed. Using Sql.Database with the Query option does execute your SQL, but the result is treated as an opaque block — subsequent steps cannot fold back through a custom SQL string in this context. Folding essentially stops at that boundary. Option C is a red herring. Wrapping the statement in a custom M function doesn't introduce folding capability — it just reorganizes your code. Folding depends on the connector and query structure, not function abstraction. Option D is the opposite of what you want. Buffering with Table.Buffer forces the entire result into memory before filtering, which completely prevents any query folding for those steps. A useful study tip: whenever a Power BI question mentions native SQL plus folding, think Value.NativeQuery with EnableFolding=true — it's the designated bridge between raw SQL and foldable M transformations.

Question 3

A query against SQL Server has eight Applied Steps. View Native Query is available when the fourth step is selected but unavailable when the eighth step is selected. The query includes a custom text transformation between those steps.

What is the best method to determine where folding stops?

  1. Select successive Applied Steps and inspect folding information to identify the first step that cannot be represented by the source query. (correct answer)
  2. Select only the Source step because folding status is fixed when the source connection is initially created.
  3. Refresh the final query twice and treat a shorter second refresh as evidence that all eight steps fold.
  4. Disable load for the query because View Native Query is unavailable only for queries loaded into the model.
Explanation: When working with query folding in Power BI, you need to understand that folding can stop at any intermediate step — not just at the beginning or end. The key diagnostic tool is the View Native Query option in the Applied Steps pane: it's available when a step folds back to the data source, and grayed out when folding has broken down. This is exactly why A is correct. By clicking through each Applied Step sequentially and checking whether View Native Query remains available, you can pinpoint the precise step where folding stops. The passage tells you folding works at step four but not step eight, and a custom text transformation exists between them — transformations like custom text operations are classic folding-breakers because the SQL engine can't represent them natively. Stepping through lets you find the exact culprit. B is wrong because folding status is not fixed at the Source step. Each subsequent transformation is evaluated independently for whether it can be pushed to the source. C is wrong because comparing refresh times is unreliable and circumstantial. Refresh duration depends on many factors unrelated to folding, making this a poor diagnostic method. D is wrong because View Native Query availability is determined by foldability, not by whether the query is loaded into the model. Disabling load changes nothing about folding behavior and provides no diagnostic information. As a study tip: whenever a Power BI question mentions query folding, think "step-by-step inspection." View Native Query is your go-to tool, and any non-native transformation — especially custom text operations — is your prime suspect for where folding breaks.

Question 4

Two queries select Orders and Customers from the same SQL Server database. Each query contains only transformations that currently fold. You merge Customers into Orders by CustomerID and then expand CustomerSegment. A colleague proposes buffering Customers before the merge to guarantee that the join occurs on SQL Server.

What should you do to maximize the chance that the join is executed by SQL Server?

  1. Buffer Customers before the merge so Power Query can upload the cached table to SQL Server.
  2. Keep both inputs foldable and perform the merge without buffering either query before the join. (correct answer)
  3. Convert both CustomerID columns to lists before merging so the connector can generate an IN predicate.
  4. Duplicate both queries as Import tables and merge the loaded model tables during dataset refresh.
Explanation: Whenever you see a question about query folding in Power Query, the key concept to hold onto is this: folding works as long as Power Query can translate your transformation steps back into a native SQL statement. The moment you introduce a step that breaks that translation chain, the database hands off execution to the local Power Query engine instead. When both your Orders and Customers queries are fully foldable, Power Query's SQL Server connector can recognize the merge as a relational join and push the entire operation — including the CustomerSegment expansion — down to SQL Server as a single efficient query. This is exactly why B is correct: preserving foldability on both inputs gives the connector everything it needs to generate a native JOIN statement. A is the trap this question is specifically designed for. Table.Buffer forces Power Query to evaluate and cache a table in memory before proceeding. This breaks the folding chain on the buffered input because the connector can no longer trace it back to a SQL source — it's now an in-memory object. The join then executes locally, not on SQL Server, which is the opposite of what your colleague intends. C is a misconception about how folding works. Converting columns to lists doesn't help the connector generate better SQL; it actually fragments the data into a non-tabular structure that is harder to fold, not easier. D introduces unnecessary complexity and changes the architecture entirely. Merging loaded model tables happens in DAX or the data model layer, not during Power Query refresh, and doesn't address query folding at all. Your study tip: think of foldability as a fragile chain — any step that forces local evaluation (like buffering, converting to lists, or sorting) breaks every downstream step's ability to fold too.

Question 5

You need a query to return the 100 transactions with the highest Amount from a relational source. The current Applied Steps first use Keep Top Rows to retain 100 rows and then sort Amount in descending order. Both steps appear capable of folding.

Which modification is required to produce the intended result while still allowing a source-side implementation when supported?

  1. Buffer the table before Keep Top Rows and sort the buffered subset by Amount afterward.
  2. Keep the existing order because folding causes SQL Server to reorder top-row operations automatically.
  3. Apply Keep Bottom Rows for 100 records and then sort Amount in ascending order.
  4. Sort Amount in descending order before applying Keep Top Rows for 100 records. (correct answer)
Explanation: When working with Power Query's query folding, the order of Applied Steps matters enormously because Power Query translates your steps sequentially into a source query. Steps that depend on each other must appear in the logical order that produces the correct result — not just any order that looks capable of folding. Here's the core issue: if you apply Keep Top Rows (100) before sorting by Amount descending, you're grabbing an arbitrary 100 rows first, then sorting that random subset. The final result is sorted, but it almost certainly doesn't contain the 100 highest amounts. To get the top 100 by Amount, you must sort Amount descending first, establishing the order, and then apply Keep Top Rows to capture the first 100 rows of that ordered set. This is exactly what D prescribes — and because both steps support folding, the engine can push this entire operation to the source as something like SELECT TOP 100 ... ORDER BY Amount DESC. A is wrong because buffering breaks query folding entirely — Table.Buffer forces evaluation in memory, preventing source-side execution. B is a dangerous misconception: query folding does not automatically reorder your steps for correctness. Power Query translates steps in the order you define them, so a logically inverted sequence produces wrong results regardless of folding. C is wrong because Keep Bottom Rows on an unsorted table still captures an arbitrary set, and sorting ascending afterward again sorts the wrong 100 records. The study takeaway: always think of Applied Steps as a pipeline where sequence defines semantics. On Power BI exams, questions about folding often test whether you understand that folding preserves your step order — it doesn't fix logical mistakes for you.

Question 6

A DirectQuery table uses a SQL Server source. A newly added custom M step calls a text-processing function that the connector cannot translate. Power BI reports that the transformation is not supported in DirectQuery mode. The business requires DirectQuery to be retained.

Which solution best satisfies the requirement?

  1. Mark the custom step as Enable Load off while keeping the same step in the DirectQuery dependency chain.
  2. Add Table.Buffer before the custom step so the text function can run locally during each interaction.
  3. Disable query folding for the table and allow DirectQuery to retrieve the full source table on demand.
  4. Move the text-processing logic into a SQL view or source expression and connect DirectQuery to that result. (correct answer)
Explanation: When a DirectQuery model encounters a transformation that cannot be folded back to the source query, Power BI has no way to execute that logic at the database level — and since DirectQuery never imports data, there's no local dataset to process it against either. The core challenge here is keeping all transformation logic inside the source system so DirectQuery can query the final result directly. Moving the text-processing logic into a SQL view or computed column on the SQL Server side — answer D — solves this cleanly. The view encapsulates the transformation at the source, and Power BI's DirectQuery simply queries that view like any other table. The connector can fold all subsequent operations against it, and the business requirement to retain DirectQuery is fully satisfied. Answer A misunderstands how DirectQuery works. Disabling "Enable Load" affects which tables are exposed in the model, not how queries are executed. The unsupported step remains in the dependency chain and still breaks query folding. Answer B is the trickiest distractor: Table.Buffer forces evaluation in-memory during Power Query refresh — but DirectQuery tables don't go through a traditional refresh. Each report interaction fires a live query to the source, so buffering during an import-style refresh doesn't apply here. Answer C is perhaps the most dangerous misconception: disabling query folding doesn't make unsupported transformations work — it just shifts the failure mode, and retrieving an entire source table on every user interaction would be a catastrophic performance problem even if it were possible. Your study tip: on DirectQuery questions, always ask "where does the computation actually happen?" If the answer is "not at the source," that solution is incompatible with DirectQuery.

Question 7

A developer adds Table.Buffer immediately after a SQL table is selected. The remaining steps filter to one month, remove most columns, and group by CustomerID. The developer expects buffering to make the later steps execute on SQL Server as a single operation.

Which assessment of this design is most accurate?

  1. Buffering is beneficial because it sends all later M transformations to SQL Server in one batch.
  2. Buffering is required because grouping operations cannot fold unless the input table is first cached.
  3. Buffering may increase work because it materializes data and blocks downstream steps from folding through it. (correct answer)
  4. Buffering changes only preview caching, so it has no effect on refresh evaluation or query folding.
Explanation: Whenever you see a question about Table.Buffer in Power Query, the core concept to test is query folding — the engine's ability to translate M steps back into native SQL that runs on the source database. Query folding works by chaining transformations together so the connector can generate a single optimized SQL statement. Table.Buffer deliberately breaks this chain: it forces Power Query to immediately materialize the entire table into memory before any downstream steps run. This means that filtering, column removal, and grouping can no longer be "seen through" by the SQL connector — they must execute in-memory inside the M engine instead of on SQL Server. So rather than sending one efficient query, you end up pulling a full unfiltered table across the network and then processing it locally. That's why C is correct — buffering may actually increase work by materializing data early and blocking folding for all subsequent steps. A has the causality backwards. Buffering doesn't batch transformations to SQL Server — it does the opposite, severing the connection that would allow SQL Server to handle them. B is a misconception; grouping operations can fold natively (Power Query can translate Group By into SQL GROUP BY) as long as folding hasn't been interrupted upstream. D is partially true that buffering affects caching, but it's wrong to say it has no effect on refresh evaluation — breaking query folding has a direct and significant performance impact during full refresh. As a study rule: anything that materializes data mid-query (Table.Buffer, Table.ToList) is a folding fence — nothing downstream can fold back through it.

Question 8

A fact table contains several years of records in Azure SQL Database. You configure incremental refresh by creating RangeStart and RangeEnd parameters. Before filtering the timestamp column, the query converts that column to text and extracts the date portion. The policy validates, but refresh is expected to scan a large amount of data.

Which revision best supports effective incremental refresh and query folding?

  1. Filter the original timestamp column using RangeStart and RangeEnd before converting or extracting values. (correct answer)
  2. Convert RangeStart and RangeEnd to text and compare them with the extracted date strings.
  3. Keep the existing order and add Table.Buffer immediately before the incremental-refresh filters.
  4. Apply the RangeStart and RangeEnd filters only after grouping the rows by extracted date.
Explanation: Whenever you see a question about incremental refresh in Power BI, the critical concept to keep in mind is query folding — the ability for Power Query to translate your transformation steps into native SQL that runs on the source database. If folding breaks, the database sends all rows to Power BI before any filtering occurs, which destroys the performance benefit incremental refresh is designed to deliver. The reason A is correct is that filtering the original timestamp column directly using RangeStart and RangeEnd allows Power Query to fold that filter into the SQL query as a simple WHERE clause against an indexed column. The database handles the row elimination before any data travels across the network, meaning only the relevant partition's rows are loaded — exactly what incremental refresh promises. B inverts the logic in a harmful way: converting RangeStart and RangeEnd to text and comparing them against extracted date strings forces Power Query to evaluate string comparisons on the M engine side after pulling all rows, breaking query folding entirely and still scanning the full table. C misunderstands Table.Buffer, which forces evaluation of the table in memory and explicitly breaks query folding on any subsequent steps. Adding it before the incremental-refresh filters would guarantee a full-table scan — the opposite of the goal. D applies filters after a GROUP BY-style aggregation, which means all rows must be read and grouped first. Folding is either broken or the partition filtering is applied too late to reduce I/O meaningfully. Your study tip: always ask yourself, "Does this transformation preserve query folding?" Any step that converts, buffers, or reorders data before the RangeStart/RangeEnd filter is a red flag on incremental refresh questions.

Question 9

A query has these Applied Steps: Source, Promoted Headers, Changed Type, Renamed Columns, and Calculated Revenue. The formula for Calculated Revenue references the renamed columns UnitPrice and UnitsSold. You delete Renamed Columns because you no longer want those display names.

What should you expect, and how should you correct the query?

  1. Calculated Revenue will likely fail; update that step to reference the column names produced by Changed Type. (correct answer)
  2. Power Query will retain hidden aliases; remove the aliases from the model after applying the query.
  3. Changed Type will automatically adopt the renamed values; refresh the preview to synchronize the metadata.
  4. The source will rename its columns automatically; recreate only the Promoted Headers step afterward.
Explanation: When working with Power Query's Applied Steps, think of each step as a link in a chain — later steps depend on the outputs of earlier ones. If you remove a middle step, any downstream step that references column names introduced by that step will break. Here, Renamed Columns is what introduced the names UnitPrice and UnitsSold. The Calculated Revenue step was built referencing those exact names. When you delete Renamed Columns, those friendly names no longer exist in the pipeline — the columns revert to whatever names Changed Type was producing. Power Query won't silently patch the broken reference; instead, Calculated Revenue throws an error because it's asking for columns that no longer exist by those names. The fix is exactly what A describes: open Calculated Revenue and update its formula to reference the column names that Changed Type actually outputs. B is wrong because Power Query doesn't maintain hidden aliases. There's no background aliasing system that preserves names after a step is deleted — what you see in the step output is what downstream steps receive. C is wrong because Changed Type doesn't "adopt" renamed values retroactively. Changed Type only modifies data types; it has no mechanism to inherit or absorb column renaming from a later step that you've deleted. D is wrong because the data source doesn't auto-rename columns based on changes inside Power Query. The source outputs whatever it outputs; Promoted Headers reads those, but nothing causes the source layer to self-correct. As a study tip, always trace column name lineage when editing Applied Steps — any step downstream that references a column by name is a potential breakpoint whenever you modify or delete an upstream step.

Question 10

A SQL source has a Quantity column stored as text. Most values are numeric, but some archived rows contain the value N/A. The query currently changes Quantity to a whole number and then filters for OrderYear equal to the current year. All current-year Quantity values are numeric. The conversion step produces errors before the filter is evaluated.

Which change both preserves the intended current-year result and gives the source the best opportunity to reduce rows before local processing?

  1. Add Table.Buffer before the type conversion so the archived rows are excluded during source execution.
  2. Replace all conversion errors with zero before filtering, because error replacement always folds to SQL Server.
  3. Move the current-year filter before the type conversion, provided the filter does not depend on the converted Quantity column. (correct answer)
  4. Remove the current-year filter and use Remove Errors after conversion to retain only valid Quantity values.
Explanation: When working with Power Query transformations, query folding is the critical concept at play. Query folding is Power Query's ability to translate transformation steps back into a native source query (like SQL), letting the database engine filter and reduce rows before data travels to your machine. Your goal is to keep foldable steps — especially filters — as early in the query as possible. Here's the core problem: type conversion on a mixed-text column (numeric values plus N/A strings) generates errors immediately when Power Query encounters non-numeric values. Since the current-year filter comes after this step, Power Query must process all rows — including archived N/A rows — before discarding irrelevant years. Moving the OrderYear filter before the type conversion solves both problems at once. The filter can fold to SQL (letting the database return only current-year rows), and since all current-year Quantity values are numeric, the conversion then runs cleanly on a smaller, error-free dataset. That's why C is correct. A is wrong because Table.Buffer forces data into memory immediately — it actually breaks query folding rather than enabling it, doing the opposite of what the question asks. B is wrong because error-replacement functions like Table.ReplaceErrorValues do not fold to SQL Server. This answer contains a false factual claim, which is a deliberate trap. D is wrong because removing the current-year filter changes the intended result — you'd return records from all years, not just the current one. As a study habit, remember: filters fold, conversions often don't. Always push row-reducing filters as early in your query steps as possible to maximize folding opportunities.