Microsoft Power BI Quiz: Pivot Unpivot
10 questions · exam conditions
0:00
Pivot UnpivotQuestion 1 of 10

A telemetry query contains DeviceID, RecordedAt, Metric, and Reading. Each device can have multiple readings for the same metric. You need one row per device, one column per metric, and only the most recent reading for each device-metric combination.

Which transformation sequence best meets the requirement without substituting the largest reading for the latest reading?

Pivot Metric using Reading, and select Maximum as the aggregation.
Pivot RecordedAt using Reading, and select Don't aggregate.
Retain the latest row per device and metric, then pivot Metric using Reading.
Unpivot Metric and Reading, then group the resulting attribute values by device.
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Pivot Unpivot

Practice Pivot Unpivot 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 Pivot Unpivot, 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 telemetry query contains DeviceID, RecordedAt, Metric, and Reading. Each device can have multiple readings for the same metric. You need one row per device, one column per metric, and only the most recent reading for each device-metric combination.

Which transformation sequence best meets the requirement without substituting the largest reading for the latest reading?

  1. Pivot Metric using Reading, and select Maximum as the aggregation.
  2. Pivot RecordedAt using Reading, and select Don't aggregate.
  3. Retain the latest row per device and metric, then pivot Metric using Reading. (correct answer)
  4. Unpivot Metric and Reading, then group the resulting attribute values by device.
Explanation: When transforming data in Power Query, order of operations is everything — especially when your goal is "most recent" rather than "largest." These two concepts often collide in telemetry data where readings are numerical, meaning the maximum value and the latest value are not guaranteed to be the same. The correct approach, C, solves this cleanly by filtering before pivoting. You first reduce each device-metric combination to its single most recent row — using a group-by on DeviceID and Metric, keeping the row where RecordedAt is maximum. Once each combination has exactly one row, pivoting Metric using Reading with Don't Aggregate works perfectly because there's nothing left to aggregate. A is the classic trap this question is designed to expose. Pivoting with Maximum aggregation selects the highest reading value, not the reading from the most recent timestamp. For a sensor that's trending downward, this silently returns stale data without any error. B pivots on RecordedAt instead of Metric, which produces date-stamp columns rather than metric-name columns — completely backwards from the requirement of one column per metric. D describes unpivoting, which is the opposite of what you need. Your data is already in a tall, attribute-value format (Metric, Reading); unpivoting again would corrupt the structure rather than reshape it toward the one-row-per-device goal. Study tip: On Power BI transformation questions, whenever you see "most recent" paired with numerical data, treat it as a red flag — always filter to the correct row first, then aggregate or pivot. Never rely on value-based aggregations to proxy for time-based selection.

Question 2

A query has three employee rows and the columns EmployeeID, Jan, and Feb. Of the six month-value cells, two contain null and four contain numeric values. You select EmployeeID and apply Unpivot Other Columns.

How many data rows will the resulting query contain, excluding the header?

  1. Three rows, because the transformation retains one row for each employee.
  2. Four rows, because null month-value pairs are omitted during unpivoting. (correct answer)
  3. Six rows, because each employee-month combination becomes a row, including nulls.
  4. Eight rows, because both the attribute and value fields create separate records.
Explanation: When working with Unpivot Other Columns in Power Query, the key behavior to understand is how Power BI handles null values during the transformation: nulls are silently dropped. Each non-null value in the unpivoted columns becomes its own row, pairing the original row's key column(s) with an "Attribute" column (the month name) and a "Value" column (the numeric amount). Here's the math: you start with 3 employees × 2 month columns = 6 potential employee-month combinations. Two of those cells contain nulls, so Power Query discards them. That leaves 4 valid pairs, which become 4 data rows — confirming B is correct. A is wrong because Unpivot Other Columns doesn't keep one row per employee. It expands rows outward, one per non-null attribute-value pair, so the row count grows, not stays flat. C would be correct only if Power Query preserved nulls during unpivoting, but it doesn't. By default, null pairs are omitted entirely from the output. If you need to retain nulls, you'd have to explicitly use a different approach. D confuses how the output is structured. The Attribute and Value fields appear as columns, not separate rows. Every unpivoted pair occupies exactly one row with two new columns, so there's no doubling of records. Study tip: Remember that in Power Query, "Unpivot" and "Unpivot Other Columns" both drop nulls by default — this is a favorite exam detail. If you see a question involving unpivoting with nulls present, always subtract the null count from the total possible combinations to get your row count.

Question 3

A query contains ProductID, Sales_Q1, Sales_Q2, Cost_Q1, and Cost_Q2. You need the result to contain ProductID, Quarter, Sales, and Cost, with one row per product-quarter combination.

Which sequence of Power Query transformations produces the required shape?

  1. Unpivot the four measure columns, split Attribute into measure and quarter, then pivot measure using Value. (correct answer)
  2. Pivot the four measure columns, split ProductID into measure and quarter, then unpivot Value.
  3. Unpivot the four measure columns, split Value into measure and quarter, then pivot Attribute.
  4. Transpose the query, promote the first row to headers, then pivot ProductID using quarter.
Explanation: When reshaping data from a "wide" format (multiple measure columns) to a "long" format (one row per product-quarter combination), you need to think carefully about the sequence of transformations — each step must produce a table that makes the next step possible. Start by unpivoting the four measure columns (Sales_Q1, Sales_Q2, Cost_Q1, Cost_Q2). This gives you an Attribute column containing values like Sales_Q1 and a Value column with the corresponding numbers. Next, split Attribute on the underscore, producing two columns: one for the measure type (Sales or Cost) and one for the quarter (Q1 or Q2). At this point you have ProductID, Quarter, Measure, and Value. Finally, pivot the Measure column using Value as the values column — this fans Sales and Cost out into their own columns, giving you exactly the four-column result required. That's option A, and it's the correct answer. Option B falls apart immediately because pivoting measure columns (which are already attributes, not values) doesn't make structural sense here — you'd need to unpivot first, not last. Option C splits the wrong column: Value holds numeric data, not compound names like Sales_Q1, so splitting it produces nonsense. Then pivoting Attribute afterward would recreate the wide format you started with, the opposite of what you want. Option D uses Transpose, which swaps rows and columns entirely — useful in niche scenarios, but irrelevant here since ProductID isn't a header row that needs promoting. A helpful rule of thumb: unpivot first to consolidate, split to separate combined names, then pivot to spread distinct categories into columns. This "unpivot → split → pivot" pattern appears frequently in Power BI exam questions involving multi-measure, multi-period data.

Question 4

A query contains AssetID, Property, and PropertyValue. PropertyValue is text, and Property contains values such as Color and Location. An upstream join sometimes creates exact duplicate rows. You need one row per asset with property names as columns. Conflicting property values should remain visible as data-quality errors rather than being silently selected.

Which approach should you use?

  1. Remove exact duplicate rows, then pivot Property using PropertyValue with Don't aggregate. (correct answer)
  2. Keep every duplicate row, then pivot PropertyValue using Property with Count.
  3. Remove duplicate asset identifiers, then pivot Property using PropertyValue with Maximum.
  4. Unpivot AssetID, then group PropertyValue and concatenate all text values into one field.
Explanation: When reshaping data from a vertical (entity-attribute-value) structure into a wide table, you need to think carefully about two separate concerns: row cleanliness before pivoting, and how the pivot handles conflicting values after. The right sequence is exactly what A describes. First, remove exact duplicate rows — rows where every column matches perfectly. These are harmless artifacts from the upstream join and can safely disappear. Then, pivot Property using PropertyValue with Don't aggregate. This setting tells Power Query to place the raw text value directly into the new column. Crucially, if two rows share the same AssetID and Property but have different PropertyValue entries (true conflicts), Power Query will throw an error for that cell rather than silently picking one. That error is your data-quality flag — exactly what the scenario requires. B is wrong on two levels: it keeps true duplicates that will cause errors you didn't want, and pivoting PropertyValue by Property with Count produces numeric counts, not the property values themselves — the columns would be meaningless for this use case. C removes duplicates only on AssetID, which would silently drop legitimate rows for assets with multiple properties. Using Maximum then silently picks the highest value when conflicts exist, hiding the data-quality issue instead of surfacing it. D goes in the wrong direction entirely — unpivoting AssetID makes no sense here, and concatenating values into a single field buries conflicts inside a string rather than exposing them as errors. Your study tip: on pivot questions, always ask what happens to conflicting values — the aggregation choice is often the trap, not the pivot column selection itself.

Question 5

A staffing query contains EmployeeID, EmploymentStatus, JanHours, FebHours, and MarHours. You need a long table with EmployeeID, EmploymentStatus, Month, and Hours. The text status must not appear in the numeric Hours column.

Which transformation most directly creates the required structure?

  1. Select EmploymentStatus, then apply Unpivot Other Columns and rename the attribute as EmployeeID.
  2. Select only EmployeeID, then apply Unpivot Other Columns and convert every resulting value to number.
  3. Select the three hour columns, then pivot them using EmploymentStatus as the values column.
  4. Select EmployeeID and EmploymentStatus, then apply Unpivot Other Columns and rename the generated columns. (correct answer)
Explanation: When reshaping data from wide to long format in Power Query, the key concept is Unpivot Other Columns — and the critical decision is which columns you select before applying it. The columns you select become your "anchor" identifiers that stay as rows; everything else gets unpivoted into attribute-value pairs. To get EmployeeID, EmploymentStatus, Month, and Hours, you need both identifier columns (EmployeeID and EmploymentStatus) protected as anchors. That's exactly what D does: select both identifiers, apply Unpivot Other Columns, and Power Query automatically unpivots JanHours, FebHours, and MarHours into two new columns — one for the month name (rename to Month) and one for the numeric hours (rename to Hours). Since only numeric hour columns get unpivoted, the Hours column stays clean with no text contamination. A is wrong because anchoring only EmploymentStatus means EmployeeID also gets unpivoted into the attribute column — you'd lose the proper employee identifier structure entirely, and renaming the attribute column to EmployeeID doesn't fix that. B is wrong because anchoring only EmployeeID means EmploymentStatus gets unpivoted alongside the hour columns, dumping text values like "Full-Time" directly into the values column. Converting everything to a number would error out or corrupt those rows — exactly the problem the question warns against. C describes a Pivot operation, not an Unpivot. Pivoting spreads rows into columns, which is the opposite transformation you need here. Study tip: On Power BI exam questions involving Unpivot, always ask yourself: which columns should stay as identifiers? Select all of them before applying Unpivot Other Columns.

Question 6

A transaction query initially contains CustomerID, Month, and Amount, with several transactions for the same customer and month. You pivot Month using Amount and Sum, and later unpivot the resulting month columns.

What does the final long-form query contain?

  1. The original transaction rows, because unpivot always reverses a previous pivot operation exactly.
  2. One row per customer-month total, because the pivot aggregation removed transaction-level detail. (correct answer)
  3. One row per original transaction, but with month names stored in the values column.
  4. One row per customer only, because unpivot retains the first populated month for each key.
Explanation: When working with Pivot and Unpivot in Power Query, the critical concept to understand is data loss through aggregation. Ask yourself: does any step in the transformation collapse multiple rows into one? If yes, you cannot fully recover the original granularity by reversing that step. Here's what happens in this scenario. The pivot operation groups by CustomerID and Month, then sums the Amount values. If a customer has three transactions in January totaling $300, the pivot stores only 300 — the individual $100, $150, and $50 rows are gone permanently. When you then unpivot the month columns, Power Query reshapes that wide table back into a long format, producing one row per customer-month combination — but each row reflects the aggregated total, not the original transactions. B is correct because the pivot's Sum aggregation irreversibly collapsed transaction-level detail into one value per customer-month pair, and unpivot simply reshapes that already-summarized data. A is wrong because unpivot does not reverse a pivot "exactly." It can reverse the shape, but it cannot recover data that was destroyed by aggregation. This is a common and dangerous misconception. C is wrong because individual transaction rows were eliminated during the pivot step. There is no mechanism for unpivot to recreate rows that no longer exist in the query. D is wrong because unpivot operates on each column independently — it does not reduce rows to one per customer or retain only the first value. The key study tip: aggregation is a one-way door. Any transformation that groups and summarizes data cannot be undone by reshaping alone.

Question 7

A source includes AccountID, Department, and period columns whose names begin with FY, such as FY2025 and FY2026. Future fiscal-year columns must be unpivoted automatically. The source might also add new descriptive columns, and those columns must remain identifiers rather than being unpivoted.

Which design is most resilient to both types of schema change?

  1. Use Unpivot Other Columns after selecting only AccountID, so every future column regardless of its name prefix is treated as a period and converted to rows.
  2. Explicitly list FY2025 and FY2026 in an Unpivot Only Selected Columns step, so any new descriptive columns added later are automatically left as identifiers.
  3. Build a dynamic list of columns whose names begin with FY, and pass that generated list to an unpivot operation so future fiscal-year columns are included and new descriptive columns are excluded. (correct answer)
  4. Pivot Department first to reduce row granularity, and then transpose any fiscal-year columns the source adds so that period values move into rows rather than columns.
Explanation: When designing a Power Query transformation that must survive two distinct types of schema change — new fiscal-year columns and new descriptive columns — you need a strategy that handles both dynamically rather than hardcoding anything. The most resilient approach is C: building a dynamic list of columns whose names start with "FY" and passing that list to Table.Unpivot. In M code, you'd use something like List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "FY")) to generate that list at refresh time. This means any future FY2027, FY2028, etc. columns are automatically captured, while new descriptive columns added by the source — which won't begin with "FY" — are naturally left as identifiers. Both schema changes are handled without touching the query. A is tempting but dangerous. "Unpivot Other Columns" after selecting only AccountID would unpivot every non-selected column, meaning any new descriptive column the source adds (like Region or Manager) would accidentally become period rows instead of identifier columns. It solves one problem while creating another. B hardcodes the column names FY2025 and FY2026 explicitly. While new descriptive columns are safely left alone, any future fiscal-year column is silently ignored — it never gets unpivoted. You'd have to manually update the query each year. D is a red herring. Pivoting Department and transposing columns are unrelated operations that don't address the schema-resilience problem at all, and would likely distort the data model. The study tip: when you see "automatically" and "resilient to future changes" in the same question, think dynamic column selection using List.Select plus Text.StartsWith — that's Power Query's pattern for prefix-based unpivoting.

Question 8

A sales query contains the columns CustomerID, Region, Jan, Feb, and Mar. The source will add a new month column each month. The required output must retain CustomerID and Region and create one row per customer-region-month combination.

Which Power Query transformation should you use so that future month columns are reshaped automatically without editing the query?

  1. Select CustomerID and Region, and then select Unpivot Other Columns. (correct answer)
  2. Select Jan, Feb, and Mar, and then select Unpivot Only Selected Columns.
  3. Select CustomerID, and then select Unpivot Other Columns, leaving Region as an attribute.
  4. Select Jan, Feb, and Mar, and then select Pivot Column using Region as values.
Explanation: When reshaping data in Power Query, the key distinction to understand is the difference between the three unpivot options: Unpivot Columns, Unpivot Other Columns, and Unpivot Only Selected Columns. The critical question is always: which columns are stable identifiers, and which columns might change over time? Since CustomerID and Region are your fixed identifier columns, the correct approach is to select them and choose Unpivot Other Columns — answer A. This tells Power Query: "keep these two columns as-is, and unpivot everything else." The beauty of this approach is its future-proofing: when April, May, or December columns appear in the source data, they are automatically treated as additional month columns to unpivot, with zero query edits required. Answer B is the most tempting trap. Manually selecting Jan, Feb, and Mar then using Unpivot Only Selected Columns hard-codes exactly those three months. Next month, when Apr appears, it stays as a separate column — the query never adapts automatically. Answer C is flawed because leaving Region out of the selection means it would be treated as an attribute/value column rather than an identifier, corrupting your output structure. Both CustomerID and Region must be selected together. Answer D confuses the direction entirely. Pivot Column does the opposite — it takes rows and spreads them into columns — which would move you further from the desired row-per-combination output. Study tip: On Power BI exam questions about unpivoting, always ask yourself "what's stable vs. what might grow?" Select the stable columns, then choose Unpivot Other Columns for automatic future flexibility.

Question 9

A query contains Branch, Manager, Month, and Sales. Managers can change during the year. You need exactly one row per branch, month names as columns, and total sales for each branch-month combination. When you pivot Month, the result contains multiple rows for some branches.

What should you do before or as part of the pivot operation?

  1. Keep Manager, pivot Month, and use Don't aggregate for Sales.
  2. Remove Manager, pivot Month, and use Sum for Sales. (correct answer)
  3. Remove Branch, pivot Manager, and use Sum for Sales.
  4. Keep Manager, unpivot Month, and use Sales as the attribute column.
Explanation: When pivoting data in Power Query, your goal is to produce a clean, unambiguous table where each row represents a unique combination of your remaining columns. The key question to ask yourself is: what columns will form the row identity after pivoting? Here, you want exactly one row per branch, with months spread across columns. The problem is that Manager creates multiple rows per branch (since managers change throughout the year). Even if you pivot Month, Power Query still sees distinct Branch-Manager combinations as separate rows — so you end up with duplicates. The fix is to remove Manager before pivoting, leaving Branch as the only row identifier. Then, pivoting Month with Sum for Sales correctly aggregates all sales per branch per month into a single value. That's exactly what B does, making it the correct answer. A is wrong because keeping Manager doesn't resolve the multiple-row problem — you'd still get one row per Branch-Manager combination, not one row per branch. Choosing "Don't aggregate" makes this worse, since Power Query can't collapse duplicates at all. C is wrong on two levels: removing Branch eliminates your primary identifier, and pivoting Manager produces manager names as column headers — the opposite of what the question asks for. D is wrong because unpivoting Month would go in the reverse direction, creating more rows rather than spreading months into columns. You'd be moving away from the desired shape, not toward it. Your study tip: when a pivot produces unexpected duplicate rows, always look at which non-pivoted columns are creating extra granularity and remove them first.

Question 10

A query contains Store, Product, and Units. For one store-product combination, separate source rows contain unit values of 2 and 3. You need one row per store, one column per product, and a value of 5 for that combination.

How should you configure the Pivot Column transformation?

  1. Pivot Store, use Units as the values column, and select Count.
  2. Pivot Product, use Store as the values column, and select Don't aggregate.
  3. Pivot Product, use Units as the values column, and select Sum. (correct answer)
  4. Pivot Units, use Product as the values column, and select Maximum.
Explanation: When working with Pivot Column in Power Query, you need to think carefully about three things: which column becomes the new column headers, which column supplies the values that fill those cells, and how duplicate combinations get resolved. The goal here is one row per store and one column per product, with unit values combined. That means Product must be pivoted — its distinct values (e.g., "Widget," "Gadget") become the new column headers. The cells should be filled with Units, and since two source rows contribute values of 2 and 3 for the same store-product pair, you need an aggregation that combines them into 5. Sum is exactly that aggregation. So option C is correct. Option A fails on two levels: pivoting Store would make stores into column headers (the opposite of what's needed), and Count would return 2 — the number of rows — not 5, the total units. Option B pivots Product correctly but uses Store as the values column. Store contains text identifiers, not numeric unit counts, and Don't Aggregate only works safely when each combination has exactly one row — here there are two, which would cause an error or incorrect result. Option D pivots Units, which are numeric values, not meaningful column headers. Beyond the conceptual error, Maximum would return 3 (the larger value), not 5. Study tip: On Power BI exam questions about Pivot Column, always ask yourself three things in order — What becomes the headers? What fills the cells? How do duplicates collapse? Matching each to the business requirement will guide you to the right configuration every time.