Chapter 39: Transforming Data with Power Query
39
FIGURE 39.11
Leading spaces can cause issues in analysis.
This is intended to be an aggregate view that displays the sum of the dollar potential for
California, New York, and Texas. However, the leading spaces are forcing each state into
two sets, preventing you from discerning the accurate totals.
You can easily remove leading and trailing spaces by using the Trim function in Power
Query. Figure 39.12 demonstrates how you would update a field to remove the leading and
trailing spaces by right-clicking the column and using the Transform ➪ Trim command.
FIGURE 39.12
The Trim command
Again, the Trim command will be applied to any column or columns you select. So, you can
fix multiple columns at once simply by selecting them before selecting the Trim command.
Figure 39.12 also shows the Clean command (beneath Trim). While Trim removes lead-
ing and trailing spaces, the Clean command will remove any invisible characters such as
carriage returns and other nonprintable characters that may slip in from external source