Is there any way to Trim all the Column Values in Power query in one shot?

[Amar-Agnihotri]

I want to trim all column values in one shot because there are approx 50 columns in Excel and can't do it one by one. 

[Vera_33 · Accepted solution]

Hi @Amar-Agnihotri 

Try it before Change your column type from any to any other types

Table.TransformColumns(yourPreviousStep,{}, Text.Trim)

[Migasuke · Accepted solution]

Hi @Amar-Agnihotri ,

just select all columns (ctrl + A) - go to Transform and select Trim.
Trim will apply on all columns.

[BehnamEbrahimi]

If you want to Trim, Clean and Capitalise all columns with text type, you can use below code: 

<P>
let
  Source = ** Your Table Name **,
    Schema = Table.Schema(Source),
    #"Select_Cols" = Table.SelectColumns(Schema,{"Name", "Kind"}),
    #"Filter_Text_Cols" = Table.SelectRows(#"Select_Cols", each ([Kind] = "text")),
    #"Select_Cols_Name" = Table.SelectColumns(#"Filter_Text_Cols",{"Name"}),
    Cols_List = #"Select_Cols_Name"[Name],
    _Cols_Trimed = List.Accumulate(Cols_List, Source, (s,c) => Table.TransformColumns(s, {c, Text.Trim})),
    _Cols_Cleand = List.Accumulate(Cols_List, Source, (s,c) => Table.TransformColumns(s, {c, Text.Clean})),
    _Cols_Capitalised = List.Accumulate(Cols_List, Source, (s,c) => Table.TransformColumns(s, {c, Text.Proper}))

in
    _Cols_Capitalised
</P>

[CNENFRNL · Accepted solution]

#"Trimmed All Columns" = let cols = Table.ColumnNames(#"previous step") in List.Accumulate(cols, #"previous step", (s,c) => Table.TransformColumns(s, {c, Text.Trim}))

btw, @Migasuke good to know the trick, I didn't remember when was the last time when I used buttons on the function ribbon since I always write code with the help of wonderful intellisence in PQ.

[Migasuke · Accepted solution]

Hi @Amar-Agnihotri ,

just select all columns (ctrl + A) - go to Transform and select Trim.
Trim will apply on all columns.

[Riveran21]

If all your columns are "text" based than this will work fine. If they are not all "text" based but a mixture of alpha and numeric data, there's an extra step in this process.

What I do is I select all columns as you indicated and perform the trim or clean. However, this will convert all columns (regardless of data type) to "text".

So the next step I perform is to select all columns again and choose the option "Detect Data Type" under the "Transform" tab. This should revert all your columns back to their correct data type after the trim is performed.

Of course, I think it goes without saying that you should do a quick glance over each column to make sure that they have the correct data type.

[Vera_33 · Accepted solution]

Hi @Amar-Agnihotri 

Try it before Change your column type from any to any other types

Table.TransformColumns(yourPreviousStep,{}, Text.Trim)

[telclisson]

Excellent! It works very well!

[JasonBreedlove]

This is the simplest and most dynamic solution