In a world drowning in data, the ability to clean and manipulate that information is more crucial than ever.
Power Query Trim is a tool that might seem simple but has profound effects on data quality. Intrigued? Let’s dive into the details.
Power Query Trim is a function used within Microsoft’s Power Query tool, designed to remove unnecessary spaces from the beginning and end of a text string. Whether it’s extra spaces between words or unwanted spaces at the start or end, Trim ensures that your data is clean and consistent.
In this article, you’ll discover the ins and outs of the Power Query Trim function. From understanding its basic usage to exploring practical applications, we’ll guide you through everything you need to know to master this essential tool.
Let’s jump in!
How to Use the Trim Function in Power Query
In Power Query, Trim is a function used to clean up data by removing unwanted characters, especially whitespaces, from strings. This function is predominantly executed through the M code language, which serves as Power Query’s formula language.
In the M code language, there are three different Text.Trim functions available:
- Text.Trim – This basic function removes leading and trailing whitespaces from a given text string.
- Text.TrimStart – Used when you want to remove leading (starting) characters from a string.
- Text.TrimEnd – Useful for removing trailing (ending) characters from a text string.
In this section, we’ll go over each of these three functions and provide examples of their usage.
1. Text.Trim
The Text.Trim function has the following syntax:
TrimmedText = Text.Trim(SourceText, "characters_to_trim")In the above snippet, the first parameter, SourceText, is the text you want to trim, and the second parameter, “characters_to_trim”, is an optional string containing the specific characters you want to remove from both the beginning and end of the text.
If the second argument is omitted, the function will trim whitespace characters by default.
For example, to remove leading and trailing whitespaces from the text string ” a b c d “, you would use the following M code:
Text.Trim(" a b c d ")Output:
"a b c d"Here is what this example would look like in the Power Query Editor:
Note: Text.Trim does not remove spaces between words.
2. Text.TrimStart
The Text.TrimStart function will only remove whitespace from the start of a text string. For example:
Text.TrimStart(" a b c d ")Output:
"a b c d "As you can see, it removes all of the whitespace at the start of the string, but it doesn’t remove the whitespace that comes after, nor the space between the letters.
The output in the Power Query Editor is as follows:
3. Text.TrimEnd
The Text.TrimEnd function removes whitespace from the end of a text string. For example:
Text.TrimEnd(" a b c d ")Output:
" a b c d"TrimEnd does the opposite of TrimStart and removes spaces at the end of the text string but leaves the whitespace at the beginning untouched.
The Trim function in Power Query offers powerful tools for manipulating text strings. With text.trim, you can remove spaces from both the beginning and end of a text; text.trimstart allows you to trim spaces from the beginning; and text.trimend focuses on removing spaces from the end.
These functions provide flexibility and efficiency in handling text data within your queries. However, it’s important to understand how to handle special characters, which is what we’ll delve into in the next section.
How to Handle Special Characters with the Trim Function
Special characters within text data can often present challenges in data processing and analysis. Power Query’s Trim function not only allows for the removal of spaces but can also be tailored to handle various special characters that might be present within your data.
In this section, we’ll explore how to use the Trim function to manage special characters, including those that may be considered extraneous or disruptive in certain contexts.
1. Handling Control Characters with Text.Clean
In addition to removing whitespaces, you might need to clean up control characters from your data.
Control characters are non-printing characters, such as carriage returns and line feeds, that can cause issues when processing and analyzing data.
They are characters that don’t represent a written symbol or glyph. They are often used to control devices (such as printers) that make use of text, or to provide meta-information about the text itself.
To remove control characters in your strings, you can use the Text.Clean function as follows:
The above code snippet defines a text string containing a non-printable character and then cleans it using the Text.Clean function.
The variable SourceText is assigned a concatenated string that includes the text “This is a text with a non-printable character: ” and a non-printable “bell” character, represented by Character.FromNumber(7).
Next, the CleanedText variable is assigned the result of applying the Text.Clean function to SourceText, effectively removing all non-printable characters, including the bell character.
Text.Trim and Text.Clean can also be combined as well, for example:
Text.Clean(Text.Trim(" a b c d\t "))This will remove both whitespaces and the tab character from the input text.
2. Handling Specific Characters with Text.Trim
The Text.Trim function can be used to remove specific characters from both the beginning and end of a text string. By providing a second argument, you can specify the characters to trim.
This code snippet will remove all the hash (#) characters from both the beginning and end of the SourceText, resulting in “This is a text with special characters.”
3. Using Text.TrimStart and Text.TrimEnd to Handle Specific Characters
You can also use Text.TrimStart and Text.TrimEnd to remove specific characters from the beginning or end of a text string, respectively.
In the above example, TrimmedStart will contain “This is a text with special characters###”, while TrimmedEnd will contain “###This is a text with special characters.”
Handling special characters with the Trim function in Power Query provides a flexible and powerful way to clean and manipulate text data.
By using Text.Trim, Text.Clean, Text.TrimStart, and Text.TrimEnd with specific characters, you can precisely control how text is processed.
These functions are essential tools for anyone working with text data that may contain unwanted or extraneous characters, allowing for cleaner and more accurate data analysis.
Final Thoughts
Working with text data in Power Query requires a nuanced understanding of various functions and techniques, and the Trim function plays a vital role in this process.
From basic trimming of whitespace to handling special characters, the functions we’ve explored — Text.Trim, Text.Clean, Text.TrimStart, and Text.TrimEnd — offer a comprehensive solution for text manipulation.
These functions are more than just tools for cleaning data; they are essential components in the data preparation stage, enabling you to transform raw data into a format suitable for analysis.
By understanding how to use them effectively, you can enhance the quality of your data, streamline your workflow, and ensure that your analyses are built on a solid foundation.
If you’d like to learn more about the M language and Power Query in general, check out our introduction to Power Query masterclass below:
Frequently Asked Questions
Can you TRIM in Power Query?
No, you can’t directly use the TRIM function in Power Query as you would in Microsoft Excel.
However, you can achieve similar functionality by utilizing the “Text.Trim” function within Power Query’s M language. This function lets you remove leading and trailing spaces from text values in your data transformations.
How to trim spaces in Power Query?
To trim leading and trailing spaces in Power Query, use the Text.Trim function. Right-click on the column you want to trim, go to ‘Transform’, and then choose ‘Trim’.
Text.Trim(text as nullable text, optional trim as any) as nullable textWhat’s the method to remove trailing spaces in Power Query?
To remove trailing spaces in Power Query, use the Text.TrimEnd function. Apply this function to the desired column or columns.
How to trim leading zeros in Power Query?
Trimming leading zeros can be done by using the Text.TrimStart function with “0” as the second argument.
Text.TrimStart(text as nullable text, optional trim as any) as nullable textCan you trim the last character in Power Query?
Yes, the last character can be trimmed using the Text.Range function.
Text.Range(text as nullable text, start as number, optional count as nullable number) as nullable textTo remove the last character, use Text.Length – 1 as the count argument in the function.
Why is Power Query trim not removing spaces?
If Power Query’s Text.Trim function is not removing spaces, it’s possible that the spaces are non-breaking spaces or other special characters that are not recognized as whitespace by the function.
In such cases, you can use the Text.Replace function to replace specific special characters with regular spaces, and then apply the Text.Trim function.
