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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors