Restrict transformation to Header row or Row 1 Power Query

xyz333 0
Viewed 49

I need to find and replace the headers of my Source Table in Power query I am able to do this with BulkReplace But this searches the entire table, is there a way to restrict BulkReplace to only the headers, or if not then I can demote the headers and run BulkReplace on just Row 1 of the Source Table

Thank you

sumAppHeads (Find Replace Table) enter image description here

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
powerquery powerbi excel
3 Answers
  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
userhiPt0

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"

enter image description here

horseyride0

Try this

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