'Apply Power Query to all columns

I have a Power query that finds and replaces values listed in a table that I work through from here Bulk Find And Replace In Power Query

But I need to apply it to All columns.

How to do this without listing all the columns as they are dynamic and keep changing

Thanks

What I have so far

let
    Source = Excel.CurrentWorkbook(){[Name="MyData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Title", type text}}),
    BulkReplaceStep = fBulkReplace(#"Changed Type", MyFindReplace, {"Job Title","Job Title2"})
in
    BulkReplaceStep

The find/replace data table

let
    Source = Excel.CurrentWorkbook(){[Name="MyFindReplace"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Find", type text}, {"Replace", type text}})
in
    #"Changed Type

Bulkreplace

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
                if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},
                if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
                Replacer.ReplaceText,
                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

excel powerquery

Solution 1:^[1]

This works

Change this:

BulkReplaceStep = fBulkReplace(#"Changed Type", MyFindReplace, {"Job Title","Job Title2"})

To This:

BulkReplaceStep = fBulkReplace(#"Changed Type", MyFindReplace, Table.ColumnNames(#"Changed Type"))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Einarr

'Apply Power Query to all columns

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]