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))

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 (15) Write a comment

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz