textTrim M function | Normalize whitespace and punctuation spacing in strings.

textTrim.pq

let
    fxTrim = (string as nullable text, optional addPunctuationMarks as text) as text =>
        [
            baseMarks = ".,;:?!'-–—_",
            marks = Text.ToList(baseMarks & (addPunctuationMarks ?? "")),   
            clean = Text.Combine(
                List.Transform(
                    Splitter.SplitTextByCharacterTransition(each true, marks)(string),
                    Text.Trim
                )
            ),
            trim = Text.Combine(
                List.RemoveMatchingItems(
                    Splitter.SplitTextByWhitespace()(clean),
                    {""}
                ),  " "
            )
        ][trim],
    trimType = type function (
        string as (type nullable text meta [
            Documentation.FieldCaption     = "Input Text",
            Documentation.FieldDescription = "The text value to normalize",
            Documentation.SampleValues     = {"  Hello ,   World !  ", null}
        ]),
        optional addPunctuationMarks as (type text meta [
            Documentation.FieldCaption     = "Additional Punctuation Marks",
            Documentation.FieldDescription = "Optional string of characters to also treat as punctuation marks.",
            Documentation.SampleValues     = {"/"}
        ])
    ) as text meta [
        Documentation.Name            = "fxTrim",
        Documentation.LongDescription = "Normalize text by eliminating excess whitespace and unnecessary spaces around punctuation marks. Built-in punctuation marks include . , ; : ? ! ' - – — _. Additional punctuation characters can be specified in the second argument.",
        Documentation.Category        = "Text",
        Documentation.Version         = "1.00: Initial Version",
        Documentation.Author          = "Melissa de Korte",
        Documentation.Examples        = {
            [
                Description = "Trim excess spaces",
                Code        = "fxTrim(""  Hello ,   World !  "")",
                Result      = "Hello, World!"
            ],
            [
                Description = "Handle an additional punctuation mark",
                Code        = "fxTrim(""   and   /   or "", ""/"")",
                Result      = "and/ or"
            ],
            [
                Description = "Handle null",
                Code        = "fxTrim(null)",
                Result      = ""
            ]
        }
    ]
in
    Value.ReplaceType(fxTrim, trimType)