Automatical or Bulk- Rename Columns in Power BI and Power Query

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 ๐Ÿ™‚

Comments (16) Write a comment

  1. The file could not be downloaded. The error message read : No file paths defined. Go to homepage โ†’

    Reply

  2. 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?

    Reply

    • 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.

      Reply

      • 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

        Reply

        • 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 ” “&_ ))))

          Reply

          • Always better together ๐Ÿ™‚
            Nice name! – will post there in the evening

  3. 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

    Reply

  4. Pingback: How List.Zip helps with Renaming Columns in Power Query – Ivan Bond's blog

  5. Pingback: Easy way to auto rename columns in Power Query

Leave a Reply