Efficiently rename columns with a function in Power BI and Power Query

Edit 2018-July-05: As it turns out I’ve missed the native function that exists with exact the same functionality: Table.TransformColumnNames Thanks to Tristan Malherbe for pointing this out 🙂

But anyway: If you want to understand what it does and how you could have written it by yourself – read along:

The standard function to rename columns in the query editor of Power BI or Power Query is Table.RenameColumns. It takes a nested list of old and new column names as its second argument. But I often come across scenarios where I would like the new column names to be created dynamically. Therefore I’ve created a function (Table.RenameColumnsWithFunction) that takes a text transformation function as its argument instead of a hardcoded list of values:

Problem

Say you’re importing tables where the table name is part of each column name like so:

Old column names for Table.RenameColumnsWithFunction

 

 

But you only want to maintain everything after the TABLENAME.:

New column names for Table.RenameColumnsWithFunction

 

 

 

One way would be to replace “TABLENAME.” by nothing (“”):

Table.RenameColumnsWithFunction

This function allows you to pass the function as a second argument instead of a hardcoded list of new names like so:

Table.RenameColumnsWithFunction

 

 

 

 

 

As you can use any appropriate function, an alternative could also be to use is like this for example:

Table.RenameColumnsWithFunction( MyTable, each Text.BeforeDelimiter(_, ".") ) 

So the underscore (_) stands for each column name of the table that you’re passing in as the 1st parameter of the function.

Function Code


let func =
(Table as table, MyFunction as function) =>
let
OldColumnNames = Table.ColumnNames(Table),
NewColumnNames = List.Transform(OldColumnNames, MyFunction),
RenameList = List.Zip({OldColumnNames, NewColumnNames}),
RenameColumns = Table.RenameColumns(Table, RenameList)
in
RenameColumns ,
documentation = [
Documentation.Name = " Table.RenameColumnsWithFunction ",
Documentation.Description = " Returns a <code>Table</code> with the column names transformed according to <code>MyFunction</code> from the 2nd parameter ",
Documentation.LongDescription = " Returns a <code>Table</code> with the column names transformed according to <code>MyFunction</code> from the 2nd parameter ",
Documentation.Category = " Table ",
Documentation.Source = " ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann: www.TheBIccountant.com . ",
Documentation.Examples = {[Description = " see this blogpost: https://wp.me/p6lgsG-UZ ",
Code = " let
Table = #table( {""TABLENAME.FirstColumn"", ""TABLENAME.SecondColumn""}, { {""A"" ,""B""} } ),
FunctionCall = fnTableRenameColumnsFunction(Table, each Text.Replace(_, ""TABLENAME."", """"))
in
FunctionCall ",
Result = " #table( {""FirstColumn"", ""SecondColumn""}, { {""A"" ,""B""} } ) "]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

view raw

Table.RenameColumnsWithFunction.pq

hosted with ❤ by GitHub

If you don’t know how to handle custom functions, check out this video: https://www.youtube.com/watch?v=6TQN6KPG74Q

How it works

Details to row numbers:

  • 4: The old column names are retrieved and returned as a list
  • 5: Each item from that list will be transformed with the transformation function that has been passed in as the 2nd parameter
  • 6: Both lists are zipped to be in the required shape for the native function “Table.RenameColumns”
  • 7: The native function “Table.RenameColumns” is applied to perform the desired renaming operation
  • 10 ff: This is just function documentation that will appear when you address this function in the program

Hope this will help you one day, so

enjoy & stay queryious 😉

Comments (16) Write a comment

  2. Hi. You could use also this function:

    (Source as table, ColumnMappng as table) =>
    let
    #”Renamed Columns” = Table.RenameColumns(Source, Table.ToRows(ColumnMappng))
    in
    #”Renamed Columns”

    All you need to do is define the table you need to rename, and mapping table where are matching column names in 1’st column, and renamed columns in 2’nd column. It will rename columns where there is mapping available. Unknown / not matched columns will not be renamed.

    You can use it to define new renamed table or use it inside the query step.
    Thanks 🙂

    Reply

  4. Can you provide an example of a function to type into the 2nd parameter (exactly as you would enter it)? I can’t get anything to work….it all just says “enter a function value” and the “invoke” box is grayed out.

    Reply

    • Hi Jared,
      this would be one possible function: each Text.BeforeDelimiter(_, “.”)
      Don’t forget the “each” at the beginning.
      An alternative way of writing this would be : (myFunctionParameter) => Text.BeforeDelimiter(MyFunctionParameter, “.”)

      Hope this works for you?
      /Imke

      Reply

  5. I tried copying and pasting both of those in the “MyFunction” field after selecting the appropriate table. Neither seems to work. The “invoke” box is still greyed out and I get the warning that says “Enter a function value”. Not sure what I’m doing wrong.

    Reply

    • Hi Jared,
      are you able to share the code of your query (from the advanced editor)?
      /Imke

      Reply

  6. Dear Imke

    I have same issue as jared

    Input Table: SSOS [One of my existing queries]

    Input MyFunction: each Text.BeforeDelimiter(_, “.”)

    The MyFunction raise an error (Yellow Triangle): “Enter a Function value”
    Invoke button is grayed out

    Reply

  7. I got it now!
    If the columns are not consistent with a delimiter such as the below example, then function will fail

    Col1: Site
    Col2: AllRows.Item
    Col3: AllRows.Name
    Col4: Warehouse

    using the function: each Text.BeforeDelimiter(_, “.”) or each Text.AfterDelimiter(_, “.”) with the above tbale will fail.
    But will work with: each Text.Replace(_, “AllRows.”,””)

    Reply

  8. I have AS400 cryptic columns and I have an excel sheet with all as 400 columns and their description that I would like to use to replace or enrich the cryptic columns for end user consumption.

    Reply

  9. I’m having the exact same issue – Is there a format the MyFunction has to be in?

    I have my table selected from the dropdown and in the MyFunction box I have: each Text.Replace(_, “Students.”,””) – but I still get myFunction Invoke greyed out.

    Reply

  10. Pingback: Dynamic Column Names in Power Query - Goodly

Leave a Reply