Create a function (fx) that will replace certain word in a selected column

I had a comprehensive post explaining it all, but I got an error in posting it...

Long story short.

This is the new query with all errors fixed and tested.

//fxTextToClean
//Paramètre=0 > Supprime / Delete
//Paramètre=1 > Garde / Kepp
//Paramètre=2 > Remplace
//Choix-0 > Chiffres + Minuscule + Majuscule + Symbole + Accent / Number + Lowercase + Uppercase + Symbol + Accent
//Choix=1 > Chiffres / Number
//Choix=2 > Lettres minuscules / Lowercase
//Choix=3 > Lettres majuscules / Uppercase
//Choix=4 > Lettres minuscules + Lettres majuscules / Lowercase + Uppercase
//Choix=5 > Chiffres + Lettres minuscules + Lettres majuscules / Number + Lowercase + Uppercase
//Choix=6 > Symbole / Synbol
//Choix=7 > Chiffres + Lettres minuscules + Lettres majuscules + Symbole / Number + Lowercase + Uppercase + Synbol
//Choix=8 > Accent
//Choix=9 > Saint ou Sainte
let
    worker_function_list =   // A list of function to call based on Parametre
        {
            Text.Remove,
            Text.Select,
            (txt, lst) =>   List.Accumulate(lst,txt,(State,Current) => Text.Replace(State, Current{0}, Current{1}))
        },

    ListAccents = 
        {
            {"à", "a"}, {"á", "a"}, {"â", "a"}, {"ã", "a"}, {"ä", "a"}, {"å", "a"}, {"À", "A"}, {"Á", "A"}, {"Â", "A"}, {"Ã", "A"}, {"Ä", "A"}, {"Å", "A"}, 
            {"È", "E"}, {"É", "E"}, {"Ê", "E"}, {"Ë", "E"}, {"è", "e"}, {"é", "e"}, {"ê", "e"}, {"ë", "e"}, 
            {"ì", "i"}, {"í", "i"}, {"î", "i"}, {"ï", "i"}, {"Ì", "I"}, {"Í", "I"}, {"Î", "I"}, {"Ï", "I"}, 
            {"ò", "o"}, {"ó", "o"}, {"ô", "o"}, {"õ", "o"}, {"ö", "o"}, {"Ò", "O"}, {"Ó", "O"}, {"Ô", "O"}, {"Õ", "O"}, {"Ö", "O"}, 
            {"ù", "u"}, {"ú", "u"}, {"û", "u"}, {"ü", "u"}, {"Ù", "U"}, {"Ú", "U"}, {"Û", "U"}, {"Ü", "U"}, 
            {"ý", "y"}, {"ÿ", "y"}, {"Ý", "Y"}, {"Ÿ", "Y"}, {"ç", "c"}, {"Ç", "C"}, {"ñ", "n"}, {"Ñ", "N"}, 
            {"š", "s"}, {"Š", "S"}, {"ž", "z"}, {"Ž", "Z"}, {"Œ", "OE"}, {"œ", "oe"}, {"Æ", "AE"}, {"æ", "ae"} 
        },

    ListSaintSainte = 
    {
        {"st-", "Saint-"},
        {"St-", "Saint-"},
        {"ST-", "Saint-"},
        {"ste-", "Sainte-"},
        {"Ste-", "Sainte-"},
        {"STE-", "Sainte-"}
    },
    
    List_of_CleanningLists =  // The list of cleaning lists to use based on Choix

        {
            {"0".."9"},
            {"a".."z"},
            {"A".."Z"},
            {"a".."z", "A".."Z"},
            {"0".."9", "a".."z", "A".."Z"},
            {" ".."/", ":".."@", "[".."_", "{".."~"},
            {"0".."9", "a".."z", "A".."Z", " ".."/", ":".."@", "[".."`", "{".."~"},
            {"`", "´", "«", "»", "“", "”", "‘", "¨", "±", "¢", "°", "–", "¨"},
            ListAccents,
            ListSaintSainte
        },

    // Here the actual function
    Source = (TextToClean as text, Parametre as number, Choix as number) =>
            worker_function_list{Parametre}(TextToClean, List_of_CleanningLists{Choix})  // {x} gets item x from a list. Starting with 0
in
    Source

Paste it in a new query in the advanced editor.

Name the query fxTextToClean (or any other name you like)

Your screen shoul look like this:

Enter some parameters and hit Invoke.
This will produce a new query where the custom function is called...

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel