- Where should you make the transformation so that it is applied consistently to every file?
Answer: Add the removal step to the sample-file transformation that defines the automatically generated transformation function.
When Power Query's Combine Files feature processes a folder, it doesn't transform each file directly — it builds a reusable architecture. It creates a sample file query, a transformation function derived from that sample, and then invokes that function against every file in the folder. Understanding this pipeline is the key to answering this question correctly.
Because the transformation function is built from the sample-file query, any step you add to that sample-file transformation is automatically reflected in the function — and therefore applied to every file before the results are appended. That's exactly why B is correct. Editing the sample transformation is the single point of control that propagates your change universally and consistently.
A is tempting but wrong. Adding the removal step only to the final combined query means you're operating on already-appended data, which works in this narrow case but breaks the intended pattern. More importantly, it doesn't scale well and bypasses the function architecture entirely — if the structure changes, you lose the centralized control the helper queries provide.
C misunderstands what the folder source query does. It retrieves a list of file binaries (the Content column represents raw file bytes), not parsed column data. You can't filter CSV columns at the binary level before parsing.
D confuses the role of the helper parameter. The parameter identifies the sample file to use for building the transformation — it doesn't accept column names or act as a filter for unwanted fields.
As a study tip, remember: in a Combine Files workflow, the sample-file transformation is your universal edit point — think of it as the template every file inherits.
- Which function design best meets the requirement?
Answer: Declare the delimiter as an optional nullable text argument and substitute a comma when the argument value is null.
When designing reusable Power Query functions, the key question is: how do you balance flexibility for new callers with backward compatibility for existing ones? This is exactly what optional parameters solve in M (Power Query's formula language).
In M, you can declare a function argument as optional with a type like nullable text. Inside the function body, you check whether the argument is null and substitute a sensible default — in this case, a comma. This means existing callers that pass only the binary input continue to work without modification, while new callers can supply a custom delimiter when needed. That's precisely what A describes, making it the correct design pattern.
B fails because making the delimiter a required argument breaks all existing calls that don't supply one — the opposite of the stated requirement. Forcing a Power Query parameter also adds unnecessary configuration overhead for every caller.
C sounds clever but is brittle in practice. Auto-detecting delimiters from the first line is unreliable (what if the first line is a header with no consistent delimiter signal?), and it removes explicit control from the caller entirely. The requirement asks for callers to specify a different delimiter, not for the function to guess.
D creates maintenance duplication. Managing two separate functions that do essentially the same job means any future changes must be applied twice, and renaming queries to switch logic is fragile and unconventional — not a scalable design.
Your study tip: whenever a Power Query question mentions "existing calls must still work," immediately think optional parameters with null substitution — that's the M-language idiom for backward-compatible function design.
- Which expression should you use?
Answer: Table.Combine(List.Transform(Years, each fnGetYear(_))) because the list is mapped to tables before they are combined.
When you need to apply a function to every item in a list and collect the results, Power Query gives you two essential tools: List.Transform to map each item through a function, and Table.Combine to stack the resulting tables into one. Keeping those roles distinct is the key to answering this question correctly.
List.Transform(Years, each fnGetYear(_)) iterates over each year value (2023, 2024, 2025), calls fnGetYear once per value, and returns a list of tables. Table.Combine then accepts that list of tables and stacks them vertically into a single unified table. That's exactly what option D does — and why it's correct.
Option A is tempting but wrong: Table.Combine does not automatically invoke a function. It expects a list of already-materialized tables, not a function reference. Passing fnGetYear (a function) and Years (a list) directly into Table.Combine like this would cause a type error.
Option B misuses List.Combine, which merges lists together — not tables. Even if each fnGetYear call returns a table, wrapping those tables in List.Combine produces a flat list, not a combined table. You'd lose the table structure entirely.
Option C misunderstands how Table.FromList works. Converting the list to a table doesn't automatically call fnGetYear once per row — you'd still need an explicit transformation step to invoke the function against each value.
Study tip: Memorize this two-step pattern cold — List.Transform to apply, Table.Combine to stack. It appears frequently in Power Query scenarios involving dynamic data retrieval.
- Which design is most likely to preserve source-side filtering while still making the date reusable?
Answer: Reference StartDate directly in an early Table.SelectRows step and verify that the step continues to fold to SQL.
When working with large data sources in Power BI, query folding is the mechanism that pushes transformation steps back to the source database as native SQL. This means filtering happens server-side before rows ever travel across the network — critical when minimizing data transfer is a priority.
The key to preserving query folding is keeping your transformation steps in a form the Power Query engine can translate into SQL. Option B does exactly this: referencing StartDate directly inside Table.SelectRows at an early pipeline stage gives Power Query the best chance to fold that filter into a WHERE clause. You can confirm folding is active by right-clicking the step and checking whether "View Native Query" is available. This approach also keeps StartDate reusable as a parameter across multiple queries or refreshes.
Option A breaks folding entirely. Custom functions invoked row-by-row force Power Query to evaluate each row locally after a full table download — the opposite of what you want. Option C converts the date to text and filters a derived column, which introduces unnecessary complexity and almost certainly breaks the fold chain, since string manipulation on a non-source column can't be translated back to SQL. Option D moves filtering to DAX inside the report layer, which means the entire table has already been imported into the model — no rows were ever filtered at the source, defeating the purpose entirely.
Your study tip: whenever a Power BI question mentions "minimizing transferred rows" alongside a query folding-capable source, think early Table.SelectRows with a parameter. Late or model-layer filtering never reduces what's imported.
- What is the best correction?
Answer: Change the function to accept a binary, parse it with the appropriate document connector, and then apply table transformations.
When working with folder queries in Power BI, you need to think carefully about data types at each stage of the pipeline. A folder query exposes file contents as binary values in the [Content] column — raw bytes, not tables. The critical insight here is that a function expecting a table input will always fail when handed a binary value, no matter how you adjust surrounding logic.
The correct fix, D, addresses the root cause: the function itself must be redesigned to accept a binary, use the appropriate connector (like Excel.Workbook, Csv.Document, or Pdf.Tables) to parse it into a table, and then apply Table.TransformColumnTypes. This creates a complete, self-contained transformation that correctly handles what the folder query actually provides.
Option A is tempting but misguided. Changing the parameter type to any suppresses the type error at the signature level, but Table.TransformColumnTypes still receives a binary value internally — you've just moved the crash one step later. Option B attempts to wrap the binary in a list and convert it to a table row, but Table.FromList({[Content]}) produces a table containing the binary as a cell value, not a parsed, structured table. The type mismatch persists in a different form. Option C confuses Binary.Buffer with parsing — buffering loads binary data into memory for performance reasons, but it does absolutely nothing to convert bytes into a structured table.
The key study tip: in Power BI, parsing and transformation are always separate steps. Connectors parse; transformation functions like Table.TransformColumnTypes operate on already-structured data. Never assume a function designed for tables can accept raw binary input.
- What should the administrator do?
Answer: Configure credentials and an appropriate gateway data-source mapping for the production server, then run the refresh again.
Whenever you see a Power BI question involving changing a data source — especially from one server to another — think about the two separate layers the service requires to reach on-premises data: gateway mappings and credentials. Changing a parameter value in the service doesn't automatically carry those trust relationships over to the new destination.
When the parameter is updated to point to the production server, Power BI treats that new server as an entirely new data source. The service has no stored credentials for it, and no gateway data-source entry that maps to it. The correct fix, answer C, is to open the dataset settings, add credentials for the production server, configure a gateway data-source mapping that resolves to it, and then retry the refresh. This is the standard workflow any time a new on-premises endpoint is introduced.
A is a red herring — DAX measures evaluate data after it's loaded; they have no influence over Power Query connection parameters or how the service authenticates to a source. B misrepresents how credentials work. Converting a query into a function doesn't magically inherit credentials for arbitrary servers; each distinct server still needs its own registered credentials and gateway entry. D describes a behavior that doesn't exist in Power BI. Whether a parameter query has "Enable Load" turned on or off has no effect on the service's ability to recognize a changed server name; credential and gateway configuration is always required regardless.
As a study tip: on Power BI exam questions about refresh failures after a source change, always ask yourself "are credentials and a gateway configured for the new endpoint?" — that covers the majority of these scenarios.
- What should you do in the folder query?
Answer: Add a custom column using fnTransform([Content], [Region]), expand the returned tables, and remove unneeded metadata columns.
When working with folder queries in Power Query, the core challenge is applying a transformation to each row individually while preserving row-level context — in this case, the Region value paired with its corresponding Content binary. This is a classic row-by-row function application pattern.
The right approach is C: add a custom column using fnTransform([Content], [Region]). Since fnTransform is designed to accept one binary and one region value at a time, you invoke it once per row by referencing the column names directly inside the custom column formula. This passes each row's Content and Region together, and the function returns a standardized table for that file. You then expand those nested tables into a flat result and remove metadata columns like Name, Date modified, etc., that the folder connector automatically includes.
A is wrong because fnTransform takes scalar inputs (one binary, one text value), not an entire table. Passing the whole folder table isn't how the function is designed, and Power Query doesn't auto-iterate a multi-input function across a table like that.
B is wrong because converting Region to a single text parameter loses the per-row region context entirely. You'd also only call fnTransform once, meaning all files would be processed with one region value — discarding the row-level relationship.
D is wrong because expanding a binary column doesn't make sense — binaries must be transformed, not expanded. Grouping by Region before calling fnTransform also breaks the per-file granularity the function expects.
Study tip: Whenever a custom function needs per-row inputs from multiple columns, your go-to move is always a custom column with fnFunctionName([Col1], [Col2]) — let Power Query handle the row-by-row iteration automatically.
- How should you invoke the function?
Answer: Use try fnTransform([Content]), then expand the resulting record to inspect HasError, Value, and Error information.
When working with custom functions applied to multiple files in Power Query, your goal is often to catch transformation errors per row without losing any rows from your dataset. This is exactly where the try expression becomes essential.
The try keyword in Power Query M wraps an expression and returns a record with three fields: HasError (a boolean), Value (the successful result), and Error (a record describing what went wrong). When you write try fnTransform([Content]) for each row and then expand that resulting record column, you get full visibility into both successes and failures — all 200 rows remain intact. This makes C the correct approach: you retain every file's row, and you can filter or inspect based on HasError to pinpoint exactly which files failed and why.
Option A fails because Remove Errors permanently discards error rows, so you lose the malformed files entirely rather than identifying them. You'd only know how many failed, not which ones or what went wrong.
Option B uses try ... otherwise null, which collapses the rich error record into a plain null. You lose all error detail — you can only infer that something failed, not diagnose why. This violates the requirement to capture error details.
Option D wraps the entire folder query in a single try, meaning one error could suppress the whole table rather than isolating individual file failures. It provides no per-row granularity whatsoever.
As a study tip: whenever a Power BI question asks you to retain rows AND capture error details, think try without otherwise — expand the record to get HasError, Value, and Error fields per row.
- Which design provides the most maintainable way to switch environments?
Answer: Create a text Power Query parameter for the server name and reference it in the Sql.Database source expression.
When a Power BI solution spans multiple environments, the cleanest approach is to isolate environment-specific values so that your transformation logic stays untouched. The tool designed exactly for this is a Power Query parameter — a named, typed placeholder you define once and reference throughout your queries.
Option B is the right choice because a text Power Query parameter lets you define the server name in one place and pass it directly into the Sql.Database() function call. When you need to switch environments, you update a single parameter value — your transformation steps remain completely unchanged. This separation of configuration from logic is the foundation of maintainable deployments, and it integrates natively with Power BI's deployment pipelines, which support parameter overrides per environment.
Option A misuses a What-if parameter, which is a DAX-based tool designed to drive slicer-driven scenario analysis in reports. It has no role in Power Query source configuration and cannot be referenced inside Sql.Database().
Option C is an anti-pattern. Duplicating queries for each server multiplies your maintenance burden — any transformation change must be replicated across all three copies, which defeats the purpose of keeping transformations identical and introduces human error risk.
Option D is fundamentally mismatched. DAX calculated columns exist in the semantic model layer and run after data is already loaded; they cannot influence how the data connection is established at query time.
Study tip: On Power BI exam questions involving environment management or source switching, Power Query parameters are almost always the right tool — remember they live in the M layer, not the DAX layer.
- Which M definition correctly implements the function?
Answer: (SourceTable as table, CutoffDate as date) as table => let Filtered = Table.SelectRows(SourceTable, each [OrderDate] >= CutoffDate) in Filtered
When converting a query into a reusable M function, you need to recognize two things: the correct function signature syntax and how parameters flow into the function body. In Power Query M, a function is defined using parentheses for parameters, optional type annotations, an optional return type, followed by =>, and then the expression body.
Answer A is correct because it follows this exact pattern precisely. The parameters SourceTable and CutoffDate are declared with their types inside parentheses, as table declares the return type, and the => introduces the body. Inside, a let...in block filters using the passed-in parameters — making it fully reusable and self-contained.
Answer B wraps everything inside a let expression without a function signature at all. There are no parentheses or =>, so this defines a regular query, not a function. The type annotations inside let are also syntactically invalid in M.
Answer C uses the correct outer signature structure, but inside the body it references FilteredOrders (the original hard-coded query) instead of SourceTable, and tries to access FilteredOrders[CutoffDate] as if it were a column — completely defeating the purpose of parameterization.
Answer D uses function(...) which is not valid M syntax at all. M does not use the keyword function to declare functions, and the = assignment operator is not how function bodies are introduced — that role belongs to =>.
A helpful pattern to memorize: every M function starts with (params) =>. If you see anything else opening the definition — a let, the word function, or no arrow — it's wrong.
