Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Find Replace values only on first row of source table

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

If I demote the headers and run BulkReplace then promote the headers, I get what I want.

 

But this Finds/Replaces on the entire table.

 

I know I can transpose the source file and then run BulkReplace on the first column then transpose again and this will give me what I want as well

 

But both these methods seem inefficient/resource heave if all I want to do is run BulkReplace on the first row

 

Is there a way to run BulkReplace on only the first row?

Or is there a different way to loop over the first row and find replace?

 

Thanks

 

sumAppHeaders (Find Replace Table)

 

BulkReplace

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

 

  • You can use a combination of Table.FirstN and Table.Skip to do this like:

    BulkReplaceStepHeaders = fBulkReplaceStep(Table.FirstN(table, 1), ...) & Table.Skip(table, 1)

     Just replace table with the name of the previous step and ... with the other args to the bulk replace function

4 Replies

  • artemus's avatar
    artemus
    Icon for Microsoft Employee rankMicrosoft Employee

    You can use a combination of Table.FirstN and Table.Skip to do this like:

    BulkReplaceStepHeaders = fBulkReplaceStep(Table.FirstN(table, 1), ...) & Table.Skip(table, 1)

     Just replace table with the name of the previous step and ... with the other args to the bulk replace function

    • Anonymous's avatar
      Anonymous
      Not applicable

      So this worked

      BulkReplaceStepHeaders = fBulkReplaceWhole(Table.FirstN(#"Changed Type", 1),sumAppHeaders,Table.ColumnNames(#"Changed Type")) & Table.Skip(#"Changed Type", 1),

       

      But I can't quit following what is going on. Could you explain a bit as this technique will be useful in other contatexts

       

      Thanks again

      • artemus's avatar
        artemus
        Icon for Microsoft Employee rankMicrosoft Employee

        The table is cut into 2 parts. The first part is the just the first row via Table.FirstN function, the second is the remaining rows in the table via Table.Skip function. The Bulk replace function is called on just the first 1-row table. Finally the concat operdator & is used to glue the tables back together.