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

Below is my version of your function. Not tested though....

Doing if else to get a value from a list is a big no no. Very error sensitive, hard to maintain and uneccesary slow in most languages.

So I changed your function to use Parametre and Choix as indexes in lists.

I skipped the parameter checking though... I would probably make a list of valid Parametre/Choix pairs and check that... 

Just save this in a blank query with Advanced editor and give it any name you like (fx is not required, but go for it if it fits your naming conventions)

Call it anywhere you can call a function by the name you gave it... In a Table.AddColumns for example?

And I skipped the Table.TransformColumns for Parametre=2 and Choix=11. I would not recommend putting this in the same function. This would require you to pass a table to the function as well. If you want a standard function to wrap around the Table.TransformColumns, make it a separate one. Probably calling your fxTextToClean function? 😊

Feel free to ask your follow up question ....

//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,
            (TextToClean, CleaningList) => Text.Combine(List.ReplaceMatchingItems(Text.ToList(TextToClean), CleaningList))
        },

    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 - 1}(TextToClean, List_of_CleanningLists{Choix - 1})  // {x} gets item x from a list. But starting with 0, so x-1
in
    Source

 

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