Dynamically rename columns from a column names table

[richard-powerbi]

Let's say I have a table with 3 columns: TableName, ColumnNameOld, ColumnNameNew.

I create two functions that filter TableName to the table I want, and I keep either ColumnNameOld or ColumnNameNew and returns a list.

Then I want to use these wo functions to rename columns. 

First I though I could do this:

Table.RenameColumns(Source, {{fOldColumnNames("TableName"), fNewColumnNames(("TableName")}})

But I quickly realized that didn't work. Because it uses multiple small lists for each column rename.

When talking about Table.ExpandRecordColumn, it could work, like this:

= Table.ExpandRecordColumn(Source, "Whatever", {"oldColumnName1", "oldColumnName2"}, {"NewColumnName1", "NewColumnName2"})

and thus:

= Table.ExpandRecordColumn(Source, "Whatever", {fOldColumnNames}, {fNewColumnNames})

Back to the problem with Table.RenameColumns, I realized I had to do this:

Table.RenameColumns(
  Source, 
  {
    {fOldColumnNames("TableName"){0}, fNewColumnNames("TableName"){0}}, 
    {fOldColumnNames("TableName"){1}, fNewColumnNames("TableName"){1}}, 
    {fOldColumnNames("TableName"){2}, fNewColumnNames("TableName"){2}}, 
    {fOldColumnNames("TableName"){3}, fNewColumnNames("TableName"){3}}, 
    {fOldColumnNames("TableName"){4}, fNewColumnNames("TableName"){4}}, 
    {fOldColumnNames("TableName"){5}, fNewColumnNames("TableName"){5}}, 
    {fOldColumnNames("TableName"){6}, fNewColumnNames("TableName"){6}}
  }
)

I feel like this can be done smarter. I don't like the fact that I manually have to write the code for each column. When the amount of columns changes I would have to manually add or remove rows. Does anyone have a suggestion?

[AlB · Accepted solution]

Hi @richard-powerbi 

im not sure i understand what exactly you need but perhaps List.Zip will be of help

Table.RenameColumns(
Source,
List.Zip( {fOldColumnNames("TableName"), fNewColumnNames("TableName") } )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[richard-powerbi]

Thank you! What a wonderful function! 🙂

This also has led me to this:

https://excel.city/2017/11/how-to-use-list-zip-in-power-query/

And this:

https://www.thebiccountant.com/2017/01/09/dynamic-bulk-type-transformation-in-power-query-power-bi-a...

Both very useful for these kind of things.