Power Query – A function to remove spaces within Text values
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.
(text as text, optional char_to_trim as text) =>
let
char = if char_to_trim = null then ” ” else char_to_trim,
nbsp = if char=” ” then Character.FromNumber(160) else “”,
split = Text.SplitAny(text, char & nbsp),
removeblanks = List.Select(split, each _ <> “”),
result=Text.Combine(removeblanks, char),
NonBlank = Text.Replace(result,” “,””)
in
NonBlank
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.
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?
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.
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.
I think you are correct Daniil, thanks for that let me confirm again!
Awesome. This really helped.
Hi Amit
Thanks for letting me know, happy to hear it has helped someone!
If you just want to remove spaces (including nbsp):
=Text.Combine(Splitter.SplitTextByWhitespace()(text))
That is a great idea, let me double check & confirm
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’
Thanks for the suggestion, let me double check and confirm, so many great solutions.
I had the same idea but discovered the limitation of this simple replace is that if there are more than two spaces in a row it won’t reduce them all to on space.
I found that if I used my solution it would remove all the required spaces.
I’m testing out your function but the code snippet has been corrupted by your syntax box. I know how to fix it for me, but can you change it to show the less than, greater than, and ampersand characters properly?
(text as text, optional char_to_trim as text) =>
let
char = if char_to_trim = null then ” ” else char_to_trim,
nbsp = if char=” ” then Character.FromNumber(160) else “”,
split = Text.SplitAny(text, char & nbsp),
removeblanks = List.Select(split, each _ “”),
result=Text.Combine(removeblanks, char),
NonBlank = Text.Replace(result,” “,””)
in
NonBlank
Hi there
Thanks for that it has been updated.