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

Need Help

ImkeF

8 years ago

You were pretty close already:

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

10 Replies

Greg_Deckler
Community Champion
8 years ago
Dealing with multiple tables in a query is tricky outside of a Merge or Append but ImkeF might have a trick for it.
- ImkeF
  Community Champion
  8 years ago
  You were pretty close already:
  
  #"Rename Columns" = Table.RenameColumns(Table2, List.Zip( { Table.ColumnNames(Table2), Table.ColumnNames(Table1) } ) )
  - Anonymous
    Not applicable
    4 years ago
    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.
ImkeF
Community Champion
4 years ago
Hi Anonymous ,
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.
- Anonymous
  Not applicable
  4 years ago
  Many thanks ImkeF .
  This was the solution. 😃