Customer Function: 'Rename Column IF' only results in the last request?

Question

I need to put together several files but there were column name changes between different versions of the file. I created a function 'fxRenameCol_If' that will look for a single old column name and change it to the new one.

let  
    fxRenameCol_If = (Table as table, Wrong_Name as text, Correct_Name as text) =>  
    Table.RenameColumns(Table, List.Transform(Table.ColumnNames(Table), each {_, if Text.Contains(_, Wrong_Name) then Correct_Name else _}))  
  
in  
   fxRenameCol_IF  

I would like to push this function to check and change column names based on several sets of old and new column names. I've put together the following formula to do this but the result is that only the last change will take effect but not the sets listed before. How do I have all the changes apply?

let  
    fxRenameCol_IF_Muti = (tbl as table, lt as list) =>   '//List needs to be a list of lists which has two parameters'   
                                                          '//(Old column name to find , what to rename it to) '  
                                                          '//ex: {{"Old", "New"},{"One Fish", "Two Fish"}, {"Red Fish", "Blue Fish"},...}'  
 List.Accumulate(List.Numbers(0,List.Count(lt)),          '//Generates a list of numbers for the length of the changes'   
 Table.ColumnNames(tbl),  
 (State, Current) =>                                      '//These are numbers from the list generated'  
   Table.RenameColumns( tbl,                              '//Rename the columns of this table'  
   List.Transform(Table.ColumnNames(tbl),                 '//using a list of the table column header names'  
   each {_, if Text.Contains(_, lt{Current}{0}) then lt{Current}{1} else _} '//test if the list of table names'  
                                      '//matches the Old column name true then rename New Column Name else rename it itself.'  
  )  
  )  
 )   
in  
   fxRenameCol_IF_Muti  

Answer 1

Hi @RIVERA Nicole (Genuent)
Thanks for posting your code. Your problem is with List.Accumulate's seed (2nd) argument and the fact that you always refer to the same table (tbl). The following works here:

let  
    // Function begin  
    fxRenameMulti_If = (tbl as table, lt as list) as table =>  
    let  
        Source = tbl,  
        RenamedColumns = List.Accumulate({0..List.Count(lt)-1}, Source,  
            (State, Current)=>  
                Table.RenameColumns(State,  
                    List.Transform(Table.ColumnNames(State), each  
                        {_, if Text.Contains(_, lt{Current}{0}) then lt{Current}{1} else _}  
                    )  
                )  
        )  
    in  
        RenamedColumns,  
    // Function end  
  
    Source = Table.FromRows(  
        {List.TransformMany({1..5}, each {"demo"}, (x,y)=> y & Text.From(x))},  
        {"Year","Per","GL Account","Post","Cost element name"}  
    ),  
    RenamedMulti = fxRenameMulti_If(Source,  
        {{"GL","GL Acc#"},{"Post","Post Date"},{"Coat","Cost Element"},{"Year","Year 2021"}}  
    )  
in  
    RenamedMulti  

Answer 2

That works great! Thank you. I think I was confused about the purpose the 'State' parameter. I thought it held the previous number not the previous result of the function. I removed the Source step from the function to reduce the coding. I didn't see any changes to the result. Would it make the difference to keep it?

let
    fxRenameMulti_If = (tbl as table, lt as list) as table => //List needs to be a list of lists which has two parameters ex: {{"Old", "New"},{"One Fish", "Two Fish"},...{}}
            List.Accumulate({0..List.Count(lt)-1}, tbl,
                    (State, Current)=>
                    Table.RenameColumns(State,
                            List.Transform(Table.ColumnNames(State), each
                                {_, if Text.Contains(_, lt{Current}{0}) then lt{Current}{1} else _}
                                )
                        )
                )
in
fxRenameMulti_If

Answer 3

@RIVERA Nicole (Genuent) . An alternative:

fxRenameMulti_If = (tbl as table, lt as list) as table =>  
    let  
        buffList = List.Buffer(lt),  
        renameList = List.Accumulate(Table.ColumnNames(tbl), {},  
            (state, current)=>  
                let  
                    match = List.Select(buffList, each  
                        Text.Contains(current, List.First(_))  
                    ),  
                    keep = List.Last(List.First(match))  
                in  
                    if List.IsEmpty(match) then state else state & {{current, keep}}  
        )  
    in  
        if List.IsEmpty(renameList) then tbl else Table.RenameColumns(tbl, renameList),