Conditionally rename columns

[michaelsh]

I am getting inconsistent column names from my source files.

Sometimes, one column's name is "Document Type", sometime, it is "Document type", with a small "t".

Sometimes, another column's name is "Amt. in loc. curr.", sometimes it is "Amount in local currency"

I am using import "From Folder" that generates function for each file in the folder.

I would like to add some conditional column renaming to this function, 

so the logic would be something like this:

"If there is a column named "Document type", change it to "Document Type" else do not change, if there is a column named "Amt. in loc. curr.", change it to "Amount in local currency", else do not change.

This way I can ensure that the rest of my queries work with standard columns.

Please help

[Mark_N]

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

[watkinnc]

Actually the column sequence does not matter, the function will still work.

--Nate

[watkinnc · Accepted solution]

All you have to do is use Table.RenameColumns, and add the renames whether or not that table's column names are correct or not, because you can add the MissingField.Ignore parameter. So in your Transform file, after you've promoted headers, or at whatever point your column names are in place, add this as your next step:

= Table.RenameColumns(PriorStepName, {{"Document type", "Document Type"}, {"Amt. in loc. curr.", "Amount In Local Currency"}, {"Another bad name", "Another Bad Name"}}, MissingField.Ignore)

If the column name is incorrect, it'll get corrected. If it's already correct, then the misspelled column is not present in that table, and will be ignored.

--Nate

[michaelsh]

Thanks a lot again, @watkinnc 

You save me a lot of work!

[watkinnc]

Very glad I could help!

[michaelsh]

Awesome!

Thank you, @watkinnc !

[mahoneypat]

You can adapt the approach described in this video to dynamically rename your columns with relative references (e.g., rename the first column to "Column1" and the second to "Column2").  You can use Table.ColumnNames(#"Previous Step"){0} for example to get the name of the first column from the previous step (replace Previous Step with the actual step name), and you can use that in place of your current hard-coded column name.

(3) Power BI - Use relative references to avoid combine & transform errors - YouTube

Pat