Remove spaces from words based on length of word = 1

[Pandadev]

Is there a way to remove a space between words if the words are only 1 character in length , as in the example below I am trying to do a search based on the first two words from my_op appearing in the_op

My formula works fine , except when a name is like A A Bros Manufacturing , the search formula looks for A or A , which there is a high chance it could appear in a wrongly matched name that has an A in it. If I could remove all spaces where there is only 1 letter in the words , so my search criteria would be AA or Bros. That would solve my issue , is this possible

[amitchandak]

@Pandadev , refer if these can help

M

https://docs.microsoft.com/en-us/powerquery-m/text-trim

Dax

https://docs.microsoft.com/en-us/dax/trim-function-dax

[Pandadev]

Thanks , i checked both links , but they are just a Trim of data.

What i needed was to remove space from name when there is only 1 letter in each word

A A Bros Ltd  would be AA Brost Ltd

[Anonymous]

Hi @Pandadev,

You can refer to below query formula to remove one length space between characters:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclRwVHAqyi9W8ClJUVCK1YlWclJwUlBwRvDd8/PTc1IVkhXyFXIVChQSFfIUKoEysQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Select(Text.Split(Text.Trim([Text])," "), each _ <> null)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Combine", 
        each 
            if Text.Length(List.First([Custom]))>1 
            then
                Text.Combine(List.Select([Custom], each Text.Length(_)>1)," ")&" "&
                Text.Combine(List.Select([Custom], each Text.Length(_)=1)) 
            else
                Text.Combine(List.Select([Custom], each Text.Length(_)=1))&" "&
                Text.Combine(List.Select([Custom], each Text.Length(_)>1)," ")
                )
in
    #"Added Custom1"

Notice: this formula does not work for a complex mixed text string. (e.g. 'A A BAC B B C' => 'AA BAC BBC', I try to modify the functions to compatibility with these scenarios but failed)

Regards,

Xiaoxin Sheng

[Pandadev]

How do I add that code to a custom column please , like how I add the remove all non a to z / non numeric numbers in the above screenshot. Thanks

[Anonymous · Accepted solution]

HI @Pandadev,

You can add two custom columns and past the below codes into the editor.

Custom column1: replace [Text] with your fields who stored the text values.

List.Select(Text.Split(Text.Trim([Text])," "), each _ <> null)

Custom column2: replace [Custom] with above custom column name

if Text.Length(List.First([Custom]))>1 
then
    Text.Combine(List.Select([Custom], each Text.Length(_)>1)," ")&" "&
    Text.Combine(List.Select([Custom], each Text.Length(_)=1)) 
else
    Text.Combine(List.Select([Custom], each Text.Length(_)=1))&" "&
    Text.Combine(List.Select([Custom], each Text.Length(_)>1)," ")

Regards,

Xiaoxin Sheng