I need to find and replace the headers of my Source Table in Power query I am able to do this with

In my Power Query, I have

BulkReplaceStepHeaders = fBulkReplaceStep(#"Demoted Headers", sumAppHeaders, Table.ColumnNames(#"Demoted Headers")),

let BulkReplace = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
    let
        //Convert the FindReplaceTable to a list using the Table.ToRows function
        //so we can reference the list with an index number
        FindReplaceList = Table.ToRows(FindReplaceTable),
        //Count number of rows in the FindReplaceTable to determine
        //how many iterations are needed
        Counter = Table.RowCount(FindReplaceTable),
        //Define a function to iterate over our list 
        //with the Table.ReplaceValue function
        BulkReplaceValues = (DataTableTemp, n) => 
        let 
            //Replace values using nth item in FindReplaceList
            ReplaceTable = Table.ReplaceValue(
                DataTableTemp,
                //replace null with empty string in nth item
                if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},
                if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
                Replacer.ReplaceValue,
                DataTableColumn
                )
        in
            //if we are not at the end of the FindReplaceList
            //then iterate through Table.ReplaceValue again
            if n = Counter - 1 
                then ReplaceTable
                else @BulkReplaceValues(ReplaceTable, n   1),
        //Evaluate the sub-function at the first row
        Output = BulkReplaceValues(DataTable, 0)   
    in
        Output
in
    BulkReplace

CodePudding user response：

1. Demote the headers
2. Transpose the table
3. Replace the old column names that are now all in Column1
4. Transpose the table back
5. Promote the headers

CodePudding user response：

Try this

BulkReplaceStepHeaders = fBulkReplaceStep(Table.FirstN(#"Demoted Headers", 1),sumAppHeaders,Table.ColumnNames(#"Demoted Headers")) & Table.Skip(#"Demoted Headers", 1),

CodePudding user response：

This grabs the column names, merges against the replace table to find new names, then does a rename to use the new names

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Merged Queries" = Table.NestedJoin(Table.FromList(Table.ColumnNames(Source)), {"Column1"}, ReplaceTable, {"Find"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Replace"}, {"Replace"}),
#"NewNames" = Table.AddColumn(#"Expanded Table2", "Custom", each if [Replace]=null then [Column1] else [Replace])[Custom],
#"Rename"=Table.RenameColumns( Source, List.Zip( { Table.ColumnNames( Source ), #"NewNames" } ) )
in #"Rename"