Replace values in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Replace values in a column using DAX

You have a column 'Status' with values 'Pending', 'In Progress', and 'Completed'. You want to create a new calculated column that replaces 'Pending' with 'Not Started' and keeps other values unchanged. Which DAX expression produces the correct result?

A NewStatus = SUBSTITUTE(Table[Status], "Pending", "Not Started")

B NewStatus = SWITCH(Table[Status], "Pending", "Not Started", Table[Status])

C NewStatus = REPLACE(Table[Status], 1, 7, "Not Started")

D NewStatus = IF(Table[Status] = "Pending", "Not Started", Table[Status])

💡 Hint

Use a conditional function that checks the value and replaces only when it matches.

Option D uses IF to check if the value is 'Pending' and replaces it with 'Not Started'. Other values remain unchanged.

Option D also works because the last argument in SWITCH acts as the default for unmatched cases, but IF is more straightforward for a single replacement.

Option D tries to replace characters by position, which is not suitable here.

Option D replaces substring occurrences, which could cause unintended replacements if 'Pending' appears inside other words.

❓ visualization

intermediate

Visualizing replaced values in a report

You replaced 'Pending' with 'Not Started' in your data model. You want to create a bar chart showing counts of each status including the replaced value. Which visualization setup is best practice?

A Use a slicer to filter only 'Pending' values and show counts.

B Use the original 'Status' column on the axis and count of rows as values.

C Use the new calculated column 'NewStatus' on the axis and count of rows as values.

D Use a pie chart with original 'Status' column and replace labels manually.

💡 Hint

Use the column that contains the replaced values for accurate visualization.

Option C is correct because it uses the new column with replaced values, so the chart reflects the updated categories.

Option C shows original values, so 'Pending' will appear instead of 'Not Started'.

Option C filters only 'Pending', ignoring other statuses.

Option C requires manual label changes, which is error-prone and not dynamic.

❓ data_modeling

advanced

Replacing multiple values in a column efficiently

You want to replace multiple values in a 'Category' column: 'A' → 'Alpha', 'B' → 'Beta', 'C' → 'Gamma'. Which DAX expression correctly creates a new column with these replacements and keeps other values unchanged?

A NewCategory = SWITCH(Table[Category], "A", "Alpha", "B", "Beta", "C", "Gamma", Table[Category])

B NewCategory = REPLACE(Table[Category], 1, 1, "Alpha")

C NewCategory = IF(Table[Category] = "A", "Alpha", IF(Table[Category] = "B", "Beta", IF(Table[Category] = "C", "Gamma", Table[Category])))

D NewCategory = SUBSTITUTE(Table[Category], "A", "Alpha")

💡 Hint

Use a function that handles multiple conditions with a default fallback.

Option A uses SWITCH with multiple replacements and a default fallback to original value, which is efficient and clear.

Option A is correct logically but more complex and nested; however, it also works but is less efficient.

Option A replaces characters by position, which is incorrect for categorical replacements.

Option A replaces substring 'A' everywhere, which can cause wrong replacements.

🔧 Formula Fix

advanced

Identify the error in this value replacement DAX

This DAX expression aims to replace 'Old' with 'New' in 'Status' column but causes an error. What is the error?

Power BI

NewStatus = IF(Table[Status] == "Old", "New", Table[Status])

A Runtime error: Table[Status] column not found.

B SyntaxError: '==' is invalid in DAX; use single '=' for comparison.

C No error; expression works correctly.

D TypeError: Cannot compare text with '==' operator.

💡 Hint

Check the operator used for comparison in DAX.

DAX uses a single '=' for equality comparison, not '=='. Using '==' causes a syntax error.

Option B is incorrect because '==' is a syntax error, not a type error.

Option B is wrong; the expression does not work.

Option B is unrelated to the operator used.

🧠 Conceptual

expert

Best practice for replacing values in large datasets

You have a large dataset with millions of rows. You need to replace several values in a column. Which approach is best for performance and maintainability?

A Create a separate mapping table with old and new values and use a relationship to replace values via LOOKUPVALUE.

B Use nested IF statements in a calculated column to replace values directly.

C Use REPLACE function to substitute values by character position in the column.

D Manually edit the source data file to replace values before loading.

💡 Hint

Consider scalability and ease of updates when choosing replacement methods.

Option A is best because a mapping table allows easy updates and efficient lookups without complex nested logic.

Option A becomes hard to maintain and slow with many replacements.

Option A is not suitable for categorical replacements.

Option A is error-prone and not scalable for ongoing data refreshes.

Practice

1. What is the main purpose of using Replace Values in Power BI's Power Query?

easy

A. To clean or standardize data by changing specific values

B. To create new calculated columns

C. To filter rows based on conditions

D. To merge two tables together

Solution

1. Step 1: Understand Replace Values function

   Replace Values is used to change specific values in a column to clean or standardize data.

2. Step 2: Compare with other options

   Creating columns, filtering rows, or merging tables are different operations not related to Replace Values.

3. Final Answer:

   To clean or standardize data by changing specific values -> Option A

4. Quick Check:

   Replace Values = Clean or standardize data [OK]

Hint: Replace Values changes data entries to fix or standardize them [OK]

Common Mistakes:

• Confusing Replace Values with creating new columns
• Thinking Replace Values filters data
• Mixing Replace Values with merging tables

2. Which of the following is the correct syntax to replace all occurrences of "old" with "new" in the column "Status" using Table.ReplaceValue in Power Query M language?

easy

A. Table.ReplaceValue(Source, "old", "new", Replacer.ReplaceText)

B. Table.ReplaceValue(Source, "old", "new", Replacer.ReplaceValue, {"Status"})

C. Table.ReplaceValue(Source, "old", "new", Replacer.ReplaceText, "Status")

D. Table.ReplaceValue(Source, "old", "new", Replacer.ReplaceText, {"Status"})

Solution

1. Step 1: Identify correct replacer and column list

   The correct replacer for text replacement is Replacer.ReplaceText and the columns must be passed as a list, e.g., {"Status"}.

2. Step 2: Check syntax correctness

   Table.ReplaceValue(Source, "old", "new", Replacer.ReplaceText, {"Status"}) uses correct replacer and column list syntax. Options B, C, and D have wrong replacer or column argument format.

3. Final Answer:

   Table.ReplaceValue(Source, "old", "new", Replacer.ReplaceText, {"Status"}) -> Option D

4. Quick Check:

   Correct replacer and column list = Table.ReplaceValue(Source, "old", "new", Replacer.ReplaceText, {"Status"}) [OK]

Hint: Use Replacer.ReplaceText and column list in curly braces [OK]

Common Mistakes:

• Using wrong replacer like Replacer.ReplaceValue
• Passing column name as string instead of list
• Omitting replacer argument

3. Given the following Power Query M code snippet:

let
  Source = Table.FromRecords({[Name="John", Age=30], [Name="john", Age=25]}),
  Replaced = Table.ReplaceValue(Source, "john", "Jonathan", Replacer.ReplaceText, {"Name"})
in
  Replaced

What will be the value in the "Name" column for the second row after replacement?

medium

A. john

B. John

C. Jonathan

D. Error

Solution

1. Step 1: Understand case sensitivity in ReplaceValue

   Table.ReplaceValue with Replacer.ReplaceText is case sensitive by default, so "john" (lowercase) matches only exact lowercase.

2. Step 2: Check the second row value

   The second row has "john" lowercase, so it matches and should be replaced with "Jonathan". But the first row "John" with uppercase J is not replaced.

3. Final Answer:

   Jonathan -> Option C

4. Quick Check:

   Case sensitive replace changes "john" to "Jonathan" [OK]

Hint: ReplaceValue is case sensitive by default [OK]

Common Mistakes:

• Assuming replace is case insensitive
• Expecting "John" to be replaced
• Confusing row order

4. You wrote this Power Query M code to replace "N/A" with null in the "Score" column:

Table.ReplaceValue(Source, "N/A", null, Replacer.ReplaceText, {"Score"})

But it throws an error. What is the likely cause?

medium

A. Using Replacer.ReplaceText instead of Replacer.ReplaceValue for text replacement

B. Replacing with null is not allowed in Power Query

C. The column list should be a string, not a list

D. The source table is missing

Solution

1. Step 1: Check replacer type for text replacement

   Replacing text values with null requires Replacer.ReplaceValue, not Replacer.ReplaceText.

2. Step 2: Understand null replacement

   Replacing with null is valid, but replacer must match data type operation. Using wrong replacer causes error.

3. Final Answer:

   Using Replacer.ReplaceText instead of Replacer.ReplaceValue for text replacement -> Option A

4. Quick Check:

   Correct replacer needed for text replace [OK]

Hint: Use Replacer.ReplaceValue for null replacement, not ReplaceText [OK]

Common Mistakes:

• Using wrong replacer for text replacement
• Thinking null replacement is invalid
• Passing column name as string instead of list

5. 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?

hard

A. Use Table.ReplaceValue with Replacer.ReplaceText and set case insensitive option

B. Add a custom column with Text.Lower and replace "fruit" with "Produce" there

C. Use Table.ReplaceValue with Replacer.ReplaceText multiple times for each case variant

D. Use Table.ReplaceValue with Replacer.ReplaceValue ignoring case automatically

Solution

1. 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.

2. 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.

3. 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.

4. Final Answer:

   Add a custom column with Text.Lower and replace "fruit" with "Produce" there -> Option B

5. Quick Check:

   Use Text.Lower in custom column for case insensitive replace [OK]

Hint: 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