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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Gangula
Advocate II
Advocate II

Trim trailing spaces of rows

Can we trim trailing spaces of the 1st row?

Or is it possible with the header row?

11 REPLIES 11
Jimmy801
Community Champion
Community Champion

Hello @Gangula 

I've written you this customfunction that trims the column header names

(tTable as table) as table =>
let
    Columnheaders = Table.ColumnNames(tTable),
    TrimList = List.Transform(Columnheaders, each Text.Trim(_)),
    RenameHeaders = Table.RenameColumns(tTable, List.Zip({Columnheaders, TrimList}))
in
    RenameHeaders

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

donder
New Member

I just split the data into the header row (keep 1st row) and the data rows (remove 1st row). Then I transposed the header, trimmed the data, and transposed back. Then it is just a matter of appending the header and data rows and proceeding.

 

I hope this helps.

David

Greg_Deckler
Super User
Super User

Not rows, but columns. Transform -> Text Transform -> Trim


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Can we do the same thing with Rows? I have a set of excel files with tables having the same name which I have to merge. But I've noticed that few of the tables have trailing spaces at the column headers. So when I merge, they show up as 2 different columns.

 

If I can use trim on rows, I can demote the headers and the trim the 1st row and then promote the headers back.

Is there any other simpler solution to this?

Well, if you trim the columns, you are trimming all of the rows, is that an issue?


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

It's not an issue, but trimming a column would trim all the rows as you said. And if the number of rows increases, it would take more time to trim.

And the issue in my case is not with the columns, it's with the "column header names". It's not just with one column, there are multiple columns.
Demoting all the column headers and trimming all the columns is not an option either as the number of columns in the query might change.

Hi @Gangula,

 

I would suggest you do it manually. Double click the title and delete the spaces. Solve it in the beginning.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I do agree with you Dale, but I have more such queries(from multiple files) with even more number of columns which I need to merge into one. And there might also be few more new queries which I need to check. So I was looking for a simpler way to do it.

Did you find a solution to it. I am facing the same issue.

 

I have multiple tables wich I am combining and it is throwing errors and upredictable behavior due to leading/training spaces in the column names of different files. 

Jimmy801
Community Champion
Community Champion

Hello @aviral 

I've written you a custom function for it to trim header names

(tTable as table) as table =>
let
    Columnheaders = Table.ColumnNames(tTable),
    TrimList = List.Transform(Columnheaders, each Text.Trim(_)),
    RenameHeaders = Table.RenameColumns(tTable, List.Zip({Columnheaders, TrimList}))
in
    RenameHeaders

 

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

This will be much faster and is cleaner. Thanks. Not sure how to mark it as a solution but I did give you kudos.

 

David

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.