Microsoft Power BI Quiz: Parameters And Functions In Power Query
10 questions · exam conditions
0:00
Parameters And Functions In Power QueryQuestion 1 of 10

An existing query named FilteredOrders filters a table by a hard-coded date. You want to convert its logic into a reusable function that accepts both a source table and a cutoff date.

Which M definition correctly implements the function?

(SourceTable as table, CutoffDate as date) as table => let Filtered = Table.SelectRows(SourceTable, each [OrderDate] >= CutoffDate) in Filtered
let SourceTable as table, CutoffDate as date, Filtered = Table.SelectRows(SourceTable, each [OrderDate] >= CutoffDate) in Filtered
(SourceTable as table, CutoffDate as date) => Table.SelectRows(FilteredOrders, each [OrderDate] >= FilteredOrders[CutoffDate])
function(SourceTable, CutoffDate) as table = Table.SelectRows(SourceTable, each [OrderDate] >= CutoffDate)
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Parameters And Functions In Power Query

Practice Parameters And Functions In Power Query 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 Parameters And Functions In Power Query, 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

An existing query named FilteredOrders filters a table by a hard-coded date. You want to convert its logic into a reusable function that accepts both a source table and a cutoff date.

Which M definition correctly implements the function?

  1. (SourceTable as table, CutoffDate as date) as table => let Filtered = Table.SelectRows(SourceTable, each [OrderDate] >= CutoffDate) in Filtered (correct answer)
  2. let SourceTable as table, CutoffDate as date, Filtered = Table.SelectRows(SourceTable, each [OrderDate] >= CutoffDate) in Filtered
  3. (SourceTable as table, CutoffDate as date) => Table.SelectRows(FilteredOrders, each [OrderDate] >= FilteredOrders[CutoffDate])
  4. function(SourceTable, CutoffDate) as table = Table.SelectRows(SourceTable, each [OrderDate] >= CutoffDate)
Explanation: 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.

Question 2

A list query named Years contains the values 2023, 2024, and 2025. A function named fnGetYear accepts one year as a number and returns a table of transactions for that year. You need one combined transaction table.

Which expression should you use?

  1. Table.Combine(fnGetYear(Years)) because Table.Combine automatically invokes the function once for every list item.
  2. List.Combine(List.Transform(Years, each fnGetYear(_))) because each returned table must first be combined as a list.
  3. fnGetYear(Table.FromList(Years)) because converting the list to a table creates one function call per row.
  4. Table.Combine(List.Transform(Years, each fnGetYear(_))) because the list is mapped to tables before they are combined. (correct answer)
Explanation: 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.

Question 3

You use Combine Files on a folder containing monthly CSV files with identical structures. Power Query creates helper queries, including a sample-file transformation and a function. Later, a new column must be removed from every imported file before the results are appended.

Where should you make the transformation so that it is applied consistently to every file?

  1. Add the removal step only to the final combined query after expanding the results from all monthly files.
  2. Add the removal step to the sample-file transformation that defines the automatically generated transformation function. (correct answer)
  3. Edit the folder source query so that the binary Content value no longer includes the unwanted CSV column.
  4. Change the helper parameter to contain the unwanted column name before invoking the generated function.
Explanation: 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.

Question 4

A reusable text-import function normally uses a comma delimiter, but some callers must specify a different delimiter. Existing calls that supply only the input binary must continue to work.

Which function design best meets the requirement?

  1. Declare the delimiter as an optional nullable text argument and substitute a comma when the argument value is null. (correct answer)
  2. Declare the delimiter as a required text argument and create a Power Query parameter that callers must always supply.
  3. Remove the delimiter argument and detect every possible delimiter solely from the first line of each imported file.
  4. Create separate comma and non-comma functions, then select the required function by renaming the query before refresh.
Explanation: 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.

Question 5

A folder contains 200 files. A custom function successfully transforms most files, but several malformed files cause errors. You must retain all file rows and identify which files failed, including the error details.

How should you invoke the function?

  1. Invoke the function normally, use Remove Errors on the result column, and compare the remaining row count with the folder count.
  2. Use try fnTransform([Content]) otherwise null, then filter null values and infer that the omitted files were malformed.
  3. Use try fnTransform([Content]), then expand the resulting record to inspect HasError, Value, and Error information. (correct answer)
  4. Place one try expression around the entire folder query so that any function error returns the untransformed folder table.
Explanation: 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.

Question 6

A function is declared as (Input as table) as table and immediately uses Table.TransformColumnTypes. A folder query invokes the function by passing [Content], which contains each file as a binary value. Every invocation returns a type error.

What is the best correction?

  1. Change the argument type from table to any while leaving the existing table transformation as the first function step.
  2. Convert each binary to a one-row table by using Table.FromList({[Content]}) before invoking the existing function.
  3. Apply Binary.Buffer to [Content] before invocation so that the function receives a materialized table value.
  4. Change the function to accept a binary, parse it with the appropriate document connector, and then apply table transformations. (correct answer)
Explanation: 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.

Question 7

A semantic model uses a Power Query parameter as the server argument to Sql.Database. After publication, an administrator changes the parameter from an on-premises test server to an on-premises production server. The next refresh reports that the data source cannot be accessed.

What should the administrator do?

  1. Create a DAX measure that returns the production server name so the semantic model can override the Power Query parameter.
  2. Convert the source query into a function because functions automatically inherit credentials for every server supplied as an argument.
  3. Configure credentials and an appropriate gateway data-source mapping for the production server, then run the refresh again. (correct answer)
  4. Disable load for the parameter query because loaded parameter values prevent the service from recognizing a changed server.
Explanation: 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.

Question 8

A query reads a large SQL table. A date parameter named StartDate determines the earliest rows required. The data source supports query folding, and minimizing transferred rows is a priority.

Which design is most likely to preserve source-side filtering while still making the date reusable?

  1. Load the complete SQL table, invoke a custom function once per row to compare the date, and remove rows returning false.
  2. Reference StartDate directly in an early Table.SelectRows step and verify that the step continues to fold to SQL. (correct answer)
  3. Convert StartDate to text, append it to every imported row, and filter the resulting text column after all transformations.
  4. Load the complete SQL table into the model, create a DAX filter using StartDate, and exclude earlier rows from report visuals.
Explanation: 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.

Question 9

A Power BI Desktop file is deployed to development, test, and production environments. Each environment uses the same SQL database schema but a different server name. The query transformations must remain identical across environments.

Which design provides the most maintainable way to switch environments?

  1. Create a model What-if parameter for the server name and use its selected value in the SQL source step.
  2. Create a text Power Query parameter for the server name and reference it in the Sql.Database source expression. (correct answer)
  3. Duplicate the query for each server and disable load for the two queries that are not currently required.
  4. Create a DAX calculated column containing the server name and reference that column from the SQL connection.
Explanation: 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.

Question 10

A folder query returns one row per file. Each row contains a Content binary column and a Region text column. A function named fnTransform accepts a binary value and a region value, then returns a standardized table. The results from all files must be combined.

What should you do in the folder query?

  1. Pass the entire folder table and the Region column to one call of fnTransform, then expand the returned table.
  2. Convert Region to a single text parameter, call fnTransform once, and merge the result with the folder query.
  3. Add a custom column using fnTransform([Content], [Region]), expand the returned tables, and remove unneeded metadata columns. (correct answer)
  4. Expand the Content binary column first, group the rows by Region, and call fnTransform once for each group.
Explanation: 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.