Replace values in Power BI - Cell-by-Cell Formula Trace

Sample Data

This table lists products with some repeated names.

Cell	Value
A1	Product
A2	Apple
A3	Banana
A4	Cherry
A5	Banana
A6	Apple

Formula Trace

NewProduct = SUBSTITUTE(Table1[Product], "Banana", "Orange")

Step 1: SUBSTITUTE("Apple", "Banana", "Orange")

Step 2: SUBSTITUTE("Banana", "Banana", "Orange")

Step 3: SUBSTITUTE("Cherry", "Banana", "Orange")

Step 4: SUBSTITUTE("Banana", "Banana", "Orange")

Step 5: SUBSTITUTE("Apple", "Banana", "Orange")

Cell Reference Map

   A       
1 Product   
2 Apple    
3 Banana   
4 Cherry   
5 Banana   
6 Apple    

Formula references Table1[Product] column cells A2 to A6.

The formula reads each product name from the Product column to check for 'Banana' to replace.

Result

   A         B        
1 Product   NewProduct
2 Apple     Apple    
3 Banana    Orange   
4 Cherry    Cherry   
5 Banana    Orange   
6 Apple     Apple    

The new column 'NewProduct' shows 'Banana' replaced by 'Orange', other values remain the same.

Sheet Trace Quiz - 3 Questions

What will the value in NewProduct be for the original value 'Banana'?

A Orange

B Banana

C Apple

D Cherry

Key Result

SUBSTITUTE(text_column, old_text, new_text) replaces occurrences of old_text with new_text in each text value.