Excel 2019 Bible

Chapter 39: Transforming Data with Power Query

39

FIGURE 39.5

Replacing empty strings with the word Undefined

We can do so by right-clicking the ContactTitle field, selecting the Replace Values com-

mand, and then entering the word Undefined in the Replace With text box. As you can

see in Figure 39.5, because we are replacing an empty string, there is no need to enter any-

thing in the Value To Find text box.

If you need to adjust or correct the step where you replaced values, you can reactivate the Replace Values dialog
box by clicking the gear icon next to the name for that step in the Query Settings pane. This is true for basically any
action that requires a dialog box to complete. Clicking the gear icon next to any step name will activate the appropri-
ate dialog box for that step.

Concatenating columns

You can easily concatenate (join) the values in two or more columns. In Power Query, this is

achieved by using the Merge Columns command. The Merge Columns command concatenates

the values in two or more fields and outputs the newly merged values into a new column.

The idea is to select the columns that you want to concatenate, right-click, and select the

Merge Columns command, as shown in Figure 39.6.

This will activate the Merge Columns dialog box shown in Figure 39.7. Here, you have the

option of choosing a character that will act as a separator for the concatenated values. You

have all of the standard options such as comma, semicolon, space, and so forth. As you can

see, you also have the option of naming the new column that will be created.