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.
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.
Enjoy & stay queryious 🙂