Chapter 3 focused on getting familiar with operating on rows; the focus of this chapter shifts to columns. This chapter includes various techniques like transforming string case, reformatting columns, creating calculated fields, and more. To follow this chapter’s demonstrations, refer to ch_04.xlsx in the ch_04 folder of the book’s repository. Go ahead and load the rentals table into Power Query.
Changing Column Case
Power Query streamlines the process of converting text columns between lowercase, uppercase, and “proper” case (in which each word is capitalized). To test this capability, press the Ctrl key and select the Title and Artist Name columns simultaneously. Next, right-click on one of the columns, navigate to Transform → Capitalize Each Word, as shown in Figure 4-1.
Figure 4-1. Changing text case in Power Query
Notice that Title and Artist Name lack spaces after colons and commas. To address this, with both columns still selected, right-click on either column and choose Replace Values. In the Replace Values dialog box, search for “:” and replace it with a colon followed by a space, as shown in Figure 4-2.
Figure 4-2. Replacing values in Power Query
Next, apply the same process to commas: replace them with a comma followed ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month, and much more.
