cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
richard-powerbi
Post Patron
Post Patron

Dynamically rename columns from a column names table

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?

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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 

SU18_powerbi_badge

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

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 

SU18_powerbi_badge

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors