Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Can we trim trailing spaces of the 1st row?
Or is it possible with the header row?
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
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
Not rows, but columns. Transform -> Text Transform -> Trim
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?
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |