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

[vancromy]

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

[ImkeF]

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.

[dumpguy]

Many thanks @ImkeF .

This was the solution. 😃

[Greg_Deckler]

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

[ImkeF · Accepted solution]

You were pretty close already:

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

[thebestPBI]

The solution working fine, thank you for sharing

[dumpguy]

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:

While ProjectLables_indexed  returns this table:

respectively  rename_table  returns:

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

or

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:

 

However, combining this into

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

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

[ChemEnger]

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!

[Luthiendsa]

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.

[ImkeF]

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.

[vancromy]

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