Microsoft Power BI Quiz: Basic Power Query Transformations
10 questions · exam conditions
0:00
Basic Power Query TransformationsQuestion 1 of 10

A text column named Comment contains meaningful text, empty strings, and null values. Both empty strings and nulls must be excluded, but rows containing spaces within otherwise meaningful comments must remain.

Which Power Query action best satisfies the requirement?

Open the Comment filter and clear both the empty-string entry and the null entry.
Use a Does Not Contain filter with a single space as the filter value.
Replace null values with empty strings, but do not apply a row filter afterward.
Remove the Comment column and rely on the remaining columns to eliminate blank rows.
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Basic Power Query Transformations

Practice Basic Power Query Transformations 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 Basic Power Query Transformations, 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 text column named Comment contains meaningful text, empty strings, and null values. Both empty strings and nulls must be excluded, but rows containing spaces within otherwise meaningful comments must remain.

Which Power Query action best satisfies the requirement?

  1. Open the Comment filter and clear both the empty-string entry and the null entry. (correct answer)
  2. Use a Does Not Contain filter with a single space as the filter value.
  3. Replace null values with empty strings, but do not apply a row filter afterward.
  4. Remove the Comment column and rely on the remaining columns to eliminate blank rows.
Explanation: When working with data quality tasks in Power Query, you need to think carefully about what exactly you're filtering and whether your method handles every case without causing collateral damage to valid data. The requirement here has two parts: remove nulls, remove empty strings, and — critically — leave rows with spaces inside meaningful comments untouched. The cleanest approach is to use the column's filter dropdown directly. When you open the filter on a text column, Power Query lists distinct values including a (null) entry and a blank/empty-string entry. Clearing both checkboxes filters out exactly those two categories — nothing more, nothing less. That's why A is correct: it's precise, surgical, and preserves any comment that contains actual text, even if spaces appear within it. B is a trap. A "Does Not Contain" filter with a single space would exclude any row whose comment contains a space anywhere — including valid comments like "Great product" or "Needs improvement." That destroys meaningful data. C doesn't satisfy the requirement at all. Replacing nulls with empty strings just converts one problem into another form; if you don't follow up with a row filter, both the converted empties and the originals remain in your dataset. D is a misdirection. Removing the Comment column entirely throws away the data you're supposed to be cleaning, and relying on other columns to eliminate blank rows is speculative and indirect — it won't reliably target the same rows. As a study tip, remember that Power Query's filter dropdowns enumerate actual values in the column, making them ideal for targeting specific categories like nulls and empty strings without writing custom logic.

Question 2

An OrderReference column contains values such as NW-2026-0041 and SE-2025-0198. You must create RegionCode containing NW or SE and OrderKey containing the entire remaining value, such as 2026-0041.

How should you configure Split Column by Delimiter?

  1. Use a fixed character position after four characters and create two columns.
  2. Use the hyphen delimiter, split at each occurrence, and create three columns.
  3. Use the hyphen delimiter, split at the right-most delimiter, and create two columns.
  4. Use the hyphen delimiter, split at the left-most delimiter, and create two columns. (correct answer)
Explanation: When splitting a delimited column in Power Query, the key decision is where to split and how many pieces you need. Here, NW-2026-0041 must become two parts: NW (before the first hyphen) and 2026-0041 (everything after it). That tells you immediately you need exactly one split point — the first hyphen. Option D is correct because splitting at the left-most delimiter cuts the string at its first hyphen, producing exactly two columns: NW on the left and 2026-0041 on the right. This preserves the remaining hyphens inside OrderKey intact, which is precisely what the requirement demands. Option A fails because a fixed-width split after four characters would work for NW-2 (splitting mid-year), not cleanly isolating the region code from the rest. Fixed-width is the wrong tool when a natural delimiter already exists. Option B is tempting but wrong. Splitting at each occurrence of the hyphen produces three columns — NW, 2026, and 0041 — shattering the OrderKey value you were supposed to keep whole. This is a classic trap: more splits than intended. Option C is also plausible but inverted. Splitting at the right-most delimiter would cut at the last hyphen, giving you NW-2026 and 0041 — the exact opposite of what's needed. Study tip: On Power BI exam questions about Split Column, always map out your desired output columns first, then count how many splits you need and which delimiter occurrence to use. "Left-most" = split once from the front; "right-most" = split once from the back.

Question 3

Each source row represents one product. A Tags column can contain values such as Outdoor;Clearance;Seasonal. The transformed query must contain one row per product-tag combination while repeating the product's other column values.

Which Split Column configuration should you use for Tags?

  1. Split by the semicolon delimiter and choose to split into rows. (correct answer)
  2. Split by the semicolon delimiter and choose to split into columns.
  3. Replace semicolons with spaces and retain one row per product.
  4. Filter Tags by semicolon and remove rows that contain multiple tags.
Explanation: When you need to "explode" a delimited list into multiple rows — one row per value — Power Query's Split Column feature is your go-to tool, and the key decision is whether to split into columns or into rows. Splitting into rows duplicates all other column values for each split piece, which is exactly the one-row-per-product-tag-combination structure the question describes. So A is correct: choosing "Split by Delimiter → Semicolon → Split into Rows" transforms a single row like {Widget, Outdoor;Clearance;Seasonal} into three rows, each repeating Widget alongside one tag. B is the most tempting distractor. Splitting into columns spreads the tags horizontally — you'd get Tags.1, Tags.2, Tags.3 as separate columns on one row. This preserves one row per product but doesn't give you the row-per-tag structure required. C is a transformation that changes the display of the Tags column (spaces instead of semicolons) but never actually separates the tags at all. You'd still have one row per product with a combined value — no unpivoting occurs. D misunderstands the goal entirely. Filtering out rows with multiple tags would discard most of your data, leaving only products with a single tag. That's the opposite of what's needed. Study tip: On Power BI exam questions involving delimited columns, ask yourself: "Do I need values spread across columns (wide) or down rows (tall/long)?" Tall/long format → Split into Rows. Wide format → Split into Columns. This distinction appears frequently and is easy to confuse under exam pressure.

Question 4

A Status column contains Active, Active-Review, Inactive, and Pending. Only cells whose complete value is Active must be changed to Enabled; all other values must remain unchanged.

How should you configure Replace Values?

  1. Find Active-Review, replace it with Enabled, and enable Match entire cell contents.
  2. Find Active, replace it with Enabled, and allow matching within longer text.
  3. Find Active, replace it with Enabled, and enable Match entire cell contents. (correct answer)
  4. Find Inactive, replace it with Enabled, and allow matching within longer text.
Explanation: When working with Replace Values in Power Query, the critical setting to understand is Match entire cell contents. By default, Power Query performs a substring match — meaning if you search for Active, it will also match Active-Review and Inactive because both contain the string "Active." This behavior is the core trap this question is testing. Since the goal is to replace only cells whose complete value is exactly Active, you need to find Active, replace it with Enabled, and enable Match entire cell contents. This tells Power Query to match the full cell value rather than any partial occurrence, so Active-Review and Inactive are safely left alone. That's why C is correct. A is wrong because it targets Active-Review as the search term and pairs it with Enabled as the replacement — but the task requires changing Active to Enabled, not Active-Review. Even the correct use of Match entire cell contents can't fix a wrong search term. B is wrong because it searches for Active but allows substring matching. This would incorrectly replace Active inside Active-Review and Inactive, corrupting data that should remain unchanged. D is entirely off-track — it replaces Inactive with Enabled, which contradicts the requirement entirely. Study tip: On Power BI exam questions involving Replace Values, always ask yourself whether the search term appears inside other values in the column. If it does, Match entire cell contents is your safeguard — and questions will almost always hinge on whether you enable it.

Question 5

A Status column initially contains Pending, Open, and Closed. The required final mapping is Pending to Open, Open to Current, and Closed unchanged. You will use two sequential Replace Values steps.

In which order should you apply the replacements?

  1. Replace Closed with Current, and then replace Pending with Open.
  2. Replace Pending with Open, and then replace Open with Current.
  3. Replace Open with Current, and then replace Pending with Open. (correct answer)
  4. Replace Open with Pending, and then replace Pending with Current.
Explanation: When chaining Replace Values steps in Power Query, order matters because each transformation operates on the output of the previous one. The key question to ask yourself is: "Could an earlier replacement accidentally create values that a later step will then modify?" Starting with the target mapping — Pending→Open, Open→Current, Closed unchanged — notice the danger: Pending must become Open, but Open must also become Current. If you replace Pending with Open first, those newly created Open values will get swept up by a subsequent Open→Current step, inadvertently converting them to Current instead of stopping at Open. The safe approach is answer C: replace Open with Current first, locking in that conversion before any new Open values are introduced. Then replace Pending with Open. Now the original Open values are already Current and won't be touched, and the original Pending values cleanly become Open. Closed is never mentioned, so it stays unchanged throughout. Answer A is a distractor that manipulates Closed, which doesn't need changing at all — it wastes a step and still doesn't resolve the Pending/Open conflict. Answer B applies replacements in the dangerous order: Pending→Open runs first, and then Open→Current converts all Open values — including the ones that just came from Pending — producing the wrong result. Answer D introduces an artificial intermediate step (Open→Pending) that creates unnecessary complexity and still risks a collision. As a strategy, whenever two replacements share a value (one step's output matches another step's input), always apply the step that touches the shared value first, so it's already transformed before new instances are created.

Question 6

A text Score column contains numeric strings, 0, and unknown. Rows whose original Score is 0 must be excluded. Rows whose original Score is unknown must remain, but their final Score value must be changed to 0.

Which order of transformations preserves the required distinction?

  1. First replace unknown with 0, and then filter out rows where Score equals 0.
  2. First filter out rows where Score equals 0, and then replace unknown with 0. (correct answer)
  3. First filter out rows where Score equals unknown, and then replace 0 with unknown.
  4. First remove the Score column, and then filter the remaining rows for values unequal to 0.
Explanation: Whenever you see a transformation sequencing question in Power BI, ask yourself: does the order change which rows survive into the next step? The answer almost always hinges on whether a value you need to act on gets destroyed before you can distinguish it. Here, you have three categories of Score values: numeric strings, literal 0, and unknown. The business rule says rows with original 0 must be deleted, while rows with unknown must survive but have their Score changed to 0. The challenge is that after any replacement, both groups could look identical — making them indistinguishable to a subsequent filter. Option B works precisely because it respects that distinction. By filtering out rows where Score equals 0 first, you eliminate only the rows that were originally 0. The unknown rows are untouched by this filter because their value is still unknown. Then, replacing unknown with 0 safely converts those surviving rows without risk of accidentally deleting them. Option A reverses the order fatally: replacing unknown with 0 first merges the two groups into a single value, 0. The subsequent filter then deletes both the original zeros and the former unknown rows — exactly what you were told not to do. Option C is logically backwards. Filtering out unknown rows removes them entirely, which violates the requirement that they survive with a changed value. Swapping 0 to unknown afterward solves nothing. Option D is a red herring — removing the Score column entirely means you have no value left to filter on meaningfully, and the logic collapses. Study tip: On transformation-order questions, trace a sample row through each step mentally. If a value you still need gets overwritten or deleted prematurely, that order is wrong.

Question 7

A fixed-width Code column contains values such as US20261234. The first two characters identify the country, the next four identify the year, and all remaining characters identify the serial number.

Which split positions should you specify to create Country, Year, and Serial columns?

  1. Split after characters 4 and 8, producing US20, 2612, and 34.
  2. Split after characters 2 and 4, producing US, 20, and 261234.
  3. Split after characters 3 and 7, producing US2, 0261, and 234.
  4. Split after characters 2 and 6, producing US, 2026, and 1234. (correct answer)
Explanation: When splitting a fixed-width text column in Power Query, your split positions mark the end of each segment — meaning "split after position N" means the first N characters form one piece, and the next segment begins at character N+1. For the value US20261234, the structure is defined precisely: characters 1–2 are the country (US), characters 3–6 are the year (2026), and characters 7–10 are the serial number (1234). To isolate these three segments, you need to cut after position 2 (end of country) and after position 6 (end of year). That's exactly what answer D does — splitting after characters 2 and 6 yields US, 2026, and 1234, which maps perfectly to Country, Year, and Serial. Answer A splits after positions 4 and 8, producing US20, 2612, and 34 — this misaligns every segment and mixes digits from the year and serial number across all three pieces. Answer B splits after positions 2 and 4, giving US, 20, and 261234 — it correctly isolates the country but only captures two digits of the year (20) instead of all four, leaving too much in the serial column. Answer C splits after positions 3 and 7, yielding US2, 0261, and 234 — each boundary is shifted one character too late compared to B and one too early compared to D, misrepresenting all three fields. A useful habit: before entering split positions in Power Query, manually count the characters in a sample value and write down the cumulative position where each segment ends. That running total directly gives you the split positions to enter.

Question 8

A source currently contains CustomerID, OrderDate, Amount, and several audit columns. The query must load only CustomerID, OrderDate, and Amount. If new audit columns are added to the source later, they must also be excluded automatically.

Which transformation should you apply?

  1. Select the three required columns and use Remove Other Columns. (correct answer)
  2. Select the current audit columns and use Remove Columns.
  3. Select the three required columns and use Remove Columns.
  4. Filter each required column to exclude null and blank values.
Explanation: When working with Power Query transformations, the key distinction to understand is the difference between column-centric and row-centric resilience. Ask yourself: "If the source schema changes, will my query break or adapt automatically?" Remove Other Columns works by keeping only the columns you explicitly select and discarding everything else — including any columns added to the source in the future. This makes option A the correct choice. By selecting CustomerID, OrderDate, and Amount and applying Remove Other Columns, your query stays clean and future-proof regardless of how many new audit columns appear downstream. Option B is tempting but fragile. Selecting the current audit columns and using Remove Columns explicitly names those columns in the query step. If new audit columns are added later, they won't be in that list and will silently load into your model — exactly the problem the requirement asks you to avoid. Option C combines the right column selection with the wrong transformation. Remove Columns drops the columns you select, not the ones you don't — so selecting your three required columns and removing them would delete the very data you need to keep. Option D is a red herring entirely. Filtering for nulls and blanks is a row-level operation that has nothing to do with excluding columns from a query. Study tip: Memorize this contrast — Remove Columns is a blocklist (named columns are dropped, new ones sneak in), while Remove Other Columns is an allowlist (only named columns survive, everything else is automatically excluded). Exam questions about schema-change resilience almost always point to the allowlist approach.

Question 9

A FullAccount column contains values such as C1048 | Alpine Sports. The loaded table must retain FullAccount exactly as supplied and also create separate AccountID and AccountName columns.

Which sequence of transformations meets the requirement?

  1. Split FullAccount by the pipe delimiter, and then duplicate the resulting AccountID column.
  2. Duplicate FullAccount, and then split the duplicate by the pipe delimiter into two columns. (correct answer)
  3. Remove FullAccount, and then split AccountName by the pipe delimiter into two columns.
  4. Replace the pipe with a space in FullAccount, and then duplicate the modified column.
Explanation: When working with column transformations in Power Query, the key question to ask is: which operations preserve the original column, and which ones destroy or modify it? The requirement here has two parts — keep FullAccount intact and create two new columns from it. The right approach, option B, is to first duplicate FullAccount, which creates an exact copy while leaving the original untouched. You then split that duplicate by the pipe delimiter (|), which Power Query automatically expands into two separate columns — one for AccountID (C1048) and one for AccountName (Alpine Sports). The result is all three columns in the table, satisfying both requirements cleanly. Option A fails because it splits FullAccount itself first, which destroys the original column — it gets replaced by the split results. Duplicating a column afterward just copies one of the split pieces, not the original combined value. Option C is doubly wrong: it removes FullAccount entirely (the opposite of preserving it) and assumes AccountName exists before the split, which it doesn't. Option D replaces the pipe with a space, permanently modifying FullAccount rather than preserving it, and duplicating that modified column gives you two altered columns, not the original or two properly separated fields. The strategic takeaway here is to remember the Duplicate → Transform pattern in Power Query: whenever you need to derive new columns from an existing one without losing the source, always duplicate first, then apply your transformations to the copy. "Split Column" is a destructive operation on the source — keep that in mind on exam questions about column preservation.

Question 10

A Power Query table contains the columns Region and Status. A report must retain rows only when Region is East or West and Status is Active.

Which filtering approach meets the requirement without creating a custom column?

  1. Select East and West in the Region filter, and then select Active in the Status filter. (correct answer)
  2. Select East in the Region filter, and then select West and Active in the Status filter.
  3. Select East and West in the Region filter, and then exclude Active in the Status filter.
  4. Select East in the Region filter, and then apply a second Region filter that selects West.
Explanation: When filtering data in Power Query, think of each column filter as its own independent condition — and for a row to be retained, all column conditions must be satisfied simultaneously. This is essentially an AND relationship across columns, while selections within a single column's filter act as OR conditions. To keep only rows where Region is East or West and Status is Active, you need exactly two separate, independent filters: one on Region and one on Status. That's precisely what A does — it selects East and West within the Region filter (rows matching either value pass that condition), then applies a separate Active selection in the Status filter. Only rows satisfying both conditions are retained. B is wrong because it places West and Active together in the Status filter. Power Query would then look for rows where Status equals "West" or "Active" — which is meaningless for the Status column and would never correctly isolate Region = West. C fails because excluding Active in the Status filter does the opposite of what's required — it would keep every status except Active, retaining Inactive, Pending, or any other value. D applies two sequential filters on the same Region column. The second Region filter would override or further restrict the first, likely returning no rows (you can't be both East and West simultaneously), rather than combining them. As a study tip, remember: within one column's filter, selections are OR; across different column filters, conditions are AND. When you need multiple values from one column, select them all in that single column's filter rather than stacking separate filters on the same column.