- Which M expression should you use?
Answer: List.Contains({"A1", "B2"}, Text.Upper(Text.Trim([Code])))
When working with text matching in Power Query M, you need to think about three things simultaneously: trimming whitespace, normalizing case, and using the right membership-check function.
The goal here is to check whether a cleaned-up version of [Code] exists inside a known list. The correct approach is A: List.Contains({"A1", "B2"}, Text.Upper(Text.Trim([Code]))). This works because Text.Trim removes leading and trailing spaces (turning a1 into a1), then Text.Upper converts the result to uppercase (a1 → A1), and finally List.Contains checks whether that normalized value exists in {"A1", "B2"}. The result is a true logical (Boolean) value, exactly what a custom logical column requires.
B is tempting but flawed — it passes Comparer.Ordinal as the third argument to List.Contains, which performs a case-*sensitive* comparison. Since it doesn't apply Text.Upper or Text.Lower, lowercase inputs like a1 or c3 would never match the uppercase list entries "A1" and "B2".
C misuses Text.Contains, which checks whether one string contains another as a substring. Searching for "A1" inside the string "A1,B2" would technically return true, but "B2" would also match inside "A1,B2" unreliably — and this approach is semantically wrong for membership testing.
D uses Text.Lower instead of Text.Upper, converting the code to lowercase before comparing it against {"A1", "B2"}, which are uppercase. The case mismatch means no value will ever match.
A useful tip: whenever you need case-insensitive list membership in M, the reliable pattern is always List.Contains(list, Text.Upper(Text.Trim(value))), normalizing both the list and the input to the same case.
- Which custom column expression produces the required key?
Answer: Text.From(Date.Year([OrderDate])) & "-" & Text.PadStart(Text.From(Date.Month([OrderDate])), 2, "0")
When building date keys in Power Query's M language, you need two things: correct text formatting and proper zero-padding. Years are always four digits, so they don't need padding — but months like February return 2 as a number, which must become "02" to satisfy the YYYY-MM format requirement. The function Text.PadStart(text, totalLength, padCharacter) handles this by left-padding a string until it reaches the specified length.
Option C correctly applies this logic: Text.From(Date.Year([OrderDate])) produces the four-digit year, then Text.PadStart(Text.From(Date.Month([OrderDate])), 2, "0") ensures the month is always two characters wide — so "2" becomes "02". Combined with the "-" separator, you get the required 2026-02 format.
Option A fails because it applies no padding at all. Text.From(Date.Month([OrderDate])) on February returns "2", producing "2026-2" instead of "2026-02". Option B pads the year to four digits instead of the month — years are already four digits, so the padding does nothing useful, and the month still comes out unpadded. Option D gets both the padding target right (month) and the padding itself correct, but then reverses the order, placing the month before the year and producing "02-2026" instead of "2026-02".
A useful rule of thumb: when you see a date-key formatting question, immediately ask yourself which component needs padding (almost always the month or day, never the year) and verify that the concatenation order matches the target format exactly.
- Which M expression computes 15% of [Amount]?
Answer: [Amount] * 0.15
Fifteen percent means 0.15, so multiplying the column [Amount] by 0.15 gives exactly 15% of it. The tempting wrong answer is Amount (15 / 100): 15/100 equals 0.15, but in M a column reference must include brackets, so Amount alone is not valid. Therefore use [Amount] 0.15.
- Which M expression defines D as 0.1 and multiplies [S] by D?
Answer: let D = 0.1 in [S] * D
In M, a variable is introduced with let and used in an in clause, so let D = 0.1 in [S] D defines D as 0.1 and evaluates the multiplication. The tempting version D = 0.1; [S] D looks like a sequence, but M requires the let...in structure, not a semicolon at the top level. That is why only the let form works.
- Which M expression returns total hours between [A] and [B]?
Answer: Duration.TotalHours([B] - [A])
The expression [B] - [A] produces a duration value, and Duration.TotalHours converts that entire duration into total hours, including fractional hours. Duration.Hours would return only the hour component, ignoring days and minutes, so it is not the total. Use Duration.TotalHours([B] - [A]).
- Which custom column expression meets the requirement for all rows?
Answer: Duration.Days(Date.From([ShippedAt]) - Date.From([OrderedAt]))
When calculating the number of calendar days between two datetime values in Power Query, you need to strip away the time component before measuring the gap — otherwise, partial days distort your result. This question tests whether you understand the difference between elapsed duration and calendar-date distance.
The correct approach, D, uses Date.From() to convert both datetimes to pure dates first (2026-01-31 and 2026-02-02), then subtracts them to get a Duration, and finally wraps that in Duration.Days(). The result is exactly 2 — matching the two date boundaries crossed, regardless of what time the events occurred.
A is the most tempting trap. Duration.Days([ShippedAt] - [OrderedAt]) subtracts the raw datetimes, giving a duration of roughly 1 day and 1.5 hours. Duration.Days() returns only the whole-day integer portion of that duration, which is 1, not 2. The time components bleed into the calculation.
B tries a clever shortcut — subtracting the day-of-month numbers — but this breaks badly across month and year boundaries. For your example, 2 - 31 = -29, which is completely wrong.
C converts the duration to total hours and divides by 24, giving approximately 1.52. This is a fractional result, not the integer 2 the business requires, and it still doesn't strip time components.
D is correct by converting to dates before subtracting, ensuring only calendar days are counted.
Study tip: Whenever a business rule says "ignore times" or "count date boundaries," your first instinct in Power Query should be Date.From() — strip the time before you do any date math.
- Which complete M step should you add?
Answer: Table.AddColumn(Source, "Margin", each [Revenue] - [Cost], type number)
When working with Table.AddColumn in Power Query M, you need to understand three key arguments after the source table: the new column name, the transformation logic, and the output type. The transformation logic is where most mistakes happen — M requires a function that runs once per row, not a direct expression.
The each keyword is M's shorthand for creating a row-level function. Writing each [Revenue] - [Cost] means "for each row, subtract that row's Cost from its Revenue," which is exactly what the question asks for. Combined with type number (which correctly represents decimal numbers in M), option B is the complete, valid step.
Option A is the most tempting trap. It drops the each keyword, passing [Revenue] - [Cost] as a bare expression rather than a function. M will throw an error because the column reference has no row context without each — the engine doesn't know which row's values to use. Option C uses the correct row logic with each [Revenue] - [Cost], but specifies type text as the output type. Since you're performing arithmetic and the result must be a decimal number, type text is semantically wrong and could cause downstream type errors. Option D replaces subtraction with the & operator, which is M's text concatenation operator — using it on number columns would fail entirely, and the result wouldn't represent margin anyway.
A quick tip to remember: whenever you reference column values inside Table.AddColumn, always use each to establish row context. Think of each as the gateway that makes column references meaningful row by row.
- Which M expression should you use?
Answer: if [Actual] = null then [Forecast] else [Actual]
When working with null checks in Power Query's M language, the most important thing to understand is that null comparisons require explicit null syntax — M treats null as the absence of a value, not as zero or any other placeholder.
The requirement here is straightforward: use Forecast only when Actual is null, and preserve Actual in all other cases — including when it legitimately equals zero. That makes D the correct choice. if [Actual] = null then [Forecast] else [Actual] directly checks for null and returns Forecast only in that case, leaving any non-null value of Actual (including zero) untouched.
Choice A is the classic trap in this question. By checking if [Actual] = 0, you'd incorrectly substitute Forecast whenever Actual is zero — but the problem explicitly states zero is a legitimate actual value that should be preserved. This conflates "missing" with "zero," which are semantically different.
Choice B flips the logic incorrectly. if [Actual] <> null checks whether Actual is not null, and if true, returns Forecast — the exact opposite of what you want. Even if the intent were right, this would replace all valid Actual values with Forecast.
Choice C uses try...otherwise, which is designed to handle errors, not null values. If [Actual] is null, no error is thrown — the expression simply evaluates to null — so Forecast would never substitute in.
As a study tip: on Power BI exam questions involving nulls, always distinguish between null (missing), zero (a real value), and errors. Each requires a different M construct.
- Which M expression implements the rule?
Answer: if ([Region] = "West" and [Revenue] >= 100000) or [Strategic] = true then "Priority" else "Standard"
When translating a business rule into a logical expression, your first job is to identify the grouping the rule implies — because and/or operator precedence can completely change the meaning of a condition.
The rule has two independent paths to "Priority": (1) West region AND revenue ≥ 100,000, or (2) Strategic account, regardless of anything else. That structure is: (Condition A AND Condition B) OR Condition C. Answer B captures this exactly — ([Region] = "West" and [Revenue] >= 100000) or [Strategic] = true — making the two West/Revenue conditions a single unit joined by or to the Strategic check. The explicit parentheses enforce the intended grouping.
Answer A misplaces the parentheses entirely. It reads: West AND (Revenue ≥ 100,000 OR Strategic), meaning a strategic account still needs to be in the West region to qualify — that directly violates the "regardless of region" requirement.
Answer C requires Strategic to be true in all cases because it reads (West OR Revenue ≥ 100,000) AND Strategic. A West account with high revenue would still fail if Strategic is false, which is wrong.
Answer D groups Revenue with Strategic: West OR (Revenue ≥ 100,000 AND Strategic). This incorrectly allows any West account — even with low revenue and non-strategic status — to qualify, and ties revenue to the strategic flag in a way the rule never intended.
A useful habit: before writing M code, sketch the rule as a boolean expression with explicit parentheses, then translate it literally. In Power Query, and binds tighter than or by default, so always parenthesize compound conditions to make precedence explicit and avoid subtle bugs.
- Which M expression returns "H" when [N] > 10, else "L"?
Answer: if [N] > 10 then "H" else "L"
In M, the conditional operator is if ... then ... else, so you write if [N] > 10 then "H" else "L". The tempting wrong option uses IF(...) like Excel or DAX, but M has no IF function: it's a keyword-based expression. The colon version and Text.If are not valid M syntax.
- Which M expression should you use?
Answer: Number.FromText(Text.Split([SKU], "-"){1})
When working with structured text in Power Query M, you'll often need to split a delimited string and extract a specific segment. The key functions here are Text.Split(), which returns a zero-indexed list, and Number.FromText(), which converts the extracted text to a numeric value.
For a value like EU-104-Blue, calling Text.Split([SKU], "-") produces the list {"EU", "104", "Blue"}. Because M uses zero-based indexing, position {0} is "EU", position {1} is "104", and position {2} is "Blue". To grab the middle segment "104" and convert it to a number, you need Number.FromText(Text.Split([SKU], "-"){1}) — which is exactly what A does.
B is wrong because {0} retrieves the first element, "EU", which is not numeric and would cause an error when passed to Number.FromText(). C is wrong because {2} retrieves the last element, "Blue", which is also non-numeric — a similar trap. D uses List.Last(), which always returns the final element of the list ("Blue"), not the middle one, so it fails both logically and numerically.
A reliable study tip: whenever you see Text.Split() in Power Query, immediately think zero-based list indexing. Sketch out the resulting list mentally and count from zero. This habit will save you from the classic off-by-one mistake that options B and C are designed to exploit on this exam.
- Which M expression uses 0 when [R] errors, else [R]?
Answer: try [R] otherwise 0
In M, try [R] otherwise 0 catches any error in [R] and returns 0 instead, otherwise it returns [R]. That's exactly the behavior needed. The tempting wrong answer is "if [R] error then 0 else [R]" because M doesn't use if error syntax; error handling uses try otherwise.
- Which M expression should you use?
Answer: Number.FromText([AmountText], "de-DE")
When working with numeric text conversion in Power Query M, the key concept being tested is locale-aware parsing. Different countries format numbers differently — what looks like a thousand separator in one culture may be a decimal separator in another. The Number.FromText() function accepts an optional culture parameter that tells M how to interpret these formatting conventions.
German formatting (as used in Germany, Austria, and parts of Switzerland) uses a period as the thousands separator and a comma as the decimal separator — the exact opposite of English conventions. So 1.234,50 in German means one thousand, two hundred thirty-four and a half (1234.5). Passing "de-DE" as the culture parameter instructs M to parse the string using German rules, correctly returning 1234.5. This makes B the right answer.
A is wrong because "en-US" uses a comma as the thousands separator and a period as the decimal — it would misinterpret 1.234,50 and likely return an error or incorrect value, since a comma mid-number doesn't match English decimal formatting.
C ("fr-FR") uses a space as the thousands separator and a comma as the decimal, so it doesn't match the German dot-comma format and would also fail to parse correctly.
D ("en-GB") follows the same conventions as "en-US" for number formatting, so it shares the same mismatch problem as option A.
A useful study tip: whenever a question involves parsing numbers from a non-English source, immediately look for the locale code that matches the source country's formatting conventions — the culture parameter must match where the data came from, not where you're working.
- Which M expression meets all requirements?
Answer: Record.FieldOrDefault([Attributes], "Tier", null) ?? "Unknown"
When working with M (Power Query) record functions, you need to think carefully about two distinct failure modes: a missing field and a field that exists but holds null. The null-coalescing operator ?? only handles nulls — it cannot recover from a missing field that throws an error.
Record.FieldOrDefault([Attributes], "Tier", "Unknown") — option A — handles missing fields gracefully by returning the default, but if the field exists and contains null, it returns null rather than "Unknown". It silently passes null through, violating the requirement.
Option B uses Record.Field, which throws an error when the field is missing entirely. The ?? operator cannot catch errors — only nulls — so this expression crashes on records where Tier doesn't exist at all.
Option D wraps the expression in try...otherwise, which does handle errors from missing fields, but it returns null rather than "Unknown", so it still fails the requirement when the field is absent or null.
Option C is correct because it combines both defenses. Record.FieldOrDefault([Attributes], "Tier", null) safely returns null whether the field is missing or contains null — converting both failure cases into a consistent null. Then ?? "Unknown" catches that null and substitutes the desired fallback. This two-step pattern cleanly collapses both edge cases into one outcome.
Study tip: On Power BI exam questions involving record lookups, always ask yourself: "What happens when the field is missing versus when it's null?" These are separate problems requiring separate tools — FieldOrDefault for missing fields, ?? for nulls — and the correct answer usually needs both.
