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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Regular 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors