Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Amar-Agnihotri
Resolver I
Resolver I

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

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. 

3 ACCEPTED SOLUTIONS
Vera_33
Resident Rockstar
Resident Rockstar

Hi @Amar-Agnihotri 

 

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

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

View solution in original post

Migasuke
Super User
Super User

Hi @Amar-Agnihotri ,

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



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

View solution in original post

CNENFRNL
Community Champion
Community Champion

 

#"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.

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

7 REPLIES 7
BehnamEbrahimi
Frequent Visitor

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
Community Champion
Community Champion

 

#"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.

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Migasuke
Super User
Super User

Hi @Amar-Agnihotri ,

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



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

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
Resident Rockstar
Resident Rockstar

Hi @Amar-Agnihotri 

 

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

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

Excellent! It works very well!

This is the simplest and most dynamic solution

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors