Microsoft Power BI Quiz: Power Query Editor
10 questions · exam conditions
0:00
Power Query EditorQuestion 1 of 10

A query imports a large CSV file. Column profiling is enabled, and the CustomerCode column shows no errors. During a full refresh, however, type-conversion errors occur in CustomerCode values near the end of the file. The Power Query Editor is currently using its default profiling scope.

You need to inspect the column using profiling statistics that include the values causing the refresh errors. What should you do?

Enable profiling over the entire data set, and then review Column quality and Column distribution for CustomerCode.
Retain profiling over the top 1,000 rows, and then increase the number of bins shown by Column distribution.
Sort CustomerCode in descending order, and then review Column quality while retaining the default profiling scope.
Refresh the preview, and then review the Applied Steps pane without changing the current profiling scope.
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Power Query Editor

Practice Power Query Editor 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 Power Query Editor, 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 query imports a large CSV file. Column profiling is enabled, and the CustomerCode column shows no errors. During a full refresh, however, type-conversion errors occur in CustomerCode values near the end of the file. The Power Query Editor is currently using its default profiling scope.

You need to inspect the column using profiling statistics that include the values causing the refresh errors. What should you do?

  1. Enable profiling over the entire data set, and then review Column quality and Column distribution for CustomerCode. (correct answer)
  2. Retain profiling over the top 1,000 rows, and then increase the number of bins shown by Column distribution.
  3. Sort CustomerCode in descending order, and then review Column quality while retaining the default profiling scope.
  4. Refresh the preview, and then review the Applied Steps pane without changing the current profiling scope.
Explanation: When working with Power Query's column profiling, one of the most important settings to understand is the profiling scope. By default, Power Query profiles only the top 1,000 rows of your dataset. This means Column quality, Column distribution, and Column profile statistics are all calculated from that limited sample — not your entire file. If errors exist only in rows beyond row 1,000, the profiling window will show a clean bill of health, even though a full refresh will fail. That's exactly the trap described here. The CustomerCode column appears error-free in the editor because the problematic values sit past the 1,000-row boundary. The fix is to change the profiling scope to the entire dataset by selecting "Column profiling based on entire data set" in the status bar at the bottom of the Power Query Editor. Once you do that, Column quality will surface those type-conversion errors, and Column distribution will show you how those bad values are distributed — giving you what you need to investigate and fix them. Answer A is correct. Answer B is wrong because increasing the number of bins in Column distribution is a display tweak, not a scope change — it still only covers 1,000 rows. Answer C is a tempting workaround, but sorting descending doesn't guarantee the problematic rows fall within the top 1,000, and it doesn't actually expand the profiling scope. Answer D simply refreshes the preview without changing anything meaningful about what rows are being analyzed. A reliable study tip: whenever a Power BI exam question mentions "default profiling scope" alongside errors that appear only during a full refresh, that's your signal that the top-1,000-row limitation is the root cause and expanding the scope is the solution.

Question 2

A query reads several million sales rows from a SQL database. Its steps are Changed Type, Invoked Custom Function, and Filtered Rows. The custom function cannot fold to the source. The filter retains only transactions from the current year, but refresh still retrieves all sales rows before filtering them locally.

You need to reduce the number of rows retrieved from SQL while preserving the custom transformation. What should you do?

  1. Move Filtered Rows after Invoked Custom Function, and verify folding by refreshing the preview at the final step.
  2. Move Filtered Rows before Invoked Custom Function, and verify that the filter step can be represented by a native query. (correct answer)
  3. Insert Table.Buffer before Filtered Rows, and then invoke the custom function after the buffered table is filtered.
  4. Keep the existing order, and enable background data previews so that SQL processes the filter during refresh.
Explanation: Whenever you see a question about Power Query performance, think about query folding — the ability for Power Query to push transformation steps back to the data source as native SQL, so the database does the heavy lifting instead of loading everything into memory first. The key insight here is that query folding breaks the moment a non-foldable step appears in the pipeline. Once the custom function runs, Power Query can no longer translate subsequent steps into SQL. So if your filter comes after the custom function, it runs locally — meaning all millions of rows must be retrieved first, then filtered in memory. The fix in option B is to move Filtered Rows before Invoked Custom Function. Because the filter step now appears before the folding break, Power Query can push it to SQL as a native query, retrieving only current-year rows. The custom function then runs locally on that smaller dataset. Verifying via native query confirms folding is actually occurring, not just assumed. Option A is wrong because moving Filtered Rows after the custom function keeps it on the non-foldable side of the pipeline — exactly the problem you already have. Refreshing the preview doesn't fix folding; it just re-runs the broken sequence. Option C misunderstands Table.Buffer, which forces evaluation in memory and actually prevents folding rather than enabling it. It solves nothing here. Option D is a trap — background data previews affect the Power Query editor experience only and have zero impact on what SQL processes during a scheduled or manual refresh. Study tip: Think of query folding like a one-way door — once a non-foldable step appears, everything after it runs locally. Always place filters and row-reducing steps before that break point.

Question 3

You use the Folder connector to combine monthly CSV files. Every file has the same columns and contains one footer row with the text End of Report. The generated combined query appends the files by invoking a transformation function for each binary file.

You need to remove the footer from every file without relying on the number of files in the folder. Where should you add the transformation?

  1. In the final combined query, remove one bottom row after all transformed files have been expanded.
  2. In the folder listing query, filter out rows whose file name contains the text End of Report.
  3. In the sample-file transformation, remove the bottom row before the transformation is invoked for each file. (correct answer)
  4. In the final combined query, remove duplicate rows after all transformed files have been appended.
Explanation: When you use the Folder connector in Power BI to combine multiple files, the process works through a layered architecture: a folder listing query, a sample-file transformation function, and a final combined query that invokes that function on each file's binary. Understanding where to apply changes is the core concept being tested here. Because each file contains its own footer row, the fix must happen at the individual file level — inside the sample-file transformation. This is exactly what option C recommends. When you remove the bottom row there, that step gets baked into the transformation function, which is then applied independently to every file before the results are appended together. The footer is eliminated from each source, regardless of how many files exist in the folder. Option A fails because removing one bottom row from the final combined query only removes a single row from the entire appended dataset — not one row per file. If you have 12 monthly files, 11 footers remain in your data. Option B is a misunderstanding of what the folder listing query contains: it lists file metadata (names, paths, dates), not the row-level contents of each CSV, so filtering on "End of Report" there would do nothing. Option D mistakes the problem entirely — footer rows are not duplicates of data rows, so removing duplicates won't target them, and this step wouldn't address the footer at all. A good rule of thumb: any transformation that needs to happen per file belongs in the sample-file transformation function, not the final combined query. Changes in the combined query apply to the already-merged result, making per-file corrections unreliable.

Question 4

A text column contains dates supplied in day/month/year format, including 03/04/2026. The Power BI Desktop regional setting uses month/day/year. Automatically changing the column to the Date type produces valid values, but some dates are interpreted as the wrong calendar day.

You need to convert the column correctly without manually splitting and recombining each date. What should you do?

  1. Use Change Type with Locale, select Date, and choose a locale that interprets dates as day/month/year. (correct answer)
  2. Replace each slash with a hyphen, and then apply the Date type using the current regional setting.
  3. Apply the Date type first, and then use Transform to reverse the month and day components.
  4. Convert the column to a decimal number, and then convert that result to Date using the current locale.
Explanation: When Power BI imports a text column as dates, it relies on the regional locale to interpret the format. If your data uses day/month/year but Power BI's regional setting expects month/day/year, ambiguous dates like 03/04/2026 get silently misread — March 4th instead of April 3rd. The problem isn't that conversion fails; it's that it succeeds incorrectly, which is harder to catch. The right approach is A: use Change Type with Locale, select Date, and choose a locale that treats dates as day/month/year (such as English, United Kingdom). This tells Power Query exactly how to interpret the incoming format, overriding the desktop's default regional setting on a per-column basis. No manual splitting or formula work required. B is tempting but flawed — replacing slashes with hyphens changes the delimiter, not the field order. Month/day confusion persists because the underlying sequence is still wrong for your regional setting. C gets the order of operations backwards. Once you've applied the Date type, Power BI has already committed to an interpretation. You can't "un-misread" a date by rearranging components afterward; the calendar value is already locked in incorrectly. D is nonsensical in this context. Dates are not stored as decimal numbers in a way that a locale conversion would reconstruct the correct day/month/year sequence. This path would produce errors or garbage values. Study tip: On Power BI exam questions involving date parsing, always ask yourself: is the format mismatch a delimiter issue or a locale issue? Locale issues require Change Type with Locale — that's the dedicated tool for this exact scenario.

Question 5

An Orders query contains one row per order. A Customers query contains one row per customer and includes a Segment column. Some orders have customer identifiers that are not present in Customers because the customer master is loaded later.

You need to add Segment to Orders while retaining every order, including orders without a customer match. Which transformation should you use?

  1. Merge Orders with Customers by using an inner join, and then expand Segment from the nested customer table.
  2. Merge Customers with Orders by using a left outer join, and then expand the order fields from the nested table.
  3. Append Customers to Orders, and then group the combined rows by the common customer identifier.
  4. Merge Orders with Customers by using a left outer join, and then expand Segment from the nested customer table. (correct answer)
Explanation: When working with merge operations in Power Query, the key decision is always: which table is the "anchor" (left side), and what join type controls which rows survive? A left outer join keeps every row from the left table and brings in matching data from the right — returning null where no match exists. That behavior is exactly what you need when orders must be preserved even if no customer record exists yet. D is correct because it places Orders on the left side of the merge with Customers on the right, uses a left outer join, and then expands only the Segment column from the nested table. Every order row is retained; orders without a customer match simply show null in Segment. This is the precise, minimal transformation the scenario requires. A is wrong because an inner join only keeps rows where a match exists in both tables. Any order missing a customer identifier would be dropped — directly violating the "retain every order" requirement. B is wrong because it reverses the table positions: Customers is on the left, Orders on the right. A left outer join would then keep every customer, not every order, and orders without a matching customer would be lost entirely. The direction of the join matters as much as the join type. C is wrong because Append stacks rows vertically (like SQL UNION), not horizontally. Appending then grouping cannot reliably add a Segment column to order rows — it conflates two unrelated operations and would corrupt the data structure. A handy rule: the left table in a left outer join always survives completely — so make the table whose rows you must preserve the left table.

Question 6

A query has Product, Month, Metric, and Value columns. Metric contains Sales or Units. Multiple source rows can exist for the same Product, Month, and Metric because the source records daily activity. You need one row per Product and Month, with separate Sales and Units columns containing monthly totals.

Which Power Query transformation should you perform?

  1. Pivot Metric using Value as the values column, and select Sum as the aggregation operation. (correct answer)
  2. Pivot Value using Metric as the values column, and select Count as the aggregation operation.
  3. Remove duplicates from Product, Month, and Metric, and then pivot Metric without an aggregation operation.
  4. Unpivot Metric and Value while retaining Product and Month as the identifying columns.
Explanation: When reshaping data in Power Query, the key question to ask yourself is: "Do I need to spread row values into new columns, or consolidate columns into rows?" Here, you want to turn the values in the Metric column (Sales, Units) into separate column headers — that's a pivot operation. Because multiple source rows share the same Product/Month/Metric combination (daily activity), you also need to aggregate those values rather than simply extracting a single one. Answer A is correct because pivoting the Metric column using Value as the values column creates the Sales and Units columns you need, and selecting Sum as the aggregation rolls up all daily entries into monthly totals — exactly what the scenario requires. Answer B reverses the columns entirely. Pivoting Value (which contains numeric amounts) using Metric as the values column makes no logical sense — you'd be trying to create column headers from numbers like 1200, 45, etc. Count as the aggregation compounds the error by counting records instead of summing amounts. Answer C fails because removing duplicates from Product, Month, and Metric keeps only one row per combination, discarding all the daily detail before aggregation happens. Pivoting without aggregation after that would silently drop data whenever duplicates still exist in the underlying logic, and it doesn't sum the totals you need. Answer D moves in the opposite direction. Unpivoting takes existing columns and stacks them into rows — but your data is already in that tall format. Unpivoting would make it longer, not produce the wide Sales/Units columns required. Study tip: On Power BI questions, "pivot = wide" (rows become columns) and "unpivot = tall" (columns become rows). When multiple rows share the same key, always pair pivoting with an aggregation function.

Question 7

You append a CurrentCustomers query to an ArchiveCustomers query. CurrentCustomers uses the column name CustomerID, while ArchiveCustomers uses Customer ID. The columns represent the same business key and currently have compatible values. After appending, the result contains two mostly null identifier columns.

You need the appended query to contain one consistently typed identifier column. What should you do before the append operation?

  1. Reorder both identifier columns to the same ordinal position, and let Append Queries match them by position.
  2. Rename the identifier columns to the same name and assign the same data type in both source queries. (correct answer)
  3. Replace nulls in both identifier columns with zero, and then append the queries using their existing names.
  4. Transpose both source queries so the identifiers occupy the same row, and then append the transposed results.
Explanation: When appending queries in Power Query, columns are matched by name, not by position or data type. If two source queries have identifier columns with different names — like CustomerID and Customer ID — Power Query treats them as separate columns and fills in nulls wherever a row comes from the other query. That's exactly the problem described here: two mostly null columns instead of one clean identifier column. The fix is straightforward: before the append, go into each source query and rename the identifier columns to the same name, and confirm they share the same data type. When both columns share a name and type, Power Query merges them into a single column during the append — giving you one consistently populated identifier column. That's why B is correct. A is a common misconception. Power Query's Append operation does not use column position for matching — it always matches by name. Reordering columns does nothing to resolve a name mismatch. C replaces nulls with zeros, but the root cause is the name mismatch, not the null values themselves. After appending with mismatched names, you'd still have two separate columns; replacing nulls just hides the symptom without fixing the structure. D is a misapplication of the Transpose transformation. Transposing flips rows and columns — it's useful for reshaping tabular structure, not for resolving column-name conflicts during an append. Using it here would corrupt your data layout entirely. Study tip: Whenever you see an Append Queries scenario on the exam, immediately ask yourself: "Do the column names match exactly?" Name mismatches are the most common source of unexpected null columns after an append.

Question 8

A StagingSales query connects to a source and performs shared cleansing steps. You need separate InternetSales and StoreSales queries that start from the cleansed result and then apply channel-specific filters. Future changes to the shared cleansing steps must automatically flow to both downstream queries. The staging result should not be loaded into the model.

How should you configure the queries in Power Query Editor?

  1. Duplicate StagingSales twice, apply separate filters, and disable load only for the two duplicated queries.
  2. Copy the M code into two blank queries, apply separate filters, and delete the original staging query.
  3. Append StagingSales to itself twice, apply separate filters, and disable load for the appended query.
  4. Reference StagingSales twice, apply separate filters, and disable load for the StagingSales query. (correct answer)
Explanation: When you need multiple queries to build on shared transformation logic in Power Query, the key concept to understand is the difference between referencing and duplicating a query. A reference creates a live dependency — the downstream query reads the current output of its source query, so any upstream changes automatically propagate. A duplicate, by contrast, copies the M code as a standalone query with no ongoing connection to the original. The correct approach is D: reference StagingSales twice to create InternetSales and StoreSales, apply channel-specific filters in each, then disable load on StagingSales. This way, both downstream queries inherit all shared cleansing steps dynamically. When you update StagingSales, both channels update automatically. Disabling load on StagingSales prevents that intermediate result from bloating your data model while keeping it active as a logical step in the query chain. Option A is tempting but wrong — duplicating creates independent copies of the M code, not live links. Future changes to StagingSales would not flow through to the duplicated queries; you'd have to update all three manually. Option B compounds this problem by deleting the original, leaving you with two completely isolated queries and no shared foundation at all. Option C misuses Append, which stacks rows from multiple tables together — it has nothing to do with inheritance or shared transformation logic, and it would produce incorrect, combined data rather than filtered subsets. A useful rule of thumb for the PL-300 exam: whenever a question mentions "automatically flow" or "propagate changes," think Reference, not Duplicate. Reference = inheritance; Duplicate = copy.

Question 9

A worksheet contains StoreID, Region, Jan, Feb, and Mar columns. Each month, a new month column is added to the worksheet. You need a stable query that produces StoreID, Region, Month, and Revenue rows and automatically includes future month columns.

Which transformation should you use?

  1. Select the existing month columns and choose Unpivot Columns, leaving StoreID and Region unchanged.
  2. Select StoreID and Region and choose Unpivot Other Columns, then rename Attribute and Value appropriately. (correct answer)
  3. Select StoreID and Region and choose Pivot Column, using each month column as a values field.
  4. Transpose the entire table, promote the first row to headers, and append future months manually.
Explanation: When working with Power Query transformations that need to handle growing data, the key question is: does my transformation break when new columns are added? This distinction separates static selections from dynamic, future-proof logic. The scenario requires converting wide monthly columns (Jan, Feb, Mar, and future months) into long-format rows with Month and Revenue fields. The right tool is Unpivot Other Columns, which works by telling Power Query what columns to keep fixed rather than which columns to unpivot. By selecting StoreID and Region and choosing Unpivot Other Columns (answer B), Power Query automatically unpivots everything else — including any new month columns added later. After unpivoting, you simply rename the auto-generated "Attribute" column to "Month" and "Value" to "Revenue." The query stays stable with zero manual updates. Answer A is the critical trap here. Selecting the existing month columns and choosing Unpivot Columns hardcodes Jan, Feb, and Mar into the query. When April is added, it won't be unpivoted — the query silently ignores it. This is the exact opposite of what you need. Answer C uses Pivot Column, which does the reverse: it turns rows into columns. That would widen your table, not create the Month/Revenue row format you need. Answer D's manual transpose approach is impractical and fragile — it requires manual intervention every time a new month appears, which directly contradicts the requirement for a stable, automatic query. Remember this pattern: when you need a transformation to automatically include future columns, always anchor on the columns you want to keep, not the ones you want to transform.

Question 10

A Transactions query should contain one row per AccountID and PostingDate, but all original transaction columns must be available for investigation. You suspect that some composite keys occur more than once. Simply removing duplicate rows is not acceptable because the duplicate records must be reviewed.

Which sequence best identifies and retains all records belonging to duplicated composite keys?

  1. Group by AccountID only, count rows, filter counts above one, and expand the original PostingDate values.
  2. Sort by AccountID and PostingDate, remove duplicates on both columns, and retain the first transaction found.
  3. Group by AccountID and PostingDate with Count Rows and All Rows, filter counts above one, and expand All Rows. (correct answer)
  4. Add an index column, remove duplicates on the index, and filter records whose index is greater than one.
Explanation: When working with duplicate detection in Power Query, your goal isn't just to find duplicates — it's to preserve all offending records for review while still identifying which composite keys are problematic. This requires a two-phase approach: aggregate to detect, then expose all original rows. The correct approach, C, uses Group By on both AccountID and PostingDate simultaneously, adding two aggregations: Count Rows (to detect duplicates) and All Rows (to nest the original records in a table column). You then filter where Count is greater than one — isolating only the duplicated composite keys — and finally expand the All Rows column to restore every original transaction belonging to those keys. Nothing is discarded; every duplicate record surfaces for investigation. A is flawed because it groups only on AccountID, ignoring PostingDate entirely. This means unique AccountID/PostingDate combinations could be incorrectly flagged, and the composite key logic breaks down entirely. B actively destroys the data you need to investigate. Removing duplicates and retaining only the first occurrence is the opposite of the requirement — you'd lose the very records that need review. D misunderstands how index columns work. An auto-generated index is unique by design, so removing duplicates on that column removes nothing meaningful. Filtering where index > 1 would simply drop most of your dataset arbitrarily, not isolate duplicated composite keys. As a study tip: whenever a question asks you to identify AND retain duplicate records in Power Query, think "Group By + All Rows + filter count > 1 + expand." That pattern is your standard toolkit for non-destructive duplicate investigation.