Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dmoskowitz
Helper I
Helper I

How can I Trim all column names at once

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  
USNY2/4/20202/4/2020ICmanual1212121gl12
USNY2/4/20202/4/2020ICmanual1212121gl12
USNY2/4/20202/4/2020ICmanual1212121gl12

 

Thank you

1 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.

 

 

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

@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?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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".

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@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 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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.

@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.

 

 

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!

 

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.