Microsoft Power BI Quiz: Custom Columns In M
14 questions · exam conditions
0:00
Custom Columns In MQuestion 1 of 14

Which M expression defines D as 0.1 and multiplies [S] by D?

D = 0.1; [S] * D
let D = 0.1 in [S] * D
with D = 0.1 return [S] * D
(D = 0.1) [S] * D
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Custom Columns In M

Practice Custom Columns In M 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 Custom Columns In M, 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

Which M expression defines D as 0.1 and multiplies [S] by D?

  1. D = 0.1; [S] * D
  2. let D = 0.1 in [S] * D (correct answer)
  3. with D = 0.1 return [S] * D
  4. (D = 0.1) [S] * D
Explanation: 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.

Question 2

Which M expression uses 0 when [R] errors, else [R]?

  1. if [R] error then 0 else [R]
  2. Try.CatchError([R], 0)
  3. if Error([R]) then 0 else [R]
  4. try [R] otherwise 0 (correct answer)
Explanation: 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.

Question 3

Which M expression returns "H" when [N] > 10, else "L"?

  1. if [N] > 10 then "H" else "L" (correct answer)
  2. IF([N] > 10, "H", "L")
  3. Text.If([N] > 10, "H", "L")
  4. if [N] > 10: "H" else "L"
Explanation: 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.

Question 4

Which M expression computes 15% of [Amount]?

  1. [Amount] * 0.15 (correct answer)
  2. Amount * (15 / 100)
  3. [Amount] / 0.15
  4. Percent([Amount], 15)
Explanation: 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.

Question 5

Which M expression returns total hours between [A] and [B]?

  1. TotalHours([B] - [A])
  2. ([B] - [A]).TotalHours
  3. Duration.TotalHours([B] - [A]) (correct answer)
  4. Duration.Hours([B] - [A])
Explanation: 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]).

Question 6

A text column named Code can contain values such as a1, B2, and c3. You must create a custom logical column that returns true only for codes A1 or B2. Matching must ignore leading or trailing spaces and letter case.

Which M expression should you use?

  1. List.Contains({"A1", "B2"}, Text.Upper(Text.Trim([Code]))) (correct answer)
  2. List.Contains({"A1", "B2"}, Text.Trim([Code]), Comparer.Ordinal)
  3. Text.Contains("A1,B2", Text.Upper(Text.Trim([Code])))
  4. List.Contains({"A1", "B2"}, Text.Lower(Text.Trim([Code])))
Explanation: 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.

Question 7

A query contains a date column named OrderDate. You must create a text key in YYYY-MM format. For example, a date in February 2026 must produce 2026-02, not 2026-2.

Which custom column expression produces the required key?

  1. Text.From(Date.Year([OrderDate])) & "-" & Text.From(Date.Month([OrderDate]))
  2. Text.PadStart(Text.From(Date.Year([OrderDate])), 4, "0") & "-" & Text.From(Date.Month([OrderDate]))
  3. Text.From(Date.Year([OrderDate])) & "-" & Text.PadStart(Text.From(Date.Month([OrderDate])), 2, "0") (correct answer)
  4. Text.PadStart(Text.From(Date.Month([OrderDate])), 2, "0") & "-" & Text.From(Date.Year([OrderDate]))
Explanation: 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.

Question 8

A query contains numeric columns named Actual and Forecast. The Actual value can be null when no actual result has been reported. It can also legitimately be zero. You must create a custom column that uses Forecast only when Actual is null.

Which M expression should you use?

  1. if [Actual] = 0 then [Forecast] else [Actual]
  2. if [Actual] <> null then [Forecast] else [Actual]
  3. try [Actual] otherwise [Forecast]
  4. if [Actual] = null then [Forecast] else [Actual] (correct answer)
Explanation: 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.

Question 9

In the Advanced Editor, the previous step is named Source. It contains decimal-number columns named Revenue and Cost. You must add a decimal-number column named Margin by subtracting the current row's cost from its revenue.

Which complete M step should you add?

  1. Table.AddColumn(Source, "Margin", [Revenue] - [Cost], type number)
  2. Table.AddColumn(Source, "Margin", each [Revenue] - [Cost], type number) (correct answer)
  3. Table.AddColumn(Source, "Margin", each [Revenue] - [Cost], type text)
  4. Table.AddColumn(Source, "Margin", each [Revenue] & [Cost], type number)
Explanation: 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.

Question 10

You must create a custom column named Priority. A row is priority when either of the following is true: the region is West and revenue is at least 100000, or the account is strategic regardless of region. The Strategic column contains non-null logical values.

Which M expression implements the rule?

  1. if [Region] = "West" and ([Revenue] >= 100000 or [Strategic] = true) then "Priority" else "Standard"
  2. if ([Region] = "West" and [Revenue] >= 100000) or [Strategic] = true then "Priority" else "Standard" (correct answer)
  3. if ([Region] = "West" or [Revenue] >= 100000) and [Strategic] = true then "Priority" else "Standard"
  4. if [Region] = "West" or ([Revenue] >= 100000 and [Strategic] = true) then "Priority" else "Standard"
Explanation: 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.

Question 11

A text column named SKU contains values in the consistent format Region-ProductID-Variant, such as EU-104-Blue. You must create a numeric custom column containing the middle segment, so the example returns the number 104.

Which M expression should you use?

  1. Number.FromText(Text.Split([SKU], "-"){1}) (correct answer)
  2. Number.FromText(Text.Split([SKU], "-"){0})
  3. Number.FromText(Text.Split([SKU], "-"){2})
  4. Number.FromText(List.Last(Text.Split([SKU], "-")))
Explanation: 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.

Question 12

A Power Query column named AmountText contains German-formatted values such as 1.234,50. You must create a custom numeric column that converts this example to a value of 1234.5 without first replacing individual characters.

Which M expression should you use?

  1. Number.FromText([AmountText], "en-US")
  2. Number.FromText([AmountText], "de-DE") (correct answer)
  3. Number.FromText([AmountText], "fr-FR")
  4. Number.FromText([AmountText], "en-GB")
Explanation: 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.

Question 13

A query contains datetime columns named OrderedAt and ShippedAt. For an order placed at 2026-01-31 23:30 and shipped at 2026-02-02 01:00, a new column must return 2 because the business counts calendar-date boundaries and ignores times.

Which custom column expression meets the requirement for all rows?

  1. Duration.Days([ShippedAt] - [OrderedAt])
  2. Date.Day(Date.From([ShippedAt])) - Date.Day(Date.From([OrderedAt]))
  3. Duration.Hours([ShippedAt] - [OrderedAt]) / 24
  4. Duration.Days(Date.From([ShippedAt]) - Date.From([OrderedAt])) (correct answer)
Explanation: 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.

Question 14

A column named Attributes contains a record in every row. Some records contain a text field named Tier, some omit the field, and some contain Tier = null. A custom column must return the tier text when present and non-null; otherwise, it must return Unknown.

Which M expression meets all requirements?

  1. Record.FieldOrDefault([Attributes], "Tier", "Unknown")
  2. Record.Field([Attributes], "Tier") ?? "Unknown"
  3. Record.FieldOrDefault([Attributes], "Tier", null) ?? "Unknown" (correct answer)
  4. try Record.Field([Attributes], "Tier") otherwise null
Explanation: 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.