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) =>
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 _ <> “”),
NonBlank = Text.Replace(result,” “,””)
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.