Microsoft Power BI Quiz: Data Types In Power Query
10 questions · exam conditions
0:00
Data Types In Power QueryQuestion 1 of 10

A CSV file contains an OrderDate column with values such as 31/01/2026 and 04/02/2026. Power Query is running on a computer whose regional settings use month/day/year. The automatic Changed Type step produces errors for some rows and interprets other rows incorrectly.

You need to convert OrderDate to a date consistently without changing the computer's regional settings. What should you do?

Change OrderDate to Date by using locale English (United Kingdom), replacing the automatic Changed Type step.
Change OrderDate to Date by using locale English (United States), and then replace all conversion errors with null.
Keep OrderDate as Text, sort it ascending, and change it to Date after loading the model.
Split OrderDate by the slash delimiter, concatenate the parts in the same order, and apply the Date data type.
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Data Types In Power Query

Practice Data Types In Power Query 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 Data Types In Power Query, 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 CSV file contains an OrderDate column with values such as 31/01/2026 and 04/02/2026. Power Query is running on a computer whose regional settings use month/day/year. The automatic Changed Type step produces errors for some rows and interprets other rows incorrectly.

You need to convert OrderDate to a date consistently without changing the computer's regional settings. What should you do?

  1. Change OrderDate to Date by using locale English (United Kingdom), replacing the automatic Changed Type step. (correct answer)
  2. Change OrderDate to Date by using locale English (United States), and then replace all conversion errors with null.
  3. Keep OrderDate as Text, sort it ascending, and change it to Date after loading the model.
  4. Split OrderDate by the slash delimiter, concatenate the parts in the same order, and apply the Date data type.
Explanation: 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.

Question 2

A text column imported from a German accounting system contains values such as 1.234,56 and 98,25. The Power BI file is developed under English (United States) regional settings.

You need to convert the column to decimal numbers while preserving the intended values. Which transformation should you use?

  1. Replace each period with an empty string, replace each comma with a period, and then assign the Decimal Number type.
  2. Use Change Type with Locale, selecting Decimal Number and German (Germany) as the locale. (correct answer)
  3. Use Change Type with Locale, selecting Decimal Number and English (United States) as the locale.
  4. Split the column at each punctuation character, merge the resulting columns, and assign the Whole Number type.
Explanation: 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.

Question 3

A query has Region and OrderDate text columns. Rows with Region equal to US use month/day/year, while rows with Region equal to UK use day/month/year. Both regions can contain ambiguous values such as 03/04/2026.

Which approach correctly converts OrderDate without relying on whether one locale happens to produce an error?

  1. Attempt United States parsing first, and use United Kingdom parsing only when the first conversion returns an error.
  2. Assign the Date type with the current locale, and replace every conversion error with the preceding date.
  3. Select a parsing culture conditionally from Region, and use that culture with Date.FromText for each row. (correct answer)
  4. Parse all values with an invariant culture, because slash-separated dates have a culture-independent interpretation.
Explanation: 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.

Question 4

A monthly export contains a RecordType column and an Amount column. Detail rows have RecordType equal to Detail and numeric text in Amount. The final row has RecordType equal to Footer and the text Grand Total in Amount. Applying the Decimal Number type to Amount creates an error in the footer row.

You need a refreshable query containing only valid detail transactions, with Amount stored as a number. Which step order should you use?

  1. Convert Amount to Decimal Number, replace conversion errors with zero, and then remove rows whose RecordType is Footer.
  2. Filter RecordType to Detail first, and then convert Amount to Decimal Number by using the appropriate locale. (correct answer)
  3. Convert Amount to Text, filter out null Amount values, and retain the text type for the remaining detail rows.
  4. Remove the last row first, convert Amount to Decimal Number, and assume the footer always remains last.
Explanation: 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.

Question 5

An API returns Timestamp values such as 2026-05-10T23:30:00-05:00 and 2026-05-11T04:30:00Z. These two values represent the same instant. The report must normalize timestamps to UTC before deriving the reporting date.

Which Power Query transformation meets the requirement?

  1. Parse each value with DateTime.FromText, and then change the resulting column to the Date data type.
  2. Parse each value with Date.FromText, and retain the date written before the offset in the source text.
  3. Remove the offset characters from each value, parse it as Date/Time, and label the result as UTC.
  4. Parse each value with DateTimeZone.FromText, apply DateTimeZone.ToUtc, and then derive the date. (correct answer)
Explanation: 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.

Question 6

A DateText column should contain ISO-formatted dates, but occasional malformed values must be reviewed rather than silently removed. Valid rows must continue through the query, and refresh must not fail because of the malformed rows.

Which design best supports both parsing and data-quality auditing?

  1. Use try Date.FromText([DateText]), expand the result's error status and value, and separate failed rows for review. (correct answer)
  2. Change DateText directly to Date, remove all rows containing errors, and count only the remaining records.
  3. Use try Date.FromText([DateText]) otherwise null, and treat every null as a confirmed source-system error.
  4. Replace all nonnumeric characters in DateText, assign the Whole Number type, and reconstruct dates after loading.
Explanation: 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.

Question 7

A financial source supplies ExchangeRate as text with values containing no more than four digits after the decimal separator. The values will be multiplied and aggregated across millions of rows. The solution must avoid floating-point rounding artifacts and preserve all four fractional digits.

Which Power Query data type should you assign after parsing the values with the source locale?

  1. Decimal Number, because its floating-point representation preserves every four-decimal source value exactly.
  2. Text, because numeric aggregation over text avoids precision changes during query evaluation.
  3. Whole Number, because scaling and aggregation automatically restore the source's fractional component.
  4. Fixed Decimal Number, because it provides fixed precision with four digits to the right of the decimal separator. (correct answer)
Explanation: 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.

Question 8

A French-language export contains AmountText values using a comma decimal separator. Thousands are separated inconsistently by either a normal space or a nonbreaking space, and some values also have leading or trailing spaces. Direct conversion produces intermittent errors.

Which transformation is the most reliable before assigning a numeric type?

  1. Remove normal and nonbreaking spaces, trim the text, and parse the result with the French locale. (correct answer)
  2. Apply Text.Clean only to remove control characters, and then parse the result with the United States locale, which treats the comma as a list separator.
  3. Convert AmountText to Decimal Number first, and then apply Text.Trim to any rows that return conversion errors to correct whitespace issues.
  4. Replace every comma with a period, retain all space characters as thousands separators, and parse the result with the French locale.
Explanation: 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.

Question 9

A folder query combines identically structured CSV files. The generated Transform Sample File query includes an automatic Changed Type step based on the initial sample. New files can contain values that were absent from the sample, and all files use the same nondefault regional format for dates and numbers.

You need type conversion to be predictable as new files are added. What should you do?

  1. Retain the inferred Changed Type step because the sample file's inferred schema is automatically reevaluated for every row.
  2. Remove the inferred type step, combine the columns as text, and apply explicit types with locale after the files are combined. (correct answer)
  3. Convert every source column to Binary in the sample query, and allow the data model to infer types after refresh.
  4. Keep the inferred step, replace all future conversion errors with null, and document that new values can be discarded.
Explanation: 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.

Question 10

A CSV source contains ProductCode values such as 000127 and 004510. Power Query automatically creates a Changed Type step that converts ProductCode to Whole Number. A later step converts the column to Text, but the resulting values are 127 and 4510.

You need to retain the original six-character codes. What should you do?

  1. Keep the existing steps and pad the converted text to six characters by adding leading zeroes.
  2. Change the Whole Number type to Fixed Decimal Number before converting ProductCode to Text.
  3. Remove or edit the initial Changed Type step so ProductCode remains Text from the source onward. (correct answer)
  4. Convert ProductCode back to Binary and decode it as text by using the source file's encoding.
Explanation: 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.