Dynamically rename a set of columns using Power Query

Question

I am trying to dynamically rename a set of columns in Power Query, List1 being the original column names and List2 the new column names. I think I need to merge List1 and List2 into a single list of pairs, but can't figure out the correct syntax.

Many thanks!

    let

    //list of original column names 
    List1= {"Name1","Name2","Name3","Name4"},

    //Create test table
    Source = Table.FromRows({{1231,1233,4121,5232},{3546,3426,1246,3464}} , List1),

    //list of new column names
    List2 = {"NewName 1","NewName 2","NewName 3","NewName 4"},

    //Rename columns (in practice, the two lists of names will be dynamic, not hard coded as below)
    Result = Table.RenameColumns(Source, {
        {"Name1","NewName 1"},
        {"Name2","NewName 2"},
        {"Name3","NewName 3"},
        {"Name4", "NewName 4"}})

    in
        Result

Ivan Bond · Accepted Answer · 2016-01-20 15:05:11Z

4

If you have a table with old and new names then you can use following pattern

let
    rename_list = Table.ToColumns(Table.Transpose(Table2)),
    result = Table.RenameColumns(Table1, rename_list, MissingField.Ignore)
in result

where Table2 is "Rename Table" and Table1 is initial table with data.

This idea is described in details here https://bondarenkoivan.wordpress.com/2015/04/17/dynamic-table-headers-in-power-query-sap-bydesign-odata/

answered Jan 20, 2016 at 15:05

Ivan Bond

612 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Brian Mather Over a year ago

Thanks Ivan, I'll take a look at this.

Brian Mather Over a year ago

I like it - it will always pair up the correct oldname/newname pairs. The Table.ToColumns(Table.Transpose...) solves the "I need to merge List1 and List2 into a single list of pairs" nicely as the second argument for Table.RenameColumns. I can skip the Table.Transpose by using Table2 = Table.FromRows({List1,List2}). I'll step it out in my original problem for completeness.

Brian Mather Over a year ago

Thanks Ivan, I've restated the original example using your solution. Many thanks for your help

Carl Walsh · Accepted Answer · 2016-01-18 22:25:44Z

1

If you have the resulting column names you want, it seems like you could convert Source back to rows, then call Table.FromRows on List2

let

    //list of original column names 
    List1= {"Name1","Name2","Name3","Name4"},

    //Create test table
    Source = Table.FromRows({{1231,1233,4121,5232},{3546,3426,1246,3464}} , List1),

    //list of new column names
    List2 = {"NewName 1","NewName 2","NewName 3","NewName 4"},

    Result = Table.FromRows(Table.ToRows(Source), List2)

in
    Result

(Unless it is wrong to assume that e.g. Name 2 will always be the second column.)

answered Jan 18, 2016 at 22:25

Carl Walsh

7,2373 gold badges53 silver badges59 bronze badges

3 Comments

Brian Mather Over a year ago

Thanks Carl, I'll take a look at this. It certainly looks a lot simpler than my solution!

Brian Mather Over a year ago

I like how Table.ToRows strips out the column names and Table.FromRows rebuilds the table again with List2 as the column new names. As you say, the replacement column names would need to be listed in the correct order within List2.

Brian Mather Over a year ago

I've gone with Ivan's answer because in production, I have some columns that I don't want to rename plus I was trying to figure out how to use the Table.RenameColumns function properly. I'll learnt a lot from stepping through your solution, thank you!

Sergey Lossev · Accepted Answer · 2016-04-04 02:58:26Z

Stating the original problem according to Ivan's solution, here goes. Carl's has the same result and is a little simpler for the example I gave, however, my situation will benefit from having the rename pairs set out explicitly in a table (ie. Table2). Plus using the MissingField.Ignore parameter with Table.RenameColumns means that it will only change the selection of columns I want to rename in my production query, the rest will remain unchanged.

let

    //list of original column names 
    List1= {"Name1","Name2","Name3","Name4"},

    //Create test table
    Source = Table.FromRows({{1231,1233,4121,5232},{3546,3426,1246,3464}} , List1),

    //list of new column names
    List2 = {"NewName 1","NewName 2","NewName 3","NewName 4"},

    //Rename columns (in practice, the two lists of names will be dynamic, not hard coded as below)

    //Bring List1 and List2 together as rows in a table
    Table2 = Table.FromRows({List1,List2}),

    //Create a list of rename pairs 
    RenameList = Table.ToColumns(Table2),

    //Call to Table.RenameColumns 
    Result = Table.RenameColumns(Source, RenameList, MissingField.Ignore)

in
    Result

Brian Mather · Accepted Answer · 2016-01-26 16:26:43Z

0

Finally... figured it out using the following function

Table.TransformColumnNames(table as table, nameGenerator as function, optional options as nullable record) as table

First create a nameGenerator function (e.g. MyFuncRenameColumns) to provide a new column name given any original column name as an input.

In my example, here's my code for MyFuncRenameColumns:

    let
        MyFunctionSwitchColumnName = (originalColumnName) as text =>
        let
            //list of original column names 
            List1= {"Name1","Name2","Name3","Name4"},

            //Create table
            Source = Table.FromRows({{1231,1233,4121,5232},{3546,3426,1246,3464}} , List1),

            //list of new column names
            List2 = {"NewName 1","NewName 2","NewName 3","NewName 4"},

            //Create table matching List1 to corresponding new value in List2
            CreateRecord = Record.FromList(List2,List1),
            ConvertedtoTable = Record.ToTable(CreateRecord),

            //Filter table to just the row where the input originalColumnName matches 
            ReduceExcess = Table.SelectRows(ConvertedtoTable, each [Name] = originalColumnName),

            //Return the matching result in the [Value] column (or give the original column name if there was no valid match)
            NewColumnName = try ReduceExcess{0}[Value] otherwise originalColumnName

        in 
            NewColumnName
    in
        MyFunctionSwitchColumnName

Here's where you use it as one of the parameters for Table.TransformColumnNames:

    let
        //list of original column names 
        List1= {"Name1","Name2","Name3","Name4"},

        //Create table
        Source = Table.FromRows({{1231,1233,4121,5232},{3546,3426,1246,3464}} , List1),

        RenameColumns = Table.TransformColumnNames(Source, MyFuncRenameColumns)
    in
        RenameColumns

Hope that helps someone!

edited Jan 26, 2016 at 16:26

answered Jan 18, 2016 at 19:33

Brian Mather

611 gold badge1 silver badge7 bronze badges

2 Comments

Oğuz Yıldız Over a year ago

you said "TransformColumnTypes", did you mean TransformColumnNames?

Brian Mather Over a year ago

Unless you have some complex requirements for the function to rename the columns, my solution here is overkill and too complex. It is nice to know the Table.TransformColumnNames function exists, but if you just have a straight column name switch then both Ivan and Carl's answers are much simpler.

Collectives™ on Stack Overflow

Dynamically rename a set of columns using Power Query

4 Answers 4

3 Comments

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related