How can I Trim all column names at once

[dmoskowitz]

I would like to add a step in my Power Query data load Applied Steps that trims the beginning and ending spaces in all the columns.

Below is an example of a table where you can see that there are spaces before and after the column names. I would like to remove them but keep the spaces that are between words. For example "   Transaction Type    " woukld be transformed to "Transaction Type".

My data has many more columns so I would like to apply it in one shot and not have to reference each column by name.

Entity	Enter Date	Date	Transaction Type	Document	Type	GL Reference	Amount
USNY	2/4/2020	2/4/2020	IC	manual	1212121	gl1	2
USNY	2/4/2020	2/4/2020	IC	manual	1212121	gl1	2
USNY	2/4/2020	2/4/2020	IC	manual	1212121	gl1	2

Thank you

[Greg_Deckler]

@dmoskowitz @ImkeF and @edhans Is there a way when doing a rename for a column to remove spaces automatically? But then could you do that for all columns?

[dmoskowitz]

@Greg_Deckler  but not just remove all the spaces, just the spaces in the beginning and the end. I want to keep the spaces that are in the middle of the column name. So "User ID" should not become "UserID".

[ImkeF]

Hi @dmoskowitz ,

there is a dedicated function in M for this: Table.TransformColumnNames

It applies a transformation function to all column names. What you need is the Text.Trim function, that eliminates all leading and trailing spaces:

let
    Source = #table ( { "    asdf   "}, {{"Hello"}}),
    Custom1 = Table.TransformColumnNames( Source, Text.Trim)
in
    Custom1

[smpa01]

@ImkeF  this is awesome !!! how can I also throw a Text.Clean in there

Current code which is working

Table.AddColumn(
  #"Filtered Rows", 
  "Custom", 
  each 
    let
      x = Excel.Workbook([Content])[Data]{0}, 
      y = Table.PromoteHeaders(x, [PromoteAllScalars = true])
    in
      Table.TransformColumnNames(Table.TransformColumnNames(y, Text.Trim), Text.Clean)
)

Is it possible to combine TRIM and CLEAN inside a single Table.TransformColumnNames

Something like - Table.TransformColumnNames( y, Text.Trim & Text.Clean))

Thank you in advance

++@AlexisOlson @CNENFRNL 

[AlexisOlson]

@smpa01 You can nest them:

Table.TransformColumnNames(y, each Text.Trim(Text.Clean(_)))

Because this combination isn't an already-existing function, you have to use the anonymous function / lambda expression formulation to tell it what transformation to do instead of simply referring to a function by name.

[dmoskowitz · Accepted solution]

@ImkeF 

Cool! So I think that worked. In terms of transofrmation steps, I have the below.  

Source

Promoted Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true])

TrimColumnNames = Table.TransformColumnNames(#"Promoted Headers", Text.Trim)   --> in this step, do I have to put #"Promoted Headers" or is there a more general text I can put it? (Sorry if I am double dipping in this question)

Thank you for your help.

[v-xuding-msft]

Hi @dmoskowitz,

It seems that you have resolved this issue. Can you please accept the helpful answer as solution? Others who have the same questions will benefit from this thread. Thanks!

[ImkeF]

Hi @dmoskowitz ,

no worries.

#"Promoted Headers" is not just a text.

It is a reference to a variable (the previous step named "Promoted Headers").

That variable holds a table.

So you can replace the string " #"Promoted Headers"  " with any reference to a table (variable). 
For example, if the table you want to apply this function to is actually a different query, you put the name of the query in there.