Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
73 | |
67 | |
42 | |
28 | |
20 |