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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
vancromy
Frequent Visitor

PQ - Rename Column Names based of column names from another table

Hi.

 

I'm sure this is really simple but I can't seem to find the answer online. Many queries are about translating rows to Column Names but I'm specifically after how I can make the column names of my table equal to the ones of another table. I know this is dangerous because it requires the columns to be in the right order and no new columns to appear or old ones to dissapear but I will gladly take that risk on. 

 

Table 1: 

Company          Date            Sales

Bob Co       27/04/2018          20

 

Table 2: 

A                        B                    C

Bob Co       27/04/2016          30

 

I would like Table 2 to inherit Table 1's column names. I can't just hardcode it because the columnames in Table 1 can change.

 

I've tried the following but got this error: Expression.Error: We expected a RenameOperations value.

#"Rename Columns" = Table.RenameColumns(Table2, Table.ColumnNames(Table1))

 

Also tried

#"Renamed Columns" = Table.RenameColumns(Table2, Table.ToList(#table({"OldNames","NewNames"},{{Table.ColumnNames(Table2)},{Table.ColumnNames(Table1)}})))

 

but got this error: Expression.Error: 2 keys were specified, but 1 values were provided.

 

Any help would be much appreciated

Y

 

1 ACCEPTED SOLUTION

You were pretty close already:

 

#"Rename Columns" = Table.RenameColumns(Table2, List.Zip( { Table.ColumnNames(Table2), Table.ColumnNames(Table1) } ) )

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

10 REPLIES 10
ImkeF
Super User
Super User

Hi @dumpguy ,
the list of column names in the rename operation must have the same lenght. 
But your first table has the index column on it. So remove the index column before grabbing its column names and you should be good to go.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

dumpguy
Frequent Visitor

Many thanks @ImkeF .

This was the solution. 😃

Greg_Deckler
Super User
Super User

Dealing with multiple tables in a query is tricky outside of a Merge or Append but @ImkeF might have a trick for it.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

You were pretty close already:

 

#"Rename Columns" = Table.RenameColumns(Table2, List.Zip( { Table.ColumnNames(Table2), Table.ColumnNames(Table1) } ) )

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

The solution working fine, thank you for sharing

dumpguy
Frequent Visitor

Hello Imke,
I tried to use your code for a similar approach, but got the same error as 'vancromy': "Expression.Error: We expected a RenameOperations value".
As I´m quite a M-code beginner, I hope you´ve some time to bring me on the right track for a solution.

The code I used is:

rename_table = Table.PromoteHeaders(Table.Transpose(ProjectLabels_indexed), [PromoteAllScalars=true]),


#"Rename_Columns" = Table.RenameColumns(#"Reordered Columns", List.Zip( { Table.ColumnNames(#"ReorderedColumns"), Table.ColumnNames(rename_table) } ), MissingField.Ignore )

 

Where  #"Reordered Columns"  is the table result of the M-Code step before, looking like this:

dumpguy_5-1637834773086.png


While ProjectLables_indexed  returns this table:

dumpguy_2-1637833023145.png

respectively  rename_table  returns:

dumpguy_0-1637851580563.png

 

I know, rename_table  isn´t in the form as required, but transformations as this

dumpguy_1-1637852838546.png

or

dumpguy_2-1637852860046.png

 

on rename_table  gave the same error message at  #"Rename_Columns".


Maybe I should use a totally different method to replace columns 1 to 6 names of  #"Reordered Columns"   by the rows 1 to 6 values of column [DOW - ProjectLabel] in table  ProjectLables_indexed  , but I´ve no idea yet how to do it.

 

Many thanks in advance for your support.

Regards,
Stefan

 

EDIT:

Using hard coded pairs of renaiming, everything works fine:
#"Rename_Columns" = Table.RenameColumns(#"Reordered Columns", {{"1", "C300016"}, {"2", "CLIENT"}, {"3", "CON"}}, MissingField.Ignore ),

 

Using List-Zip

myListZip = List.Zip( { Table.ColumnNames(#"Reordered Columns"), Table.ColumnNames(#"Promoted Headers") } ),

returns what I think was intended to be returned inside Imke´s code:

dumpguy_1-1637914804142.png 

dumpguy_2-1637914865216.png

However, combining this into

#"Rename_Columns" = Table.RenameColumns(#"Reordered Columns", myListZip, MissingField.Ignore )

still returns Expression.Error: We expected a RenameOperations value.

An old thread but a good one.  @dumpguy, I've just been scratching my head with exactly this issue - we don't seem to be able to rename a column with an integer (or other numerical) type in the Index column, as created with a Table.AddIndexColumn command.

 

The Index column needs transforming to text in a separate command* and everything should work OK.

 

*note that just specifying type text as the index is created is not sufficient!

Hi @ImkeF,

 

Where do I put the " #"Rename Columns" = Table.RenameColumns(Table2, List.Zip( { Table.ColumnNames(Table2), Table.ColumnNames(Table1) } ) ) "?

I am a beginner in Power BI and I really could use the solution you gave.

 

Hi , @Luthiendsa ,

did you check the link from my signature already?: http://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-co...

Please let me if that makes sense or you need further advise.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Thanks both! That's done the trick and I've learnt about a new PQ function in the process 🙂

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.