Table.NormalizeTextColumns

This function cleans and formats text columns in a table. It removes line breaks, non-standard spaces, duplicated spaces, and applies optional casing (Proper, Lower, or Upper). You can specify which columns to process or let the function automatically detect all text columns.

Syntax

Table.NormalizeTextColumns(
    tbl as table,
    optional columnNames as list,
    optional textCasing as text
) as table

Parameters

• tbl: The input table containing text columns to be cleaned and formatted.
• columnNames: (optional) A list of column names to be processed. If not provided or empty, all columns of type text or nullable text will be processed.
• textCasing: (optional) A string indicating the desired text casing format. Accepted values are:
  • "Proper": Capitalizes the first letter of each word.
  • "Lower": Converts all texts to lowercase.
  • "Upper": Converts all texts to uppercase.
  • If not specified, casing is not changed.

Remarks

• The function replaces line feed characters (#(lf)) with spaces.
• It removes non-breaking spaces (Character.FromNumber(160)), trims leading/trailing spaces, and collapses multiple spaces into one.
• This function is useful for preparing text data for analysis, comparison, or display.

Examples

Example 1: Clean all text columns

let
    Source = #table(
        {"Name", "Comment"}, {
        {"  JOHN DOE  ", "Hello#(lf)World"},
        {"  jane smith", "Nice to meet you"}
    }),
    Result = Table.NormalizeTextColumns(Source)
in
    Result

Result

Name	Comment
JOHN DOE	Hello World
jane smith	Nice to meet you

Example 2: Clean and apply Proper case to selected columns

let
    Source = #table(
        {"Name", "Note"}, {
        {"  MARIA   clara", "great#(lf)job"},
        {"joão   SILVA", "excellent work"}
    }),
    Result = Table.NormalizeTextColumns(Source, {"Name", "Note"}, "Proper")
in
    Result

Result

Name	Note
Maria Clara	Great Job
João Silva	Excellent Work