Text.ExcelTrim function in Power Query M Language

[Vijay_A_Verma]

Use Case - Excel doesn't provide LTRIM and RTRIM but Power Query M Language does provide Text.TrimStart and Text.TrimEnd. Here, Power Query M Language scores over Excel. Both Excel and Power Query M Language provide Trim functions. Excel's Trim is documented here (TRIM) and M language's Trim is documented here (Text.Trim). 

Text.Trim - Returns the result of removing all leading and trailing whitespace from text value. Whitespace encompasses many characters which can be referred to here - Whitespace character

Excel's Trim - Removes all spaces from text except for single spaces between words. Specifically, this removes ASCII Character Code 32 only. It doesn't remove any other Whitespace character other than space character having ASCII character code 32.

This ASCII character 32 is called space character and is the most common Whitespace character. This is introduced when you press spacebar on your keyboard.

Now, Excel's Trim will remove all leading and trailing spaces and also removes all spaces between words save single space. Hence, if I write "    Great  Power       Query   ", then Excel's Trim would make it "Great Power Query" (note single spaces between the words.) Whereas Text.Trim would remove only leading and trailing spaces and will leave spaces between words untouched. Hence, running Text.Trim on this would yield the result "Great  Power       Query"

So, how to create a fomula in Power Query's M Language which gives the result equivalent to Excel's Trim function.

Solution -  Text.ExcelTrim function can be made by following formula

= Text.Combine(List.RemoveItems(Text.Split([Data]," "),{""})," ")

How does it work -

1. Text.Split([Data]," ") - This splits the given text string into a list having words separated at each space character.

Hence if my text string is "a b c d", the list generated will be {"a","b","c","d"}

For the sake of simplicity, I would replace space with numbers in the string so that you can count spaces better.

Hence "    Great  Power       Query   " is equivalent to "1234Great12Power1234567Query123"

Now, Text.Split([Data]," ") will be giving following output = {"","",''","","Great","","Power","","","","","","","","Query","","","",}

2. List.RemoveItems(Text.Split([Data]," "),{""}) - Removes all "".

Hence what we get is {"Great", "Power", "Query"}

3. Now, the next step is to combine these words with one space character. Hence, final formula becomes

= Text.Combine(List.RemoveItems(Text.Split([Data]," "),{""})," ")

Below is a Query which you can use it to test. I have replaced space with * in input and output both by duplicating the column so that it is easier to count spaces.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYy9DsIwEINfxcrMe8AAEoShQ5Th0B00UnpBaUrUPj3pj5fPsi07Z9B0zkIFuKcqGbsek+S50fiTMzdqvic+OhBiKCXK6iMNr20EXPfQCsMGDvrBJSXeSkvfSReJxxJPTbWh60NZX0gZo/xEwZXyezTe/wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Data"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Data", "Data - Copy"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column"," ","*",Replacer.ReplaceText,{"Data - Copy"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Text.ExcelTrim", each Text.Combine(List.RemoveItems(Text.Split([Data]," "),{""})," ")),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Added Custom", "Text.ExcelTrim", "Text.ExcelTrim - Copy"),
    #"Replaced Value1" = Table.ReplaceValue(#"Duplicated Column1"," ","*",Replacer.ReplaceText,{"Text.ExcelTrim - Copy"})
in
    #"Replaced Value1"

 Note - I initally published this formula on my personal blog @ https://eforexcel.com/wp/excel-trim-formula-in-power-query/ . This article is more explanatory and provides more details alongwith example query with samples.