Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors