M Language Remove Duplicate or Repeating Spaces or Characters

[KilleenJeffrey]

I couldn't find a example for how to do this (Removing Repeating Spaces) so I am posting this.  It would work for any repeating Characters beyond spaces.

let
    Source = "abc   ABC       DEF ABC",
    StringToCleanOfExtraSpaces = Source,
    SourceSingleSpaced = Text.Combine(
                                List.RemoveNulls(
                                    List.Transform(
                                        List.Positions(
                                            Text.ToList(
                                                StringToCleanOfExtraSpaces)), each 
                                                                                if _ = 0 then Text.Range(StringToCleanOfExtraSpaces, _ , 1) 
                                                                                else if Text.Range(StringToCleanOfExtraSpaces, _ -1,1)=" " and Text.Range(StringToCleanOfExtraSpaces, _ ,1)=" " then null 
                                                                                else Text.Range(StringToCleanOfExtraSpaces, _, 1))))
in
    SourceSingleSpaced

[jbwtp · Accepted solution]

Hi @KilleenJeffrey,

There is no stock function to remove repeating spaces. If you only concerned about spaces, the function can be shorter, but not much:

let
    Source = "abc   ABC       DEF ABC",
    StringToCleanOfExtraSpaces = Source,
    SourceSingleSpaced = Text.Combine(
                            List.Accumulate(
                                    Text.ToList(StringToCleanOfExtraSpaces), {}, (a, n)=> if n = " " and List.Last(a) = " " then a else a & {n}))
in
    SourceSingleSpaced

Kind regards,

John

[jbwtp · Accepted solution]

Hi @KilleenJeffrey,

There is no stock function to remove repeating spaces. If you only concerned about spaces, the function can be shorter, but not much:

let
    Source = "abc   ABC       DEF ABC",
    StringToCleanOfExtraSpaces = Source,
    SourceSingleSpaced = Text.Combine(
                            List.Accumulate(
                                    Text.ToList(StringToCleanOfExtraSpaces), {}, (a, n)=> if n = " " and List.Last(a) = " " then a else a & {n}))
in
    SourceSingleSpaced

Kind regards,

John

[AlexisOlson · Accepted solution]

Here are a couple of shorter options.

let
    StringToClean = "  abc   ABC       DEF ABC ",
    SplitText = Text.Split(StringToClean, " "),
    RemoveBlanks = List.Select(SplitText, each _ <> ""),
    Result = Text.Combine(RemoveBlanks, " ")
in
    Result

This is a slightly simplified version of this post (only works for spaces).

let
    StringToClean = "  abc   ABC       DEF ABC ",
    Result = Text.Combine(Splitter.SplitTextByWhitespace()(Text.Trim(StringToClean)), " ")
in
    Result

Credit to Frank Tonsen's comment on this post.

[KilleenJeffrey]

For spaces that last example is slick