Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to Solution.
Try it before Change your column type from any to any other types
Table.TransformColumns(yourPreviousStep,{}, Text.Trim)
Hi @Amar-Agnihotri ,
just select all columns (ctrl + A) - go to Transform and select Trim.
Trim will apply on all columns.
#"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! |
<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>
#"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! |
Hi @Amar-Agnihotri ,
just select all columns (ctrl + A) - go to Transform and select Trim.
Trim will apply on all columns.
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.
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