Trim and Clean All Text Columns in Power Query M

Applies trim and clean to every text column in a table at once, without naming columns individually.

Data CleaningIntermediatetrimcleanall columnstextbulk

Power Query M code

let
    Source = YourTable,
    TextColumns = List.Select(Table.ColumnNames(Source), each Type.Is(Value.Type(Table.Column(Source, _)), type text)),
    Cleaned = Table.TransformColumns(Source, List.Transform(TextColumns, each {_, each Text.Clean(Text.Trim(_)), type text}))
in
    Cleaned

Notes

This detects text columns automatically, so it keeps working if columns are added or removed.

Requirements

How to use this snippet

  1. In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
  2. Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
  3. Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Trim and Clean All Text Columns.