Not Like function

Hello - if you are simply looking for a way to replace a list of values over a table, please give this function a try.  It also includes a reproducable example and helpful notes.  There are two options for the function - to search for the entire contents of the cell or to search for substrings.

• Create a new, blank query named fnFindReplaceOverEntireTable.  Copy the script below and paste it into the Advanced Editor.  Save.  
• Create another new, blank query with the name of your choice.  Copy the script for the reproducable example and paste it into the Advanced Editor.  Save.
• View the 2nd query you created. It includes three steps: ReplacementsTable, DataTable and PerformReplacements
• The goal of the function is to search the DataTable and replace all instances of the old values in the ReplacementsTable with the corresponding new values in the ReplacementsTable.
  
  Replacements Table

Data Table (Note the special characters in some columns )

Perform Replacements (for substrings).  When the third argument is "substring" the function performs all substring replacements.  When the third argument is "contents" no changes are made because no matches were found when searching the entire contents of cells.

Script for custom function:

// ****************************************************************************************************************/
//   fnFindReplaceOverEntireTable
// ****************************************************************************************************************/
//
//   PURPOSE
//   - Replace one or more values with a corresponding value in all text columns of a table.
//   - A replacements table is used to define old/new values to find/replace.
//
//   INPUTS
//   - DataTable (table): 
//         The table in which the replacements should be performed (aka the starting table)
//
//   - ReplacementsTable (table): 
//         A table containing two columns with the old and new values to find/replace.
//         The column names can be anything but column 1 must be the old values and column 2 must be the new values.
// 
//   - ReplacementsType (text): 
//         This argument is optional.
//         If it is omitted or if an invalid entry is made, a default of 'contents' is assigned.
//         Options are 'contents' and 'substring'.
//         contents: find/replace entire field values (default)
//         substring: find/replace substrings within field values
//  
//   DEVELOPER
//   - Jenn Ratten
//   - [email protected]
//   - last revised: 6/24/2021
//
// ****************************************************************************************************************/
/*
REPRODUCABLE EXAMPLE

let

    ReplacementsTable = Table.TransformColumnTypes(
            Table.FromRows(
                Json.Document(
                    Binary.Decompress(
                        Binary.FromText("i45W0gFCpVidaKX4eBirDsaIgzEiIsCsWAA=", BinaryEncoding.Base64), 
                        Compression.Deflate
                    )
                ), 
                let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [old = _t, new = _t]
            ),
            {{"old", type text}, {"new", type text}}
        ),
    DataTable = Table.TransformColumnTypes(
        Table.FromRows(
            Json.Document(
                Binary.Decompress(
                    Binary.FromText("i45W8sgvLsgsScxR0lEKDQYSwfmlJRmpicUlQLabTx2QNDQwMDAE0n4BCj6uYa4+Cp4KoX7Bzh6uLgoBQf7O+sFhziCNRqbGSrE6BAzU8YEaaIRiIA4TzbCbGB+P6kiokcaoRuIw09yCsCthJpqgmBiG3UBLI+INNEU2ELt5xgbmOM2LiMAw0QxIuwaHKASEKPiE4YwaY0Ol2FgA", BinaryEncoding.Base64),
                    Compression.Deflate
                )
            ), 
            let _t = ((type nullable text) meta [Serialized.Text = true]) 
            in type table [#"Place of Service" = _t, Country = _t, Region = _t, State = _t, #"CDM NUMBER" = _t, #"GEN-DESC" = _t, PRICE = _t]
        ), {{"Place of Service", type text}, {"Country", type text}, {"Region", type text}, {"State", type text}, {"GEN-DESC", type text}, {"CDM NUMBER", Int64.Type}, {"PRICE", Currency.Type}}
    ),
    PerformReplacements = fnFindReplaceOverEntireTable ( DataTable, ReplacementsTable, "substring" )

in
    PerformReplacements
*/
// ****************************************************************************************************************/

let

    fn = ( DataTable as table, ReplacementsTable as table, optional ReplacementsType as text ) as table =>

let

    // Assign the ReplacementsType argument text to a variable, with error handling.
    // To prevent errors in the event an invalid string is entered...
    // If the text equals anything other than "substring" (even an invalid selection), "contents" will be applied.
    ReplacementsTypeSelection = 
        if Text.Lower(ReplacementsType) = "substring" 
        then "substring" 
        else "contents",

    // Assign replacer functions to variables.
    ReplacementsTypeContents = Replacer.ReplaceValue,             // entire contents
    ReplacementsTypeSubstring = Replacer.ReplaceText,             // substrings

    // Assign selected replacer function to a variable.
    ReplacerFunctionAsFunction = 
        if ReplacementsTypeSelection = "substring" 
        then ReplacementsTypeSubstring 
        else ReplacementsTypeContents,    

    // Perform the replacements.
    Output = List.Accumulate (                                    // loop through all rows in a table column (column of values = list)
        Table.ToRows ( ReplacementsTable ),                       // replacements table as a list of lists
        DataTable,                                                // table in which the replacements should be performed
        ( t, r ) =>                                               // declare state and current variables and inline function (data table and replacements rows)
            Table.ReplaceValue (                                  // function to perform the replacements
                t,                                                // the table before each iteration  
                r{0},                                             // old value to search for - column 1 of the replacements table (columns index at base 0) 
                r{1},                                             // new value to replace with - column 1 of the replacements table (columns index at base 0)
                ReplacerFunctionAsFunction,                       // variable with the replacer function definition 
                Table.ColumnsOfType ( t, { type nullable text } ) // only perform replacements in data table columns of type text
            ) 
    )
in
    Output,

    // Define a new function type that includes the necessary documentation as a metadata record.
    fnType = 
        type function (
            DataTable as (
                type table meta [
                    Documentation.FieldCaption = "Data Table",
                    Documentation.FieldDescription = "table in which replacements are performed",
                    Documentation.SampleValues = {"Table1", "Table2"}                    
                ]
            ), 
            ReplacementsTable as (
                type table meta [
                    Documentation.FieldCaption = "Replacements Table",
                    Documentation.FieldDescription = "table containing old/new values in columns 1 and 2",
                    Documentation.SampleValues = {"Table1", "Table2"}                       
                ]
            ), 
            optional ReplacementsType as (
                type text meta [
                    Documentation.FieldCaption = "Replacements Type Selection",
                    Documentation.FieldDescription = "Should the entire value or a substring searched/replaced?",
                    Documentation.AllowedValues = {"contents", "substring"}                     
                ]
            )
        ) as table meta [
            Documentation.Name = "fnFindReplaceOverEntireTable",
            Documentation.LongDescription = "This function returns a new table with text with replacements performed.",
            Documentation.Examples = 
            {
                [
                    Description = "This function returns a new table with text with replacements performed.",
                    Code = "fnFindReplaceOverEntireTable ( DataTable, ReplacementsTable, ""contents"" )",
                    Result = ""
                ]
            }
        ]
    in 
        Value.ReplaceType ( fn, fnType )

Reproducable Example

let

    ReplacementsTable = Table.TransformColumnTypes(
            Table.FromRows(
                Json.Document(
                    Binary.Decompress(
                        Binary.FromText("i45W0gFCpVidaKX4eBirDsaIgzEiIsCsWAA=", BinaryEncoding.Base64), 
                        Compression.Deflate
                    )
                ), 
                let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [old = _t, new = _t]
            ),
            {{"old", type text}, {"new", type text}}
        ),
    DataTable = Table.TransformColumnTypes(
        Table.FromRows(
            Json.Document(
                Binary.Decompress(
                    Binary.FromText("i45W8sgvLsgsScxR0lEKDQYSwfmlJRmpicUlQLabTx2QNDQwMDAE0n4BCj6uYa4+Cp4KoX7Bzh6uLgoBQf7O+sFhziCNRqbGSrE6BAzU8YEaaIRiIA4TzbCbGB+P6kiokcaoRuIw09yCsCthJpqgmBiG3UBLI+INNEU2ELt5xgbmOM2LiMAw0QxIuwaHKASEKPiE4YwaY0Ol2FgA", BinaryEncoding.Base64),
                    Compression.Deflate
                )
            ), 
            let _t = ((type nullable text) meta [Serialized.Text = true]) 
            in type table [#"Place of Service" = _t, Country = _t, Region = _t, State = _t, #"CDM NUMBER" = _t, #"GEN-DESC" = _t, PRICE = _t]
        ), {{"Place of Service", type text}, {"Country", type text}, {"Region", type text}, {"State", type text}, {"GEN-DESC", type text}, {"CDM NUMBER", Int64.Type}, {"PRICE", Currency.Type}}
    ),
    PerformReplacements = fnFindReplaceOverEntireTable ( DataTable, ReplacementsTable, "substring" )

in
    PerformReplacements