Part V: Understanding Power Pivot and Power Query
systems. These characters are typically rendered in Excel as a question mark or square box.
But in Power Query, they show up as spaces.If the source system that supplies your data has a nasty habit of including strange char-
acters and leading spaces, you can apply the Trim and Clean functions to sanitize the data
set.Some of you may know that the TRIM function in Excel removes the leading spaces, trailing spaces, and excess
spaces within the given text. Power Query’s TRIM function removes leading and trailing spaces but doesn’t touch
the excess spaces within the text. If excess spaces are an issue in your data, you can deal with them by using the
Replace Values function to replace a given number of spaces with just one space.Extracting the left, right, and middle values
In Excel, we have the benefit of the RIGHT function, the LEFT function, and the MID
function. These functions allow you to extract portions of a string starting from different
positions:■ (^) The LEFT function returns a specified number of characters starting from the left-
most character of the string. The required arguments for the LEFT function are the
text you are evaluating and the number of characters that you want returned. For
example, LEFT ("70056-3504", 5) would return five characters starting from
the leftmost character ( 70056 ).
■ (^) The RIGHT function returns a specified number of characters starting from the
rightmost character of the string. The required arguments for the RIGHT function
are the text you are evaluating and the number of characters you want returned.
For example, RIGHT ("Microsoft", 4) would return four characters starting
from the rightmost character (soft).
■ (^) The MID function returns a specified number of characters starting from a specified
character position. The required arguments for the MID function are the text that
you are evaluating, the starting position, and the number of characters you want
returned. For example, MID ("Lonely", 2, 3) would return three characters
starting from the second character, or character number 2 in the string (one).
Power Query has equivalent functions exposed through the Extract command found on the
Transform tab (see Figure 39.13).
The options under the Extract command are as follows:
Length Transforms a given column into numbers that represent the number of characters
in each field (similar to Excel’s LEN function).
First Characters Transforms a given column to show a specified number of characters
from the beginning of text in each row (similar to Excel’s LEFT function).