Yes, Power Query has a dedicated Trim function that removes leading and trailing spaces from text, and you can apply it from the Transform tab or with the Text.Trim formula. Unlike Excel’s built-in TRIM function, Power Query’s Trim does not remove extra spaces between words by default—you need a separate step if you want that. This article explains how to use Trim in Power Query, how it differs from Excel formulas, and how to handle common text-cleaning tasks.
What Power Query Trim Actually Does
Power Query’s Trim command removes all whitespace characters (spaces, tabs, non-breaking spaces) from the beginning and end of a text value. It does not touch spaces inside the text. For example, applying Trim to " Hello World " gives "Hello World" (the internal double space remains). This is different from Excel’s TRIM function, which collapses all multiple spaces into a single space and trims the ends. If you need to remove extra internal spaces, you must use a different approach in Power Query, as described below.
How to Apply Trim in Power Query (Step-by-Step)
There are two main ways to trim text in Power Query: using the ribbon buttons or writing a custom column.
- Using the Transform tab: Select the column(s) you want to clean, go to the Transform tab, and click Format > Trim. This replaces the original column values with trimmed versions.
- Using Add Column: If you want to keep the original column, go to Add Column > Format > Trim. This creates a new column with the trimmed text.
- Using a custom formula: In the formula bar, you can use
Text.Trim([ColumnName])orText.TrimStart/Text.TrimEndfor one-sided trimming.
Note: In Power Query, the Trim command is available in Excel for Microsoft 365, Excel 2021, and Excel 2016 (via Get & Transform). The steps are identical on Mac and Windows. On Excel for the web, Power Query is not available; you would need to use Excel formulas instead.
Trimming vs. Cleaning: Removing Extra Spaces Inside Text
If your data has multiple spaces between words (e.g., “John Smith”), Power Query’s Trim won’t fix that. To collapse internal spaces, you need to use the Text.Clean function? No, that removes non-printable characters. The correct approach is to use the Text.Trim function combined with a replacement of double spaces with single spaces, repeated until no double spaces remain. In Power Query, you can do this by adding a custom column with this formula:
= Text.Trim(Text.Replace(Text.Replace([Column], " ", " "), " ", " "))
That formula replaces two consecutive spaces with one, twice. For more thorough cleaning, you can use a recursive approach, but for most data, two passes are enough. Alternatively, you can use the Replace Values feature: select the column, go to Transform > Replace Values, replace ” ” (two spaces) with ” ” (one space), and repeat until no double spaces remain. This is manual but effective.
When to Use Power Query Trim vs. Excel TRIM Function
Choose Power Query Trim when you are already importing and transforming data in Power Query, especially if you need to repeat the cleaning on a refresh. Choose Excel’s TRIM function when you have data already in a worksheet and want a quick formula. Here are key differences:
- Excel
TRIMremoves all spaces except single spaces between words; Power Query Trim only removes leading/trailing spaces. - Excel
TRIMis a volatile function? No, it’s not volatile, but it recalculates when the source changes. Power Query Trim is applied during data load and is static until you refresh. - Power Query Trim can handle non-breaking spaces (character code 160) if you specify them, but by default it removes them. Excel’s TRIM also removes non-breaking spaces in most recent versions, but older versions may not.
If you need to trim text in a table that is not connected to Power Query, use Excel’s TRIM function. If you are building a data pipeline, use Power Query.
Common Problems and Solutions
Here are frequent issues users encounter when trimming in Power Query:
- Trim doesn’t remove spaces between words: As noted, that’s by design. Use Replace Values or a custom column with
Text.Replace. - Trim doesn’t work on numbers: Trim only works on text. If your column is numeric, you need to convert it to text first (Transform > Data Type > Text) or use a different method.
- Non-breaking spaces remain: Some imported data uses non-breaking spaces (e.g., from web pages). Power Query’s Trim does remove them by default, but if not, use
Text.Replacewith the non-breaking space character:Text.Replace([Column], Char.FromNumber(160), " ")then trim. - Trim applied to a column that has mixed data types: If the column contains numbers and text, Trim may error. Ensure the column is text type before trimming.
Alternatives to Trim in Power Query
Besides Text.Trim, Power Query offers other text-cleaning functions:
Text.TrimStartandText.TrimEnd: Remove spaces only from the start or end.Text.Clean: Removes all non-printable characters (e.g., line breaks, control characters).Text.Remove: Removes specified characters from a string.Text.Select: Keeps only specified characters.
For example, to remove all spaces (including internal), you could use Text.Remove([Column], " "), but that removes every space, not just extras. For most cleaning tasks, combining Trim with Replace Values is sufficient.
Frequently Asked Questions
Does Power Query Trim remove spaces between words?
No, by default it only removes leading and trailing spaces. To remove extra spaces between words, you need to replace double spaces with single spaces manually or with a custom formula.
How do I trim all text columns at once in Power Query?
Select all the columns you want to trim (click the first column header, hold Shift, click the last), then go to Transform > Format > Trim. This applies to all selected columns simultaneously.
Is Power Query Trim the same as Excel’s TRIM function?
No. Excel’s TRIM also collapses multiple internal spaces to one. Power Query’s Trim does not. They are similar but not identical.
Can I trim text in Excel for the web?
Power Query is not available in Excel for the web. You can use the TRIM worksheet function instead, which works in the browser.
Why does Trim not work on my column with numbers?
Trim is a text operation. If your column is numeric, convert it to text first (Transform > Data Type > Text) before trimming.
How do I remove non-breaking spaces in Power Query?
Use Text.Replace([Column], Char.FromNumber(160), " ") to replace non-breaking spaces with regular spaces, then apply Trim. This handles spaces that come from web pages or other sources.
Related
- Excel First Letter Uppercase Rest Lowercase?
- Excel Eye Center Provo: Services, Doctors & What to Know
- Excel: Count Cells with Specific Text
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Find a Name in Excel: Search, Match, and TroubleshootTo find a name in an Excel spreadsheet, press Ctrl+F on Windows, type the name, and select Find…
- SQL LEFT Function: Syntax, Examples, and DifferencesSQL’s LEFT function returns a specified number of characters from the beginning of a string. For example, LEFT('Database',…
- Arduino Parts and Functions: What Each Component DoesAn Arduino board is a small, programmable circuit board used to read inputs and control outputs. Its main…
- Divide in Excel Without a Function: Use the / OperatorTo divide in Excel without a function, enter a formula that starts with an equals sign and uses…
- Excel Date Filter Multiple Criteria: Practical MethodsTo filter dates in Excel by multiple criteria, turn on AutoFilter, open the date column’s dropdown, and choose…
- Filter Excel Table by Criteria: A Practical Excel GuideTo filter an Excel table by criteria, open the drop-down arrow in the column header, choose a value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.