How to trim white space between text in power bi?

Question

I have a column of random text with random amounts of white space between characters and strings. I know how to trim leading and trailing white space and I think I know how to remove all white space but I am struggling to remove only excess white space. Here is an example of my column:

Column1
I   have    too          much   space
123    big gap     456
asdfg   2z1xc2            .

I would like to either create a new column or transform the current one to look like this:

Coumn1
I have too much space
123 big gap 456
asdfg 2z1xc2 .

Thanks in advance!

Answer 1

To do this in the query editor, I'd recommend the approach by Ken Puls:

(text as text, optional char_to_trim as text) =>
let
    char = if char_to_trim = null then " " else char_to_trim,
    split = Text.Split(text, char),
    removeblanks = List.Select(split, each _ <> ""),
    result = Text.Combine(removeblanks, char)
in
    result

As explained in the blog post, it splits the text string by the specified character (space is the default), removes any blank elements of the list, and then converts the list back into a string.