You have a table with a "Category" column containing values: "Fruit", "fruit", "FRUIT", and "Vegetable". You want to replace all variations of "fruit" (case insensitive) with "Produce" using Power Query. Which approach correctly achieves this?
AUse Table.ReplaceValue with Replacer.ReplaceText and set case insensitive option
BAdd a custom column with Text.Lower and replace "fruit" with "Produce" there
CUse Table.ReplaceValue with Replacer.ReplaceText multiple times for each case variant
DUse Table.ReplaceValue with Replacer.ReplaceValue ignoring case automatically
Step-by-Step Solution
Solution:
Step 1: Understand case sensitivity of ReplaceValue
Table.ReplaceValue with Replacer.ReplaceText is case sensitive and does not have a built-in case insensitive option.
Step 2: Replace all case variants
Adding a custom column with Text.Lower converts all values to lowercase, allowing a single replace of "fruit" with "Produce" in that column.
Step 3: Evaluate other options
Using multiple ReplaceValue calls is inefficient and error-prone. No case insensitive flag exists for ReplaceValue. ReplaceValue does not ignore case automatically.
Final Answer:
Add a custom column with Text.Lower and replace "fruit" with "Produce" there -> Option B
Quick Check:
Use Text.Lower in custom column for case insensitive replace [OK]
Quick Trick:Use Text.Lower in custom column for case insensitive replace [OK]
Common Mistakes:
Expecting case insensitive replace by default
Trying to replace all cases in one call
Using wrong replacer or ignoring case
Master "Power Query Editor" in Power BI
9 interactive learning modes - each teaches the same concept differently
