Remove spaces in Power Query (the easy way)

If data comes to Power BI through downloaded CSV/Excel files or the internet, we often encounter unwanted spaces within the text. However, we rarely want these extra spaces within Power BI. Therefore, in this post, we are looking at how to remove spaces in Power Query.

While we can remove spaces using DAX, Power Query is the best place to perform this. We want to clean the data as early as possible in the process.

Table of contents

1. Space vs. non-breaking space characters
2. Converting the nbsp character to a space
3. Removing trailing / leading spaces
4. Remove all spaces
5. Remove excess spaces with a custom function

Space vs. non-breaking space characters

Before we start, there are a few points to make you aware of. The space has an ASCII character code of 32. But webpages may use another space character known as the “non-breaking space” (AKA, nbsp). On a webpage, the nbsp character forces a space into a paragraph to create the appearance of white space. This nbsp character has an ASCII character code of 160. Any solution we create needs to handle both characters.

Converting the nbsp character to a space

It is unlikely we will ever need the nbsp character within our data. Therefore, it is a good idea to remove this character at the start and replace it with a standard space. To do this, we apply the replace values transformation.

• Select the column to have the nbsp substituted with a space [1]
• Click Transform > Replace Values from the ribbon [2],[3]
• Click into the value to find box [4] then click Advanced option > Relace using special character > Insert special character > Non-breaking space [5],[6],[7]. This will enter #(00A0) into the box. Note: #(00A0) is Power Query code for a nbsp character.
• Enter a space into the replace with box [8], then click OK [9].

Any nbsp characters are replaced with standard spaces. This prepares us for the following actions.

The M code for this step is:

= Table.ReplaceValue(Source,"#(00A0)"," ",
  Replacer.ReplaceText,{"Example Text"})

An alternative code for this uses the character number instead of the Power Query code.

= Table.ReplaceValue(Source,Character.FromNumber(160),
  " ",Replacer.ReplaceText,{"Example Text"})

We can future proof by performing this action, even if our data does not currently contain the nbsp character.

NOTE: the nbsp character isn’t all bad. We can use it to ensure the correct alignment of digits when we show negative numbers in brackets.

Remove trailing / leading spaces

The Trim transformation can remove spaces in Power Query, but only trailing and leading spaces.

• Select the column to be cleaned [1]
• Click Transform > Format > Trim from the ribbon [2],[3],[4]

The M code for this step is:

= Table.TransformColumns(#"Replaced Value",
  {{"Example Text", Text.Trim, type text}})

Remove all spaces

In your scenario, you may wish to remove all spaces. This is useful to standardize reference and ID numbers, which rarely have spaces.

The replace values transformation demonstrated above can remove all spaces. We enter the space character into the value to find box [1] with a blank string in the replace with box [2]. Finally, click OK [3].

The M code for this step is:

= Table.ReplaceValue(#"Replaced Value"," ","",
  Replacer.ReplaceText,{"Example Text"})

Remove excess spaces with a custom function

The most common scenario we encounter is the removal of leading, trailing, and multiple spaces from within a text string. This is the behavior we find in the DAX and Excel TRIM functions.

Unfortunately, there is no direct Power Query equivalent to the TRIM function. We need to create our own custom function.

Create a custom function

If we are going to the effort of creating a custom function, then let’s make it also remove nbsp and apply the clean transformation at the same time.

To create a custom function:

• Click Home > New Source > Blank Query from the ribbon [1],[2],[3].

• Call the query fxAdvancedTrim [4]
• Click View > Advanced Editor from the ribbon [5],[6]
• Enter the code below into the code window [7] and click Done [8]

The custom function contains the following code:

(textValue as text) as text =>

let
    
  //Clean the text string
  cleanText = Text.Clean(textValue),

  //Replace nbsp character for a space
  replaceText = Text.Replace(cleanText,Character.FromNumber(160)," "),

  //Split the text at each space character
  splitText = Text.Split(replaceText," "),

  //Remove the blank items from the list
  listNonBlankValues = List.Select(splitText,each _<> ""),
    
  //Join the list with a space character between each item 
  textJoinList = Text.Combine(listNonBlankValues," ")
in
    textJoinList

Power Query functions used

The Power Query functions used in the code above are :

Text.Clean: Returns a text value with all control characters removed.

Text.Clean(text as nullable text) as nullable text

Text.Replace: Returns the result of replacing all occurrences of text value with another next value. This function is case-sensitive.

Text.Replace(text as nullable text, old as text, new as text) as nullable text

Text.Split: Returns a list of text values after splitting the value based on a specific delimiter/separator.

Text.Split(text as text, separator as text) as list

List.Select: Returns a list of values from a list that match the selection criteria.

List.Select(list as list, selection as function) as list

Text.Combine: Returns the result of combining a list of text values into a single string, which can be joined with a separator between each element.

Text.Combine(texts as list, optional separator as nullable text) as text

Using the custom function

Now it’s time to use the new function.

In this scenario, we want to transform an existing column rather than add a new column. The easiest way to do this is to perform a similar step, then change the standard code. Let’s apply the trim transformation we used earlier. The M code for this step will be as follows:

= Table.TransformColumns(Source,{{"Example Text", Text.Trim, type text}})

In the M code, change the reference to Text.Trim to fxAdvacnedTrim (the name of the custom function we created.

The code will become this:

= Table.TransformColumns(Source,{{"Example Text", fxAdvancedTrim, type text}})

As our custom function only has a single argument, there is no need to pass any arguments into the function.

Ta-Dah!!! The text is now trimmed and cleaned.

The new column now contains cleaned text values with no excess spaces. Woop Woop!!!

Other resources

Here are other resources to help you remove spaces in Power Query and manipulate text:

• Clean whitespace in Power Query
• Remove excess spaces in Power Query
• Text functions in Power Query (150+ Examples)