Congruent Header Rename based on List

[Goodkat]

Dear Power Query enthusiasts,

Today I worked on the topic of automated header renaming in power query. And now I am struggling with the last step. I have a query on which the header names are incorrect. At the same time I have a list were I know the headers are correct. Both are congruent, i.e. Header for column 1 sits in [mdHeader]row 1, column 2 = row 2 and so forth.

Which command I would need to implement into the Table.RenameColumns command to achieve this? I searched the internet intensively and watched numerous videos over the past hours, but none could give me a clue how to perform this 'transposed copy rename' operation in one commandline in [Header] query.

Any idea from the community will be greatly appreciated!

https://c.gmx.net/@324888734501700174/6aAbqAZdQkKdWVkovk3c-w

Best regards, Andreas

[jennratten · Accepted solution]

Hello!  Here is how you can accomplish this.

If you have a table of data:

And a table of column headers:

You can rename them like so (for ease of understanding I have broken the actions into separate steps, but these can be combined into one step if preferred).  I have included two options - one for getting the old names from the source data and the other for getting the old names from the table which also contains the new names.

After the renaming is applied:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRTcEksKc1VcM4vKsgvSizJzM9TUNAI1QvW01TSUfJzVorViVbyBcoU6Si4FyVmJOYqJOalKLhkJmen5hUDlfi6KMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account Name" = _t, #"State or Province" = _t]),
    FieldNames = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOzi/NK1HwS8xNVdKBcZVidaKVgksSS1IV8osUAoryyzLzkkHyYDGl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [old = _t, new = _t]),

    // list of old names if they are coming from the source data.
    varOldNames = Table.ColumnNames ( Source ),

    // list of old names if they are in a table with the new names, aka a data map.
    // varOldNames = Table.Column ( FieldNames, "old" ),

    // list of new names
    varNewNames = Table.Column ( FieldNames, "new" ),

    // zip the old names and new names together into a list.
    OldNewList = List.Zip ( 
        {
            varOldNames, varNewNames
        }
    ),

    // rename the columns in the table using the zipped list.
    RenameColumns = Table.RenameColumns ( Source, OldNewList )
in
    RenameColumns

[Goodkat]

Dear Jenn,

Thank you so much! How cool! It works! And so crisp & clear! I just ran a test in my original data with all of the commands merged into one line as you suggested. And it does the trick! I will put this solution into my little Power Query learning compilation.

Have a good day, or rest of the day, and a joyful upcoming holiday season!

Many greetings, Andreas