Edit 7th Feb 2017: Friendly reader Roknic pointed out in the comments below that there’s actually an existing function for it in M: Table.TransformColumnNames 🙂
So the first of my example below would actually look like this:
Table.TransformColumnNames(Source, each Text.Replace(_, " ", "_"))But still keeping my original post here, as the transformations in them might help for other use cases:
If you want to rename all of your table’s columns with a common rule, like “replace all spaces by underscore” or just “delete all spaces”, check out this easy method:
The above formula will replace all spaces (” “) by underscores (“_”).
How does it work:
The 2nd argument in the Table.RenameColumns-formula is a list of lists, just like in Table.TransformColumnType from this article. So we apply the same technique here: List.Transform transforms a single element from a list into a list-item, whose 2nd argument will be calculated with a Text.Replace-function.
Variations:
Rename Columns Variations
Only replace FirstN or LastN elements from the column names:
If this rings a bell: Congrats, you might be a real fan and have probably read this Datachant-article, which uses List.Zip for this task. That’s a good method if your new column names cannot be derived based on a rule like above but have individual values. And it’s also good fun, because List.Zip is a really cool function 🙂
Replace only specific positions:
This is a bit of an unusual construct and I wouldn’t be surprised if there’s a more straightforward way to do it (maybe using List.Positions?): You transform the list of positions that you pass as the 1st argument by taking the list of the fully replaced headers and passing each position as a row-selector to it. Because we want to write the positions as numbers how humans count, we have to subtract 1 from the current position-element, as M starts to count at zero.
The main use case I see for my method really is to quickly eliminate all blanks in your column names, because they can prevent easy formula-editing, like you will see in an upcoming article.
File for subscribers to download: BulkRenameColumns
Enjoy & stay queryious 🙂
The file could not be downloaded. The error message read : No file paths defined. Go to homepage →
Thx for the hint Julian, pls try again.
It’s ok now. But you still forgot to add a hyperlink on your file connecting your blog. I do believe it’s a way to promote your popularity.
🙂 – fixed that as well – thx again !
I actually need to add spaces to make them user friendly. The Odata feed I’m using provides CamelCase column names. Any thoughts on how that could be done?
Hi Treb, that’s a nice twist 🙂
Yes, you can use this formula to create the list with the new column names:
List.Transform(Table.ColumnNames(Source), each Text.Trim(Text.Combine(List.Transform(Text.ToList(_), each if Character.ToNumber(_)>=65 and Character.ToNumber(_)<=90 then " "&_ else _)))) It splits up the strings into single characters -> checks if their number-representation is within the range of the capital letters -> combines those with a blank -> reassembles. And trims the first blanks.
HI Imke!
It seems like culture is hard coded there. For Cyrillic words it should be different charset, etc.
What if we’ll compare two lists: one original characters from string and one after Text.Lower. Then on positions with non-equal values we’ll insert spaces (moving from the end of string to the beginning).
Cannot test it now, but it should be more universal.
Maxim
Thx a lot Maxim!
Of course this went through my head as well, but I couldn’t find this elegant alternative 🙂
Works perfect:
= List.Transform(Table.ColumnNames(Source), each Text.Trim(Text.Combine(List.Transform(Text.ToList(_), each if _=Text.Lower(_) then _ else ” “&_ ))))
Excellent! Much more elegant, than supposed by me.
BTW, inner part of it could be placed on GitHub as Text.SplitByCaps 🙂
Always better together 🙂
Nice name! – will post there in the evening
Nice! Thanks, I’ll have to try this out.
Function Table.TransformColumnNames does the same thing. This example turn column names “A A” and “B B” into “A_A” and “B_B”.
let
Source = #table({“A A”,”B B”}, { {1,2}, {3,4} }),
BlankToSpace = Table.TransformColumnNames( Source, each Text.Replace( _, ” “, “_”) )
in
BlankToSpace
🙂 How nice – thanks Roknic!
Pingback: How List.Zip helps with Renaming Columns in Power Query – Ivan Bond's blog
Awesome, worked for me. Thank you.
Pingback: Easy way to auto rename columns in Power Query