UPDATE: 19 Nov 2018

With the recent announcement of dataflows in the Power BI Service, I see more people will be looking to better understand and leverage dataflows by using the M language which is available in Power BI Desktop, Power Apps and Microsoft Flow.

I had a great response to this blog post, and both Ted and Daniil had a much easier solution to remove spaces from the data in a column. I am not sure why this originally did not work for me, but I am always happy to learn from others. As well as find an easier way to achieve the same outcome.

The solution is all that you need to do, is to right click your column and click Replace Values, as you can see below I am searching for the space and replacing with no space

 

 

 

BELOW IS THE ORIGINAL BLOG POST

The Power Query function below will remove any spaces that I have in a text field.

I got the original Power Query function from Ken Puls blog post Clean WhiteSpace in PowerQuery which he does a great job of removing leading, trailing or multiple spaces within text.

My requirement was to remove any spaces within the text too.

I created a Blank Query in the Power Query Editor and named it fx_ReplaceSpaces

Below is the code that I actually used.

And here is what it looks like with some sample data

And this is the output, where I wanted all spaces removed

There might be a more elegant way to achieve this, so if anyone has got any suggestions please let me know, I will be happy to test and update this blog post.

8 thoughts on “Power Query – A function to remove spaces within Text values”

  1. Have you tried replacing spaces without trimming them? It seems like you don’t even need a function for this — you could just replace spaces.

    Trimming the spaces beforehand seems extraneous to me?

    1. Hi Daniil,

      Thanks for the comment, could you possibly have a bit more details on how to replace the spaces?

      Would this be using a replace values?

      I did try something similar initially with the number column and it appeared to not work as expected.

      1. Yes, Replace Values… is what I meant — was the number column of type Any? Sometimes the UI won’t let you select Replace Values… in this case.

        If you explicitly transform the column to type Text, you’ll be able to right-click on the column and select Replace Values… then replace space with nothing — this will remove all spaces, including consecutive ones.

  2. Hi Gilbert,

    Why not try the following using the standard Text.Replace function …

    let
    Source = ” ab c def g “,
    ReplacedText = Text.Replace(Source,” “, “”)
    in
    ReplacedText

    The result is ‘abcdefg’

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.