- You need to inspect the column using profiling statistics that include the values causing the refresh errors. What should you do?
Answer: Enable profiling over the entire data set, and then review Column quality and Column distribution for CustomerCode.
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.
- You need the appended query to contain one consistently typed identifier column. What should you do before the append operation?
Answer: Rename the identifier columns to the same name and assign the same data type in both source queries.
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.
- You need to add Segment to Orders while retaining every order, including orders without a customer match. Which transformation should you use?
Answer: Merge Orders with Customers by using a left outer join, and then expand Segment from the nested customer table.
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.
- Which transformation should you use?
Answer: Select StoreID and Region and choose Unpivot Other Columns, then rename Attribute and Value appropriately.
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.
- Which sequence best identifies and retains all records belonging to duplicated composite keys?
Answer: Group by AccountID and PostingDate with Count Rows and All Rows, filter counts above one, and expand All Rows.
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.
- You need to convert the column correctly without manually splitting and recombining each date. What should you do?
Answer: Use Change Type with Locale, select Date, and choose a locale that interprets dates as day/month/year.
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.
- Which Power Query transformation should you perform?
Answer: Pivot Metric using Value as the values column, and select Sum as the aggregation operation.
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.
- You need to reduce the number of rows retrieved from SQL while preserving the custom transformation. What should you do?
Answer: Move Filtered Rows before Invoked Custom Function, and verify that the filter step can be represented by a native query.
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.
- 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?
Answer: In the sample-file transformation, remove the bottom row before the transformation is invoked for each file.
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.
- How should you configure the queries in Power Query Editor?
Answer: Reference StagingSales twice, apply separate filters, and disable load for the StagingSales query.
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.
