In this article, we will cover how to trim text in Power Query.
We go into detail on how to trim different types of text, such as whitespace, leading and trailing zeros as well as first and last characters. This is done by using Text.Trim functions and variations of it.
This is very useful for data cleansing in Power Query.
Lastly, we cover the key difference between trim and clean in Power Query.
Text.Trim in Power Query
Text.Trim in Power Query can be used to clean your data by removing whitespaces and other unwanted text.
There are three Text.Trim functions in Power Query M that you can use to trim whitespace, leading text, or first and last characters in your text strings.
These are:
- Text.Trim
- Text.TrimStart
- Text.TrimEnd
There is also a function Text.RemoveRange that allows you to remove text in a specified range.
Trim whitespace
You can use Text.Trim to trim leading and trailing blank spaces (whitespace) in Power Query.
To do this, right-click on the column you want to trim, then go to ‘Transform’ and then choose ‘Trim’.
See the image below.
This will remove the leading or trailing whitespace from the column you selected.
Here is an example of a column with reference numbers, where some of the cells contain leading and trailing blank spaces.
Here is the formula for the Trim Text applied step in our Power Query example.
= Table.TransformColumns(#"Changed Type",{{"References", Text.Trim, type text}})These are the results:
The whitespace has been removed.
Trim leading zeros
To trim leading zeros from your data in Power Query, you can use the Text.TrimStart function.
Text.TrimStart has the syntax:
=Text.TrimStart( text as nullable text, optional trim as any )This means you can enter a column as the first parameter, to specify which one you want to trim text in, as well as the specific text you want to trim as the second parameter.
If you enter only the first parameter into the function, it will trim the leading whitespace from the specified column or text string.
Here’s an example of how we trim leading zeros from a column of reference numbers:
See the image below for the starting column.
To trim the leading zeros from this column, right click on the column and select ‘Transform’, then ‘Trim’, as described in the previous section.
Once this step has been applied, you can amend the M Code to Text.TrimStart.
Here is the code we would use in this example:
= Table.TransformColumns(#"Changed Type", {{"References", each Text.TrimStart(_,"0")}})The part in bold is what we have changed in the formula. Using Text.TrimStart, we specify the column and to remove all leading zeros.
Here is the result:
If we wanted to create a new column to trim the leading zeros and also keep your original column, we would create a custom column and use the following formula.
= Text.TrimStart([References],"0")This would produce the following custom column with the leading zeros removed.
If you wanted to remove trailing zeros from your data in Power Query, you can use the Text.TrimEnd function.
It works in exactly the same way as Trim.TextStart and you can follow the same steps as above but change the function to Trim.TextEnd.
An alternative way to trim leading zeros is to use the ‘Replace Values’ function and replace them with blank spaces, and then trim them using Text.Trim.
Trim first character
To remove the first character from a column in Power Query you can use the function Text.RemoveRange.
This is superior to Trim.TextStart in most scenarios because the first character may not be the same in all cells.
Text.RemoveRange will remove the first character(s) in the text string no matter what they are, whereas it has to be specified with Trim.TextStart.
The syntax for Text.RemoveRange is:
=Text.RemoveRange( text as nullable text, offset as number, optional count as nullable number )
The first parameter is where/what text you want to remove (this can be a column).
The second parameter is the position that you want to remove the text from.
The third parameter is how many characters you want to remove.
We will use the same example from the previous section.
To remove the first character, right-click on the column and choose ‘Transform’ and then ‘Trim’. Once this step has been applied, you can edit the M Code.
This is the code we would use to trim the first character:
= Table.TransformColumns(Source,{{"References", each Text.RemoveRange(_,0,2)}})The part in bold is what we have changed in the formula.
We have replaced it with Text.RemoveRange and entered the first parameter as the column, the second as where to start (0, i.e. the beginning of the text string), and the third as how many characters to remove (2).
Here is the result:
Here is the formula if we wanted to create a custom column instead, rather than removing the first characters from the initial column:
= Text.RemoveRange([References],0,2)Here is the custom column result:
Trim last character
To remove the last character from your data in Power Query, you can also use Text.RemoveRange.
Again, this has the same advantages as Text.End as shown in the previous section.
The Text.RemoveRange formula has to be modified slightly to remove the last character(s) from your text strings.
The second parameter (the position in the text that you want to remove characters from) has to use Text.Length to find the end of the string.
Using the same example as the previous section, here’s the formula we would use to trim the last two characters:
= Table.TransformColumns(Source,{{"References", each Text.RemoveRange(_,Text.Length(_)-2,2)}})We are using Text.Length(_) – 2 to give the starting position as two characters from the end of the string, i.e. the last two characters.
If you just wanted to trim the last character, you would change this to Text.Length(_) – 1 and also change the third paramater from 2 to 1.
Here are the results:
Here is the formula if we wanted to create a custom column instead, rather than removing the last characters from the initial column:
= Text.RemoveRange([References],Text.Length([References])-2,2)Here is the custom column result:
Trim vs Clean in Power Query
In Power Query, clean removes control or non-printable characters (line breaks, for example) from a text value, whereas Trim just removes leading or trailing whitespace.
Trim is generally used more frequently than Clean in Power Query because accidental whitespace is more of a common issue in data quality than control characters.
Both of these functions are found in Power Query under the ‘Transform’ section after you right-click on a column.