Microsoft Power BI Quiz: Handling Missing Values
15 questions · exam conditions
0:00
Handling Missing ValuesQuestion 1 of 15

A numeric column has nulls only; no errors. Change nulls to 0. What should Replace Values find?

Find empty, replace with 0
Find null, replace with 0
Find error, replace with 0
Use Remove Errors first
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Handling Missing Values

Practice Handling Missing Values 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 Handling Missing Values, 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 numeric column has nulls only; no errors. Change nulls to 0. What should Replace Values find?

  1. Find empty, replace with 0
  2. Find null, replace with 0 (correct answer)
  3. Find error, replace with 0
  4. Use Remove Errors first
Explanation: A null is a missing value, not an empty string or an error, so Replace Values must target null exactly. The tempting wrong choice is Find empty, because an empty value is a zero-length text string, not the same as a null. You want Find null, replace with 0.

Question 2

A column has 3 nulls and 1 error. Use Replace Errors, value 0. Result?

  1. Nulls stay; error becomes 0 (correct answer)
  2. Nulls become 0; error stays
  3. Nulls and error both become 0
  4. Error row removed; nulls stay
Explanation: Replace Errors only targets error values, not nulls. The nulls are left untouched, and the single error is replaced with 0. The tempting mistake is assuming Replace Errors also turns nulls into 0, but nulls and errors are distinct in Power Query.

Question 3

Custom column: try [Qty]/[Units] otherwise 0; Units is null. Output?

  1. Value: empty
  2. Value: 0
  3. Value: error
  4. Value: null (correct answer)
Explanation: In M, arithmetic with null returns null, not an error. Here [Qty]/[Units] is [Qty]/null, so the result is null. The try otherwise 0 clause only replaces errors, so it doesn't apply. The tempting wrong choice is 0, but otherwise isn't triggered because a null division doesn't raise an error.

Question 4

Table has all-null rows and one error cell. Apply Remove Errors. Result?

  1. Error row and null rows drop
  2. Null rows removed; error stays
  3. Error row removed; nulls stay (correct answer)
  4. No error or null rows drop
Explanation: Remove Errors filters out rows that contain an error value; it does not treat null as an error. So the row with the error cell is dropped, and all-null rows remain because their cells are null, not errors. The tempting mistake is thinking nulls count as errors and get removed, but Power Query distinguishes null from error.

Question 5

Column: null, 1, null, 2, null. Apply Fill Up. Result?

  1. Final: 1, 1, 2, 2, null (correct answer)
  2. Final: null, 1, 1, 2, 2
  3. Final: 1, 1, 2, 2, 2
  4. Final: null, 1, null, 2, null
Explanation: Fill Up takes the next non-null value and moves it upward into nulls. Starting from the bottom, the last null has no value below it, so it stays null. The 2 fills the null above it, then the 1 fills the first null, giving 1, 1, 2, 2, null. The common mistake is filling downward instead, which leaves the first null and produces null, 1, 1, 2, 2.

Question 6

A Power Query column named CustomerCode contains valid codes, null values, empty strings, and strings containing only spaces. The data model must represent every missing code as null without changing valid codes.

Which sequence of transformations should you apply?

  1. Trim CustomerCode, and then replace empty strings with null. (correct answer)
  2. Replace null with an empty string, and then trim CustomerCode.
  3. Remove blank rows, and then replace errors with null.
  4. Fill down CustomerCode, and then trim the resulting values.
Explanation: When cleaning text data in Power Query, order of operations matters enormously. The challenge here is that "missing" data appears in three forms: null values, empty strings (""), and whitespace-only strings (" "). Your goal is to collapse all three into nulls while leaving valid codes untouched. Answer A is the correct sequence. First, trimming CustomerCode removes leading and trailing spaces, which converts whitespace-only strings into empty strings (""). Now all non-null, non-valid values are empty strings. Second, replacing empty strings with null unifies everything into a single null representation. Two targeted steps, clean result. Answer B reverses this logic fatally. Replacing null with empty strings first gives you more empty strings to deal with, and then trimming those still leaves you with empty strings — you've made the problem worse, not better. You'd still need an additional step to clean up, and you've lost your nulls entirely. Answer C misunderstands the tools available. "Remove blank rows" deletes entire rows from the dataset rather than standardizing values within a column — that changes the row count, which is not what the requirement asks for. Replacing errors with null addresses a completely different data quality issue (transformation errors, not blank values). Answer D uses Fill Down, which propagates the last valid value downward to fill nulls. This would overwrite missing codes with real codes from other rows — the opposite of preserving nulls. Trimming afterward doesn't undo that data corruption. A useful mental model: always normalize the format first (Trim), then standardize the representation (replace empties with null). Think of it as cleaning before categorizing.

Question 7

A query contains OrderID, Product, and Revenue columns. Five Revenue cells contain errors caused by invalid source values. The corresponding orders must remain in the query because Product-level counts use every order. For those five cells, Revenue should be missing.

What should you do in Power Query?

  1. Select Revenue and use Remove Errors to delete the affected records.
  2. Select Revenue and use Replace Errors to replace each error with null. (correct answer)
  3. Select Revenue and replace the text value "Error" with null.
  4. Select Revenue and use Remove Empty to exclude missing values.
Explanation: When working with data quality in Power Query, it's important to distinguish between removing a row and replacing a bad value. Here, the business rule is clear: keep every order (for count purposes), but treat the five invalid Revenue amounts as unknown rather than calculated. The right move is B — select the Revenue column and use Replace Errors, setting the replacement value to null. This converts each error cell into a proper missing value while leaving the row intact. The order still participates in Product-level counts, and downstream calculations can handle nulls gracefully (aggregate functions like SUM skip them by default). A is wrong because Remove Errors deletes the entire row, not just the bad cell. You'd lose those five orders from the dataset entirely, which breaks the Product-level count requirement the question explicitly states. C sounds plausible but reflects a fundamental misunderstanding: errors in Power Query are not stored as the text string "Error" — they are a special error state. A find-and-replace on text will not touch actual error values, so this approach does nothing to the affected cells. D is a trap based on confusing two different operations. Remove Empty filters out rows where a column contains null or blank values — essentially the opposite of what you want. It would remove rows after you've already converted errors to nulls, compounding the problem. A useful rule of thumb: if you need to keep the row but clean the cell, always reach for Replace Errors, not Remove Errors or Remove Empty.

Question 8

A CSV file provides an Amount column containing numbers and the marker "N/A". An automatically generated Changed Type step converts Amount to a decimal number, causing every "N/A" value to become an error. You must convert only the known "N/A" marker to null and still expose any other invalid numeric text as an error.

How should you modify the query?

  1. Replace all errors with null after Changed Type and retain the existing order.
  2. Replace "N/A" with null before Changed Type and then apply the decimal type. (correct answer)
  3. Remove errors after Changed Type and then replace "N/A" with null.
  4. Change Amount to text after Changed Type and replace every error with "N/A".
Explanation: When working with Power Query's type conversion steps, the order of operations is everything. The automatic Changed Type step tries to parse every value in Amount as a decimal — and since "N/A" isn't a number, it becomes an error. Your challenge is to be surgical: eliminate the known bad marker without silently swallowing legitimate errors caused by truly unexpected invalid data. The right move, as option B describes, is to replace "N/A" with null before Changed Type runs. In Power Query, null is type-agnostic — it passes cleanly through any type conversion. So once "N/A" becomes null, the decimal conversion step processes it harmlessly, while any other non-numeric text (say, "unknown" or "TBD") still throws an error, exposing problems you'd actually want to know about. B gives you precision: clean known markers, surface unknown ones. Option A fails because replacing all errors after the fact is a blunt instrument — it silently converts every error to null, including values that might signal real data quality issues you should investigate. Option C makes the same mistake: removing errors after Changed Type discards rows entirely, destroying data rather than handling it gracefully, and it still doesn't address replacing "N/A" with null in a meaningful way. Option D is counterproductive — reverting Amount back to text after a type conversion step undoes the goal entirely, and replacing errors with "N/A" recreates the original problem rather than solving it. A useful rule of thumb for the exam: clean known anomalies upstream of type conversion, and let type conversion act as your validator for everything else. This pattern of "replace before cast" appears frequently in Power Query transformation questions.

Question 9

A source repeats an invoice date only on the first line of each invoice. Each customer can have multiple invoices, and the query is sorted by CustomerID and source sequence. The first record for a customer can have a null InvoiceDate because that customer's opening invoice is incomplete. A simple Fill Down on the entire column would copy the previous customer's date into that record.

Which approach best fills dates without crossing customer boundaries?

  1. Fill down InvoiceDate globally, and then remove duplicate CustomerID values.
  2. Group by CustomerID, fill down InvoiceDate within each nested group, and recombine. (correct answer)
  3. Fill up InvoiceDate globally, and then sort the rows by CustomerID.
  4. Replace every null InvoiceDate with the minimum date for its customer.
Explanation: When working with hierarchical or grouped data in Power Query, you need to think carefully about whether a transformation should respect group boundaries. A global Fill Down simply propagates the last non-null value downward across the entire column — it has no awareness of when one customer ends and another begins. The right approach is B: group the table by CustomerID, apply Fill Down to the InvoiceDate column within each nested table, and then expand or recombine. Because Fill Down operates independently inside each customer's subset, a null at the top of one customer's records can never inherit a date from the previous customer. This is the standard Power Query pattern for any operation that must stay within logical partitions. Choice A fails because removing duplicate CustomerIDs after a global Fill Down doesn't fix the contamination — it just collapses rows. The first record for a customer may already hold the wrong date before any deduplication happens. Choice C uses Fill Up globally, which moves dates in the wrong direction and still crosses customer boundaries. Sorting afterward does nothing to correct dates that were already incorrectly assigned. Choice D — replacing nulls with each customer's minimum date — sounds data-aware, but it requires the correct dates to already exist in the dataset. If the opening invoice is incomplete and its date is null, there may be no valid minimum to reference, so this approach can silently produce wrong or null results. As a study tip: whenever you see a Fill Down scenario in Power BI questions, immediately ask "does this operation need to respect a group boundary?" If yes, the answer almost always involves grouping first, transforming inside the nested tables, then recombining.

Question 10

A numeric Discount column contains valid numbers, nulls, and error values. The business rule requires both nulls and errors to become zero while every valid number and every row must be retained.

Which transformation plan meets the requirement?

  1. Replace errors with zero, and separately replace null values with zero. (correct answer)
  2. Replace null values with zero, and then remove all remaining errors.
  3. Remove empty values, and then replace remaining errors with zero.
  4. Replace the text strings "null" and "Error" with numeric zero.
Explanation: When working with data quality transformations in Power Query, you need to think carefully about two distinct concepts: null values (empty/missing cells) and error values (cells that contain a transformation or calculation error). These are separate data states that require separate handling steps. The requirement here is clear: preserve every row, and convert both nulls and errors to zero. Answer A accomplishes exactly this — replacing errors with zero and replacing nulls with zero are independent operations in Power Query, and applying both ensures complete coverage without losing any rows. Order doesn't matter here since neither operation removes data. Answer B fails because it removes error rows entirely ("remove all remaining errors") rather than converting them to zero. This violates the requirement that every row must be retained — you'd silently drop data. Answer C has the same row-loss problem from a different angle. "Remove empty values" deletes rows where the Discount column is null, so you've already lost rows before you even address errors. Both conditions required zero replacement, not deletion. Answer D reflects a common misconception about how Power Query stores these states. Nulls and errors are not stored as text strings — they are internal data states. You cannot find the literal text "null" or "Error" in a numeric column using a simple text replace. This step would do nothing meaningful. Study tip: On Power BI exam questions, watch for the word "remove" — it almost always means rows are deleted, which conflicts with any requirement to retain all rows. When you must keep every row and fix bad values, always reach for replace, not remove.

Question 11

You create a UnitPrice custom column from Revenue divided by Units. Units can be zero or null, and either condition should produce a null UnitPrice. Errors already present in Revenue must remain visible so that source-quality problems are not concealed.

Which M expression should define UnitPrice?

  1. if [Revenue] = null then null else [Revenue] / [Units] — guards against a missing numerator before performing division
  2. try [Revenue] / [Units] otherwise null — handles division errors and missing denominators by returning null for any failure
  3. if [Units] = null or [Units] = 0 then null else [Revenue] / [Units] (correct answer)
  4. if [Units] = null then 0 else [Revenue] / [Units] — substitutes zero for a missing denominator to avoid null propagation
Explanation: When writing custom columns in Power Query M, you need to think carefully about which errors you want to suppress versus which ones you want to expose. This question tests your ability to handle denominator edge cases precisely — without accidentally hiding upstream data quality problems. The requirement has two distinct rules: (1) null or zero denominators should silently produce null, and (2) errors already living in [Revenue] must remain visible. Option C, if [Units] = null or [Units] = 0 then null else [Revenue] / [Units], satisfies both. It intercepts only the denominator problems you anticipate, then lets the division proceed normally — meaning any error in [Revenue] propagates through as an error, exactly as intended. Option A guards the wrong side of the equation. Checking [Revenue] = null doesn't protect against division by zero, so if [Units] is 0, you still get an error. It also wouldn't suppress a null [Units] reliably. Option B uses try...otherwise null, which sounds convenient, but it's a blunt instrument — it catches any failure, including errors already present in [Revenue]. That conceals source-quality problems the question explicitly says must remain visible. Option D substitutes zero for a null denominator, which makes the problem worse: dividing by zero raises an error rather than returning null. A useful pattern to remember: use explicit if guards when you want surgical error handling — protecting against known conditions without swallowing unexpected failures. Reserve try...otherwise for situations where suppressing all errors is genuinely acceptable.

Question 12

An API returns a Reading field as numeric text, null, or malformed text such as "offline". You need a custom column that converts numeric text to numbers, preserves null as null, and returns null instead of an error for malformed text.

Which Power Query M expression should you use?

  1. try Text.From([Reading]) otherwise null
  2. if [Reading] = null then 0 else Number.From([Reading])
  3. Number.From([Reading]) otherwise "offline"
  4. try Number.From([Reading]) otherwise null (correct answer)
Explanation: When working with data type conversions in Power Query M, the key pattern to recognize is: when a conversion might fail, you need both a safe conversion function and an error-handling wrapper. This question tests whether you know how to combine Number.From() with try...otherwise to handle unpredictable API data gracefully. The correct answer is D because try Number.From([Reading]) otherwise null does exactly what the requirements specify. Number.From() converts numeric text like "42" to a number, passes null through as null, and throws an error on malformed input like "offline". The try...otherwise null wrapper catches that error and returns null instead — covering all three cases cleanly. A is backwards in its logic. Text.From([Reading]) converts values to text, not numbers. You'd end up with text strings where you wanted numbers, which defeats the entire purpose of the transformation. B handles the null case incorrectly — it replaces null with 0 rather than preserving it as null. It also provides no error handling, so Number.From("offline") would still throw an unhandled error and break your query. C uses Number.From([Reading]) otherwise null, which looks similar to the correct answer but is missing the try keyword. In M, otherwise is only valid as part of a try...otherwise expression — writing Number.From() without try first is a syntax error. Study tip: Memorize the full try [expression] otherwise [fallback] pattern as a unit. Whenever you see a conversion that might fail on dirty data, reach for this construct — it's the M equivalent of a try/catch block.

Question 13

A SurveyScore column contains numeric responses and nulls for respondents who skipped the question. The same rows contain demographic fields needed for other analyses. A report must calculate the average among respondents who supplied a score, and skipped responses must not count as zero.

How should you prepare SurveyScore in Power Query?

  1. Fill down SurveyScore so every respondent receives a numeric response.
  2. Replace each null with zero and keep the respondent rows in the query.
  3. Retain the null values and keep the respondent rows in the query. (correct answer)
  4. Replace each null with the overall average during data preparation.
Explanation: When working with optional survey fields in Power Query, the core question is always: how do you distinguish "no response" from "a response of zero"? These are fundamentally different things, and your data preparation must preserve that distinction. Retaining null values — answer C — is the right approach here. Power BI's DAX functions like AVERAGE and AVERAGEX automatically ignore nulls in their calculations. This means if you simply leave the nulls in place, the average will be computed only over respondents who actually provided a score, with no extra transformation required. The respondent rows must also stay in the query because those rows contain the demographic fields needed for other analyses. Answer A, filling down, copies the nearest previous score into every blank cell. This is appropriate for ordered categorical fields like region or department where a repeated value makes sense — not for survey scores, where a null means "no answer was given." Filling down fabricates data. Answer B replaces nulls with zero, which is the most dangerous trap. A score of zero is a legitimate, meaningful response in many scales, and treating skipped questions as zero will artificially drag down the average, corrupting the metric entirely. Answer D replaces nulls with the overall average — a technique sometimes used for predictive modeling imputation — but it is circular and inappropriate here. You cannot compute a valid average by pre-filling unknowns with the average you are trying to measure. The takeaway: on Power BI questions involving optional fields, nulls are your friend. DAX aggregate functions skip them by design, so preserve nulls rather than replacing them.

Question 14

In Power Query Editor, column quality reports no empty values for AccountID. After the dataset is loaded, validation shows many null AccountID values near the end of a source containing 50,000 rows. No filtering or replacement step exists in the query.

What should you do first to verify the missing-value rate in Power Query Editor?

  1. Change AccountID to text so every null is included in the preview.
  2. Enable query folding and refresh only the first 1,000 source records.
  3. Replace AccountID errors with null and review the valid percentage again.
  4. Set column profiling to the entire data set and review column quality again. (correct answer)
Explanation: When troubleshooting data quality discrepancies in Power Query Editor, the first thing to understand is that column profiling defaults to only the first 1,000 rows. This means Quality, Distribution, and Profile statistics you see in the editor reflect only that small sample — not the full dataset. If your nulls appear near row 40,000–50,000, they're completely invisible in the default view, which is exactly the trap this question describes. The fix is straightforward: go to View → Column profiling, then change the setting from "Column profiling based on top 1000 rows" to "Column profiling based on entire data set." Once you refresh, the column quality bar will recalculate across all 50,000 rows and expose the true null rate. This confirms D is the correct first step — it diagnoses the actual scope of the problem before you take any corrective action. A is wrong because changing the data type to text doesn't affect whether nulls are counted in profiling; it would only change how values are represented, not surface hidden rows outside the 1,000-row preview window. B is a red herring. Query folding is about pushing transformation steps back to the data source for performance — it has nothing to do with expanding how many rows Power Query profiles for quality statistics. C puts the cart before the horse. Replacing errors with nulls is a remediation step, not a diagnostic one. You haven't yet confirmed where or how many values are missing, so taking action before diagnosing is premature. Study tip: Anytime a Power BI question involves column quality or distribution statistics that seem "off," immediately think about the 1,000-row profiling limit — it's one of the most commonly tested gotchas on this exam.

Question 15

A query has Quantity and Comment columns. Some rows contain errors in Quantity, while other rows contain errors only in Comment. You must remove rows with invalid quantities but retain rows whose only error is in Comment for later review.

Which action should you perform?

  1. Select Quantity and choose Remove Errors for the selected column. (correct answer)
  2. Select both columns and choose Remove Errors for the query.
  3. Select Comment and choose Keep Errors for the selected column.
  4. Select Quantity and choose Remove Empty for the selected column.
Explanation: When working with error handling in Power BI's Power Query Editor, the key distinction to understand is whether your remove/keep error operation targets a specific column or the entire row based on all selected columns. This distinction completely changes which rows get eliminated. In this scenario, you want to remove only rows where Quantity is invalid, while preserving rows that have errors solely in Comment. The correct approach is A: select the Quantity column and choose Remove Errors on that column. This tells Power Query to drop any row where an error exists specifically in the Quantity field — leaving intact any row where Quantity is valid, even if Comment contains an error. Option B is the critical trap here. Selecting both columns before choosing Remove Errors causes Power Query to remove any row that has an error in either column. That means rows with a bad Comment but a perfectly valid Quantity would be deleted — exactly the opposite of what you need. Option C sounds creative but doesn't solve the problem. Keeping rows where Comment has errors would actually give you only the problematic Comment rows, discarding all the clean data you want to keep. It isolates the wrong subset. Option D applies Remove Empty, which targets null/blank cells, not error values. An error and an empty cell are fundamentally different things in Power Query, so this action wouldn't remove rows with invalid quantities at all. Study tip: Always ask yourself whether your error-handling action is column-scoped or query-scoped. Selecting one column before Remove Errors = column-level filter; selecting multiple columns = any error in any of those columns removes the row.