- Which solution best satisfies the requirement?
Answer: Move the text-processing logic into a SQL view or source expression and connect DirectQuery to that result.
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.
- Which revision best supports effective incremental refresh and query folding?
Answer: Filter the original timestamp column using RangeStart and RangeEnd before converting or extracting values.
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.
- Which change is most likely to reduce the amount of data retrieved from SQL Server without changing the final result?
Answer: Move Filtered Rows before Added Custom, and verify that the filter folds at that earlier step.
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?"
- What should you do to maximize the chance that the join is executed by SQL Server?
Answer: Keep both inputs foldable and perform the merge without buffering either query before the join.
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.
- What should you expect, and how should you correct the query?
Answer: Calculated Revenue will likely fail; update that step to reference the column names produced by Changed Type.
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.
- Which change both preserves the intended current-year result and gives the source the best opportunity to reduce rows before local processing?
Answer: Move the current-year filter before the type conversion, provided the filter does not depend on the converted Quantity column.
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.
- What is the best method to determine where folding stops?
Answer: Select successive Applied Steps and inspect folding information to identify the first step that cannot be represented by the source query.
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.
- Which implementation should you use?
Answer: Use Value.NativeQuery against the database target and set EnableFolding to true in its options record.
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.
- Which modification is required to produce the intended result while still allowing a source-side implementation when supported?
Answer: Sort Amount in descending order before applying Keep Top Rows for 100 records.
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.
- Which assessment of this design is most accurate?
Answer: Buffering may increase work because it materializes data and blocks downstream steps from folding through it.
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.
