Apply a function to all columns in a query

[omezquita]

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.

= 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:

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

Is this possible?

Thanks!

 

[Owen Auger]

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.

= Table.TransformColumns(#"Expanded Data", {}, ExcelTrim)

 

[omezquita]

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!

 

[Fran McLoughlin]

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.

 

[Owen Auger]

Hi omezquita,
I totally missed your follow up question - sorry!

As an example, you could do something like:

= 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}.

 

[omezquita]

Perfect!

Thanks!!!