Replace values in Power BI - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the 'Replace Values' feature do in Power BI?

It allows you to find specific values in your data and change them to new values, helping clean or correct data easily.

beginner

How do you access the 'Replace Values' option in Power BI Desktop?

Right-click a column in the Power Query Editor, then select 'Replace Values' from the menu.

intermediate

Can 'Replace Values' handle multiple replacements at once in Power BI?

No, you replace one value at a time. For multiple replacements, you repeat the process or use advanced methods like custom columns.

beginner

What is a common real-life example of using 'Replace Values' in Power BI?

Changing all 'N/A' or blank entries in a sales column to zero so calculations work correctly.

beginner

Does 'Replace Values' change the original data source?

No, it only changes the data inside Power BI during data loading or transformation, leaving the original source untouched.

Where do you find the 'Replace Values' option in Power BI?

A Right-click a column in Power Query Editor

B In the Visualizations pane

C In the Report view toolbar

D Under the Home tab only

You access 'Replace Values' by right-clicking a column in Power Query Editor.

What happens if you replace a value with a blank in Power BI's 'Replace Values'?

A The value becomes empty or null

B The value stays the same

C Power BI shows an error

D The entire column is deleted

Replacing with blank sets the value to empty or null, useful for cleaning data.

Can 'Replace Values' be undone after applying it in Power Query?

A No, it is permanent

B Only if you save the file first

C Only if you restart Power BI

D Yes, by deleting the applied step

You can undo by removing the step in the Applied Steps pane in Power Query.

Which of these is NOT a good use of 'Replace Values'?

A Replacing 'N/A' with zero for calculations

B Changing all zeros to blanks to hide data

C Standardizing date formats

D Fixing typos in category names

Date format changes require different transformations, not simple value replacement.

If you want to replace multiple different values at once, what should you do?

A Use a DAX measure

B Repeat 'Replace Values' for each value

C Use the 'Replace Values' once with all values separated by commas

D You cannot replace multiple values

You replace one value at a time; for multiple, repeat the process or use other methods.

Explain how to use 'Replace Values' in Power BI to fix incorrect data entries.

Think about cleaning data like fixing typos or missing values.

Describe a real-life scenario where replacing values in Power BI helps improve report accuracy.

Consider sales data or survey responses.

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