Rename columns of multiple tables based on a column

[lanecarrier]

Hello,

My issue is that my database pulls in all tables with generic column names such as job_date_1 through job_date_119 which does not help my end users.  So i put together a 'dictionary' which is a table with two columns; the first column is the generic name and the second column is the descriptive name of the column.  I want to be able to go through my tables and rename them with the descriptive name, however, there are thousands of columns in this database.  Is there a way to do this using Power Query Advanced Editor?

here is a peak at my 'dictionary' on the right and the list of tables in which I want columns renamed.  The Table1 contains the column names for all tables ~2,000 columns.

I am using the below code to attempt to accomplish this transformation.

let

    Source = Databricks.Catalogs("server", "path", null),

    hive_metastore_Database = Source{[Name="hive_metastore",Kind="Database"]}[Data],

    db_Schema = hive_metastore_Database{[Name="db",Kind="Schema"]}[Data],

    vw_wells_v_Table = db_Schema{[Name="vw_wells_v",Kind="Table"]}[Data],

    #"Renamed Columns" = Table.RenameColumns(#"Table1", Table.ToRows(Table1[FIELD_DESC]), MissingField.Ignore)

in

    #"Renamed Columns"

I figured i could use Table.RenameColumns but I can't get it to work based off of the values of a column.

Any help is appreciated,

Lane

[Vijay_A_Verma · Accepted solution]

You will have to use below statement in all queries where Source must be replaced by your previous step which is vw_wells_v_Table in your case. Note - this has to be done in all queries individually. 

ColumnsPairList = List.Buffer(List.Zip({Table1[FIELD_NAME],Table1[FIELD_DESC]})),
#"Renamed Columns" = Table.RenameColumns(Source,ColumnsPairList,MissingField.Ignore)

 I am attaching one Excel file here to so that you understand this. Open Excel file - Data menu - Data & Connections - You can see the queries here.

https://1drv.ms/x/s!Akd5y6ruJhvhugF4DGTB3YuS1PGk?e=pWmCYW 

[lanecarrier]

Thank you very much.  It's clunky for sure but will do until i can get the Spark SQL updated in the database itself.