In Power Query, there are some functions that are very similar to Excel, which you expect the same results from them. However, it may not be the case because they indeed perform differently. TRIM is one of them.

TRIM is a common function in Excel that removes all leading and trailing spaces in a text string. On top of that, TRIM also removes all extra space in between texts. As a result, only single spaces between texts will be kept. Below screenshot shows the results returned by TRIM.

When we perform Transform –> Trim or use the Text.Trim function in Power Query, the results may be a surprise to you! (At least it surprised me when I first noticed it). Here’s the result from Power Query:

Whenever we see “strange” results, we should always check the definition of the function. Indeed, we should do so before we use a function (in the ideal world). But I admitted that I did not do so either. 😅

From the MS documentation, I realized that the Text.Trim function removes leading and trailing spaces only. That’s why.

Unfortunately, there is no such function as Text.TrimLikeExcel in Power Query. But you know, when we work with Excel, there will be workaround(s), most of the time. We may achieve the same with a few Power Query functions together, i.e a little bit of M.

In this video, I will show you step by step to achieve this.

You may download a sample file to follow along.

Let’s watch it in action:

If you prefer reading article to watching video, please continue reading.

Create a list of texts

In the Power Query Editor,

1. Go to Add Column
2. Custom Column
3. Input the following formula
4. OK

= Text.Split([Original Text], " ") 'split when there is a single space

As you can guess from the name of the function, it splits texts by the delimiter specified, which is a single space (” “) in our example. And the function returns a list as a result.

We now have a list of texts and empty string.

Remove empty string from the list

With the listed returned, we want to remove all empty strings from the list. We can achieve this by using List.RemoveItems function. Let’s do it with another custom column using the following formula:

= List.RemoveItems([Custom], {""})

List.RemoveItems takes two arguments, both are list. Here’s the syntax:

List.RemoveItems(list1 as list, list2 as list) as list

You may refer to the MS documentation here.

Don’t miss the curly bracket {} in the second argument because it expects a list. In our case, we just want to remove empty items (denoted by “”) from the list. Therefore, there is only one item in list2.

= List.RemoveItems([Custom], {""})

At this point, we should have obtained lists of texts only, as shown below:

Combine the texts

The last step will be joining the texts with a single space. Text.Combine is the function we need. Let’s add another custom column with the following formula:

= Text.Combine([Custom.1], " ") 
'It means to combine the texts in the list with a space as delimiter

Here we go!

Not too difficult, is it? 🤔

It takes three simple steps instead of one though. If you want to have one step instead, we may wrap the functions together in one single formula (step).

 = Text.Combine ( List.RemoveItems (Text.Split ([Original Text], " "), {""}), " ")

It is very similar to writing Excel formula indeed.

You may be wondering why it is so complicated in Power Query to accomplish a simple task in Excel. That’s normal. I have the same feeling too. Nevertheless, I am amazed by the power of M(agical) Power Query functions. It has enormous possibilities in all sorts of data transformation, waiting for us to explore! Can you feel the power?