cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Super User IV
Super User IV

Re: Trim trailing spaces of rows

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Advocate II
Advocate II

Re: Trim trailing spaces of rows

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?

Highlighted
Super User IV
Super User IV

Re: Trim trailing spaces of rows

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Advocate II
Advocate II

Re: Trim trailing spaces of rows

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.
Highlighted
Microsoft
Microsoft

Re: Trim trailing spaces of rows

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.
Highlighted
Advocate II
Advocate II

Re: Trim trailing spaces of rows

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.

Highlighted
Advocate III
Advocate III

Re: Trim trailing spaces of rows

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. 

New Member

Re: Trim trailing spaces of rows

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

Highlighted
Super User I
Super User I

Re: Trim trailing spaces of rows

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors