- Which Power Query transformation meets the requirement?
Answer: Parse each value with DateTimeZone.FromText, apply DateTimeZone.ToUtc, and then derive the date.
When working with timestamps that include timezone offsets (like -05:00 or Z), your goal is to preserve the offset information long enough to convert everything to a common baseline — UTC — before extracting a date. Stripping or ignoring offsets too early produces wrong dates, which is exactly the trap this question sets.
The correct approach is D. DateTimeZone.FromText parses the full ISO 8601 string, retaining the offset as part of the value. DateTimeZone.ToUtc then shifts the value to UTC — so 2026-05-10T23:30:00-05:00 correctly becomes 2026-05-11T04:30:00Z, matching the second timestamp. Only after normalizing to UTC do you extract the reporting date, ensuring both values yield the same date: May 11, 2026.
A fails because DateTime.FromText discards the offset information entirely during parsing. The function treats the value as a "naive" local datetime, so the two timestamps would produce different dates depending on their written offset — defeating the normalization requirement.
B fails for a similar reason: Date.FromText reads only the date portion literally written before the offset character. The first timestamp would return May 10, not May 11, giving you the wrong reporting date and no UTC conversion whatsoever.
C is a particularly dangerous trap. Removing the offset characters before parsing doesn't convert anything — it simply pretends the offset doesn't exist. Labeling the result "UTC" is cosmetically misleading; the underlying values remain unconverted.
As a study tip, remember this pattern: parse with timezone awareness → convert → then extract. Any shortcut that skips the conversion step will produce silently incorrect dates, which is a classic Power BI data modeling pitfall.
- Which design best supports both parsing and data-quality auditing?
Answer: Use try Date.FromText([DateText]), expand the result's error status and value, and separate failed rows for review.
When Power BI questions involve both data transformation and data quality, you need to think about two simultaneous goals: keeping valid data flowing and preserving bad rows for investigation — without crashing the refresh. The try expression in M language is specifically designed for this pattern.
Option A is the right design because try Date.FromText([DateText]) returns a record with two fields: HasError (a boolean) and Value (the parsed date or the error record). When you expand that structured result, you get a column you can filter on — routing valid rows forward and flagging malformed rows to a separate review table or query branch. The refresh never fails because the error is caught and structured, not propagated.
Option B removes the problem rather than preserving it. Silently deleting malformed rows destroys the audit trail entirely, which directly violates the requirement to review bad data. Option C is tempting because try ... otherwise null does prevent refresh failures, but it collapses all failures into nulls — you lose the original bad value and any error detail, making true auditing impossible. You can't distinguish a missing value from a malformed one. Option D is a roundabout workaround that strips formatting to extract digits, which is fragile for ISO dates and bypasses M's built-in parsing entirely; it also produces integers, not dates, requiring extra reconstruction steps with no auditing benefit.
A useful tip: whenever an exam question mentions "auditing," "reviewing errors," or "not silently removing" bad rows, look for try with full record expansion — not otherwise null, which hides errors rather than surfacing them.
- You need to convert the column to decimal numbers while preserving the intended values. Which transformation should you use?
Answer: Use Change Type with Locale, selecting Decimal Number and German (Germany) as the locale.
Whenever you see a question about importing numeric data from non-English sources in Power BI, think about locale-aware type conversion. Different regions use different symbols for decimal separators and thousands separators — German formatting uses a period (.) as the thousands separator and a comma (,) as the decimal separator, the exact opposite of U.S. conventions. Power BI's regional settings determine how it interprets these symbols by default.
Change Type with Locale is the built-in Power Query feature designed precisely for this scenario. By selecting Decimal Number and choosing German (Germany) as the locale, you tell Power BI to interpret 1.234,56 as one thousand two hundred thirty-four point fifty-six — exactly the intended value. This makes B the correct answer.
A is a common manual workaround that seems logical but is fragile and error-prone. Removing periods first and replacing commas with periods would work on simple values, but it's brittle — it can break with edge cases, and it bypasses the clean, purpose-built solution Power Query already provides.
C applies English (United States) locale, which expects a comma as the thousands separator and a period as the decimal separator. Feeding German-formatted numbers through U.S. locale settings will either produce errors or wildly incorrect values (e.g., interpreting 1.234,56 as 1.234).
D is a destructive approach that splits and merges columns unnecessarily and converts to Whole Number, which would discard the decimal portion entirely — completely wrong for values like 1.234,56.
As a study tip: on Power BI exam questions involving regional number formats, Change Type with Locale is almost always the cleaner, preferred answer over manual string-replacement workarounds.
- You need a refreshable query containing only valid detail transactions, with Amount stored as a number. Which step order should you use?
Answer: Filter RecordType to Detail first, and then convert Amount to Decimal Number by using the appropriate locale.
When working with Power Query transformations, the order of steps matters enormously — especially when mixed data types would cause conversion errors. The key principle here is: eliminate problematic rows before applying type conversions, not after.
Filtering on RecordType = Detail first isolates only the rows you actually want — rows where Amount contains valid numeric text. Once the footer row is gone, converting Amount to Decimal Number (with the appropriate locale to handle regional decimal separators) succeeds cleanly on every remaining row. This is exactly what B prescribes, making it the correct and most robust approach.
A is tempting but flawed: replacing conversion errors with zero silently corrupts your data. A transaction with Amount = 0 is meaningfully different from a non-numeric footer row. You'd be masking data quality issues rather than solving them, and "Grand Total" doesn't represent a $0 transaction.
C is wrong because retaining Amount as Text defeats the entire goal — the question explicitly requires Amount stored as a number. Text cannot be aggregated or used in numeric calculations in your reports.
D relies on a fragile assumption: that the footer is always the last row. In a real, refreshable scenario, row position can shift. Hardcoding "remove last row" is brittle and will break if the file format ever changes, making this unsuitable for a reliable, repeatable query.
Study tip: On Power BI exam questions involving type conversions and errors, always ask yourself — "Can I remove bad rows upstream so the conversion never encounters them?" Filtering early is almost always cleaner than patching errors downstream.
- Which Power Query data type should you assign after parsing the values with the source locale?
Answer: Fixed Decimal Number, because it provides fixed precision with four digits to the right of the decimal separator.
When working with numeric precision in Power Query, the key question to ask is: does this data type store values exactly, or does it introduce approximation? This distinction becomes critical when values are multiplied and aggregated across millions of rows, where small errors compound dramatically.
Fixed Decimal Number (also called Currency type internally) stores values as a scaled 64-bit integer with exactly four decimal places. Because exchange rates in this scenario never exceed four fractional digits, every source value maps perfectly to this representation — no rounding, no floating-point drift. Aggregations remain exact because the math stays in integer arithmetic under the hood. This is why D is correct.
A is wrong because Decimal Number uses IEEE 754 double-precision floating point. While it can approximate most four-decimal values, it cannot exactly represent many of them in binary — for example, 0.1 in binary floating point is a repeating fraction. Across millions of multiplications and additions, those tiny errors accumulate into visible artifacts.
B is wrong because keeping values as Text doesn't enable numeric aggregation at all. Power BI cannot SUM or multiply text columns directly; you'd need to convert them anyway, at which point the precision question returns.
C is wrong because Whole Number discards the fractional component entirely. There is no automatic mechanism in Power Query to "restore" decimals from a Whole Number — you would lose the four fractional digits permanently unless you manually scale before and after every operation.
As a study tip: on Power BI exam questions involving precision with a known, fixed number of decimal places, Fixed Decimal Number is almost always the right answer over Decimal Number.
- You need type conversion to be predictable as new files are added. What should you do?
Answer: Remove the inferred type step, combine the columns as text, and apply explicit types with locale after the files are combined.
When working with folder queries in Power BI, the core challenge is that the Transform Sample File drives type inference for all combined files. Because that sample represents only one snapshot in time, any automatic type detection baked into it becomes a fragility point — new files with unfamiliar values or different regional formats can silently break your refresh.
The robust pattern is exactly what B describes: keep everything as text during the combination phase, then apply explicit Changed Type with Locale steps after all files are merged. This separates two concerns — file combination and type conversion — and ensures locale-specific formats (like European date or decimal conventions) are handled consistently regardless of which file happened to be the sample. You're in full control of when and how types are applied.
A is wrong because the inferred Changed Type step is not reevaluated per row or per refresh against new data. It's a static inference captured at query-creation time. New values that don't match the inferred type produce errors, not graceful re-inference.
C is wrong because converting columns to Binary doesn't preserve text values for later modeling — Binary is a raw byte format, not a text passthrough. The data model also cannot reliably infer locale-specific date/number formats, which the passage explicitly flags as a concern.
D is wrong because replacing errors with null silently discards data. That's not predictable type conversion — it's data loss dressed up as a workaround, and it violates the requirement that new values be handled correctly.
A useful pattern to remember: combine as text, type after merge with explicit locale is the professional standard whenever folder queries span regional formats.
- Which approach correctly converts OrderDate without relying on whether one locale happens to produce an error?
Answer: Select a parsing culture conditionally from Region, and use that culture with Date.FromText for each row.
When working with date parsing in Power BI, the critical concept is locale-aware conversion. Whenever date strings have ambiguous formats that depend on regional conventions, you must match the parsing logic to the source of each value — not assume a single locale works globally.
The only reliable approach here is C: conditionally selecting a culture string (like "en-US" or "en-GB") based on the Region column, then passing that culture into Date.FromText. This means 03/04/2026 is correctly read as March 4th for US rows and April 3rd for UK rows — every time, deterministically, regardless of the host machine's locale settings.
Option A is dangerously fragile. It only triggers UK parsing when US parsing fails. But ambiguous values like 03/04/2026 are valid in both locales — no error is thrown, so the fallback never fires. US parsing silently wins for all rows, corrupting UK dates.
Option B compounds the problem further. Assigning a type with the current locale applies one regional rule uniformly across all rows, meaning half your dates will be misinterpreted. Replacing errors with the preceding date doesn't fix semantic misinterpretation — it just masks structural failures.
Option D is a misconception. Slash-separated dates (MM/DD/YYYY vs DD/MM/YYYY) are inherently culture-dependent. There is no culture-independent interpretation for them, so invariant culture parsing will either fail or produce wrong results.
Study tip: On Power BI exam questions involving mixed-locale date columns, always look for the answer that applies conditional, row-level culture logic — that's the signal of a correct, deterministic solution.
- You need to retain the original six-character codes. What should you do?
Answer: Remove or edit the initial Changed Type step so ProductCode remains Text from the source onward.
When working with Power Query, understanding the order of applied steps is critical. Each step transforms the output of the previous one — so if an early step destroys information, no later step can recover it.
Here, Power Query's automatic "Changed Type" step converts 000127 to the integer 127, permanently stripping the leading zeroes. Once that numeric conversion happens, the original string format is gone. A subsequent Text conversion simply turns 127 back into the string "127" — you cannot recover what was never preserved.
The fix is option C: remove or edit the initial Changed Type step so ProductCode is never converted to Whole Number. If the column stays as Text throughout, 000127 remains 000127 at every subsequent step. This is the only approach that preserves the original data without fabricating information.
Option A is tempting but fundamentally wrong — padding "127" with leading zeroes produces "000127" coincidentally here, but this approach is fragile. If a code like 001200 became 1200, padding would incorrectly produce 001200 rather than 001200... wait — it would work only if all codes are uniformly six digits. But you're reconstructing data rather than preserving it, which is poor practice and error-prone.
Option B is incorrect because Fixed Decimal Number is still a numeric type — it would store 127.00, not 000127. Converting that to Text yields "127", not the original code.
Option D is a red herring. Binary encoding has nothing to do with leading zeroes being stripped by a type conversion.
The key study tip: always check Power Query's auto-generated steps when importing CSVs. Power BI frequently guesses column types incorrectly — review and adjust the Changed Type step before building anything downstream.
- You need to convert OrderDate to a date consistently without changing the computer's regional settings. What should you do?
Answer: Change OrderDate to Date by using locale English (United Kingdom), replacing the automatic Changed Type step.
When Power Query's automatic Changed Type step runs on a computer with US regional settings (MM/DD/YYYY), it tries to parse 31/01/2026 as month 31 — which is invalid — causing errors, while something like 04/02/2026 gets silently misread as April 2nd instead of February 4th. The root concept being tested here is locale-aware type conversion in Power Query.
The fix is to explicitly tell Power Query which locale the data was created in, regardless of the machine's settings. Using Change Type → Using Locale → English (United Kingdom) instructs Power Query to interpret the values as DD/MM/YYYY — matching the actual format in the file. This replaces the faulty automatic step and correctly parses every row. That makes A the right answer.
B is dangerous: using English (United States) still misinterprets the date format, and replacing errors with null simply hides the problem rather than fixing it. You'd end up with wrong dates and missing values — two bugs instead of one.
C is impractical and incorrect. Sorting text like "31/01/2026" alphabetically doesn't make it parse correctly later. Text-to-date conversion still depends on locale interpretation, and deferring the problem to the model doesn't solve it.
D is unnecessarily complex. Splitting and recombining the same parts in the same order produces an identical string — it changes nothing about how Power Query interprets it as a date.
Study tip: On Power BI exam questions involving date parsing errors, always ask yourself: does the data's locale match the machine's locale? If not, "Change Type with Locale" is your go-to tool.
- Which transformation is the most reliable before assigning a numeric type?
Answer: Remove normal and nonbreaking spaces, trim the text, and parse the result with the French locale.
When dealing with international numeric formats in Power Query, your goal is to neutralize every formatting inconsistency before type conversion — not after. Think of it as cleaning the raw string so the parser receives exactly what it expects.
The reliable approach here is answer A: strip both normal spaces (U+0020) and nonbreaking spaces (U+00A0) used as thousands separators, trim leading and trailing whitespace, and then parse using the French locale. French locale correctly interprets the comma as a decimal separator, so once all spacing noise is removed, conversion succeeds consistently.
B fails for two reasons: Text.Clean only removes control characters — it does nothing about spaces or nonbreaking spaces. Worse, parsing with the United States locale treats a comma as a list separator, not a decimal point, so values like "1 234,56" would error or misparse entirely.
C has the logic backwards. You cannot convert to Decimal Number first and then trim — the conversion itself is what fails when whitespace is present. Applying Text.Trim after errors have already occurred doesn't retroactively fix the source values; it only trims the error messages, which is useless.
D replaces commas with periods (turning the decimal separator into a period), but then tries to parse with French locale, which still expects a comma as the decimal marker. You've now created a mismatch. Retaining space characters also reintroduces the original thousands-separator problem.
Study tip: On Power BI exam questions involving locale-aware parsing, always match your cleaning step to the exact characters causing errors, and confirm your locale setting aligns with the decimal separator in the source data — these two things must be consistent.
