Trim trailing spaces of rows

[Gangula]

Can we trim trailing spaces of the 1st row?

Or is it possible with the header row?

[Jimmy801]

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]

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]

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

[Gangula]

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?

[Greg_Deckler]

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

[Gangula]

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.

[v-jiascu-msft]

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

[Gangula]

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.

[aviral]

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]

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

[donder]

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