Apply a function to all columns in a query

O

omezquita

New member
Joined
Apr 1, 2016
Messages
19
Reaction score
0
Points
0
Website
www.masterdataanalysis.com
Excel Version(s)
2016
Is there a way to apply a function to all columns in a query?

Right now I have all the values hard coded.

E.g.
HTML: 
= Table.TransformColumns(#"Expanded Data",{{"Name", ExcelTrim}, {"Column1", ExcelTrim}, {"Column2", ExcelTrim}, {"Column3", ExcelTrim}, {"Column4", ExcelTrim}, {"Column5", ExcelTrim}, {"Column6", ExcelTrim}, {"Column7", ExcelTrim}, {"Within2", ExcelTrim}, {"Column8", ExcelTrim}, {"Column9", ExcelTrim}, {"Column10", ExcelTrim}, {"Between2", ExcelTrim}, {"Within3", ExcelTrim}})

I would like something like:

HTML: 
= Table.TransformColumns(#"Expanded Data", All_Columns, ExcelTrim)

Is this possible?

Thanks!
 
Yes, just a slight change to your pseudocode.

For any columns not mentioned in the 2nd argument, the 3rd argument will be used as the default transformation function.
So if you supply an empty list as the 2nd argument, the 3rd argument will be applied to all columns.

e.g.
Code: 
= Table.TransformColumns(#"Expanded Data", {}, ExcelTrim)
 
Excellent Owen!!!
Thanks!

Follow up question:

It is possible to apply a function that will rename all columns?

For example, to replace all spaces with "_" in all column names.

Thanks!
 
There may be a function to do as you request - but if this is a one-off requirement you may already know the real quick and dirty way is to demote the header, transpose the data, run a search/replace on the offending column, then transpose back and promote the updated header column.
 
Hi omezquita,
I totally missed your follow up question - sorry!

As an example, you could do something like:

Code: 
= Table.RenameColumns( [B]PreviousStep[/B], List.Transform( Table.ColumnNames( [B]PreviousStep[/B] ), each { _, Text.Replace( _, " ", "_" ) } ) )

In this case, List.Transform(...) produces a list where each item in the list is a list of the form {OldName, NewName}.
 
You must log in or register to reply here.
Back
Top