Conditionally rename columns

Although this post is failrly old thought I would add my own method.  Most of the solutions on this topic make an assumption that the columns in each file are actually in the same sequence but what if they are not?  I wanted to create something that was column order agnostic and was able to cope with lower/upper case column names.

I have to caveat this with the fact that I have only just started using Power Query and my previous experience was simply changing data types for columns etc.  As such I dont know how efficient this solution is, only that it worked for me.  I should also say that whilst this is my solution to the problem I actually used lots of various other solutions for each of the steps (lots of Youtube research etc).

I wanted a generic and reusable solution so went for a function rather than trying to impement standard transformations.  The function below requires 2 arguments - the table to conduct the transformation on and a mapping table which has 2 columns.  Column 1 (=from name) has the current column names as a list and column 2 has the name to change to (=to name).  If a table has a column name that is not in the mapping table it keeps its original name.  Column positions are not changed but merging the tables seems to match on column names not position.

It assumes that the column names are promoted (if not then a preceding step needs to do this).  If there aren't existing column names e.g. they are named Column 1, Column 2 etc then these could be used in the mapping table

Usage:

1.  Create a mapping table.  In my case I created a separate Excel with 1 table.  Column 1 had the 'from' and column 2 had the 'to'.  Add this table to Power Query data sources

2.  Get files from folder as usual

3.  Create a new function and paste in the DAX below

4.  In the table list that comes from the above step 2 add a new calculated column that calls this new function.  First argument is the column of source tables; second argument is the name of the mapping table (text name).  For example my mapping table is called 'headerMapping' in the data sources.  The source tables from import from folder are in a column called 'tableList' and my new calcuated column for the renamed column header tables is called 'headerNames'

5.  This function returns lists of from/to column names, it does not rename the columns.  Create a new custom column and enter the following formula = Table.RenameColumns([tableList],[headerNames])  // Note the reference to the previous column step for the renamed headers.  This returns Tables with columns renamed.

How it works ...

1.  Use the #shared with the mapping table name (as text) to get the actual mapping table entity

2.  Read the columns in each source table and get the column names in a single column table

3.  Use the table merge to perform a lookup from old name to new name.  This returns a table with 2 columns containing original column name and the renamed value

4.  If the merge resulted in a new column name = null then its becasue that column name is not in the mapping table.  replace null with the original column name

5.  Convert this from/to column name to a list and return

Function:

//INPUT: Table to rename headers + Mapping table for old to New names
//
//OUTPUT: A List of header pairs that can be directly used in Table.RenameColumns(TABLE,renameHeadersStep)

let renameHeaders=(t,lookupTable) =>
let
// Find the mapping table based on its name
//
Source = Record.ToTable(#shared),
r = Table.SelectRows(Source, each ([Name] = lookupTable)),
mappingTable = r{0}[Value],

//get column names for use later in merge
//
mappingOldNameColumn = Table.ColumnNames(mappingTable){0},
mappingNewNameColumn= Table.ColumnNames(mappingTable){1},

//Step 1: Get column names for the table
//
columnNameList=Table.ColumnNames(t), //Get column headers as list
columnNameTable=Table.FromList(columnNameList), //Convert list to table with single column
columnName_renamed=Table.RenameColumns(columnNameTable,{"Column1","oldName"}), //Rename column to make it easier to use

//Step 2: Merge table with header mapping table to get new table showing old name and new name
//
mergedTable= Table.FuzzyNestedJoin(columnName_renamed, "oldName", mappingTable, mappingOldNameColumn, "newName", JoinKind.LeftOuter,[IgnoreCase=true]), //merge table with mapping table to get new names
expandedTable= Table.ExpandTableColumn(mergedTable, "newName", {"newName"}, {mappingNewNameColumn}), //expand the table to include the new name

//Step 3: If a column does not exist in mapping table then use original name
//
finalTable=Table.ReplaceValue(expandedTable,null,each if [newName] = null then [oldName] else [newName],Replacer.ReplaceValue,{mappingNewNameColumn}),

//Step 4: Convert the old/new names into a list of lists which can be used in Table.RenameColumns. The actual renaming is not carried out by this function
//
ListForRename = Table.ToRows(finalTable) //convert this table to a list wde can use

in ListForRename

in
renameHeaders