Part V: Understanding Power Pivot and Power Query
infringement on your company’s trademarked name, and it must be changed to Boulevard as
soon as possible. How would you go about meeting this new requirement?
The Replace Values function is ideal in a situation like this. Select the Address field and
then click the Replace Values command on the Home tab.
In the Replace Values dialog box (shown in Figure 39.10), simply fill in the Value To Find
and the Replace With fields.
FIGURE 39.10
Replacing text values
Note that clicking Advanced Options exposes two optional settings:
Match entire cell contents Checking this option tells Power Query to replace values that
contain only the text entered into the Value To Find field. This comes in handy when you
want to replace zeros ( 0 ) with n/a but don’t want to affect any zeros that are actually part
of a number, only those that are alone in a cell.
Replace Using Special Characters Checking this option allows you to use special invis-
ible characters such as line feed, carriage return, or tabs as replacement text. This is useful
when you want to force an indent or reposition the text so that it shows up on two lines.
Trimming and cleaning text
When you receive a data set from a mainframe system, a data warehouse, or even a text
file, it is not uncommon to have field values that contain leading and trailing spaces. These
spaces can cause some abnormal results, especially when you’re appending values with lead-
ing and trailing spaces to other values that are clean. To demonstrate this, look at the data
set in Figure 39.11.
