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

Answer 1

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/

Answer 2

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

Answer 3

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

Answer 4

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!