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

[DanFromMontreal]

Good morning dear PowerQuery community.

I'm struggling to develop a function (fx) that will replace certain word in a selected table column.

Tried many variations but could make make it work. 

I'm sure that I'm close to a working solution but my limited understanding of the M language prevent me to achieve my goal.

Any thoughts ??

//fxTextToClean2
let
//Will find "st-" and replace by "Saint-" in a selected column - for each row
ListSaintSainte =
{
{"st-","Saint-"},
{"St-","Saint-"},
{"ST-","Saint-"},
{"ste-","Sainte-"},
{"Ste-","Sainte-"},
{"STE-","Sainte-"}
},

Source = (TextToClean as text, Parametre as number, Choix as number) =>
let
L0 =
if Parametre=2 and Choix=11 then Table.TransformColumns({TextToClean, each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip({ListSaintSainte})))), type text } ,null,MissingField.Ignore)
//if Parametre=2 and Choix=11 then Table.TransformColumns(TextToClean, each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip(ListSaintSainte)))), type text ,null, MissingField.Ignore)
//if Parametre=2 and Choix=11 then Table.TransformColumns(TextToClean, each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(ListSaintSainte))), type text ,null, MissingField.Ignore)
//if Parametre=2 and Choix=11 then Table.TransformColumns(TextToClean, each Text.Combine(List.ReplaceMatchingItems({_},ListSaintSainte)), type text ,null, MissingField.Ignore)
else "TBD"
in
L0
in
Source

[dufoq3]

Hi @DanFromMontreal, check this:

Output

Add more replacements in ReplacementTable if needed:

let
    Source = Table.FromList({"Some text st- abc", "Another text Ste- cde", "Last text with STE- and sT- just for test"}),
    ReplacementTable = #table({"Old", "New"}, {
        {"st-", "Saint-"},
        {"ste-", "Sainte-"}
    }),
    LowercasedOld = Table.TransformColumns(ReplacementTable,{{"Old", Text.Lower, type text}}),
    R = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(LowercasedOld))),
    StepBack = Source,
    Ad_Replaced = Table.AddColumn(StepBack, "Replaced", each Text.Combine(List.Transform(Text.Split([Column1], " "), (x)=> Record.FieldOrDefault(R, Text.Lower(x), x)), " "), type text)
in
    Ad_Replaced

[DanFromMontreal]

@dufoq3 , thank you for the response but altough you code does work, I do not see how it is a function and how it is called.

Usually, a function as an fx infront of the query name.

Let me give you the complete function that I have create.

The only part that does not work is the replacing of a the word "st-" for "Saint" (and the rest).

Below is the complete function.  I have added english comment for better understanding.

This is how I call my function fxTextToClean with its parameter.

the parameter 0,2 will remove all lowercase text.

What I want is to be able to select the column I want, enter the parameters 2,11 and ALL "st-", "St-", "Ste-"... be replaced by their corresponding match defined.

Hope this is more clearer.

Regards.

//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

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-"}
},

Source = (TextToClean as text, Parametre as number, Choix as number) =>
let
L0 = if Parametre=0 and Choix=1 then Text.Remove(TextToClean,{"0".."9"})
else if Parametre=0 and Choix=2 then Text.Remove(TextToClean,{"a".."z"})
else if Parametre=0 and Choix=3 then Text.Remove(TextToClean,{"A".."Z"})
else if Parametre=0 and Choix=4 then Text.Remove(TextToClean,{"a".."z","A".."Z"})
else if Parametre=0 and Choix=5 then Text.Remove(TextToClean,{"0".."9","a".."z","A".."Z"})
else if Parametre=0 and Choix=6 then Text.Remove(TextToClean,{" ".."/",":".."@","[".."_","{".."~"})
else if Parametre=0 and Choix=7 then Text.Remove(TextToClean,{"0".."9","a".."z","A".."Z"," ".."/",":".."@","[".."`","{".."~"})
else if Parametre=0 and Choix=10 then Text.Remove(TextToClean,{"`", "´" ,"«" ,"»" ,"“" ,"”" ,"‘" ,"¨" ,"±" ,"¢" ,"°" ,"–" ,"¨" })

else if Parametre=1 and Choix=1 then Text.Select(TextToClean,{"0".."9"})
else if Parametre=1 and Choix=2 then Text.Select(TextToClean,{"a".."z"})
else if Parametre=1 and Choix=3 then Text.Select(TextToClean,{"A".."Z"})
else if Parametre=1 and Choix=4 then Text.Select(TextToClean,{"a".."z","A".."Z"})
else if Parametre=1 and Choix=5 then Text.Select(TextToClean,{"0".."9","a".."z","A".."Z"})
else if Parametre=1 and Choix=6 then Text.Select(TextToClean,{" ".."/",":".."@","[".."`","{".."~"})
else if Parametre=1 and Choix=7 then Text.Select(TextToClean,{"0".."9","a".."z","A".."Z"," ".."/",":".."@","[".."`","{".."~"})

else if Parametre=2 and Choix=8 then Text.Combine(List.ReplaceMatchingItems(Text.ToList(TextToClean), ListAccents))
else if Parametre=2 and Choix=9 then Text.Combine(List.ReplaceMatchingItems(Text.ToList(TextToClean), ListSaintSainte))

else if Parametre=2 and Choix=11 then Table.TransformColumns(TextToClean, each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip(ListSaintSainte)))), type text ,null,MissingField.Ignore)
else "Sélection de paramètres erronnée"
in
L0
in
Source

[PwerQueryKees]

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

[DanFromMontreal]

@PwerQueryKees ,

Thank you for your advice.  Had to understand the modification you did to my original query.

All is working fine but I do not understand where to insert the code to replace the {"st-", St-"...} in the function you've modified!

My ultimate goal is to have a one-stop-shop to modify my desired text.  Sometime I want to modify the text, sometime it is the number... or both.  This way, I have a flexibility not only for me, but for my users that DO NOT understand the PowerM language.... although that I consider myself as very novice.

[PwerQueryKees · Accepted solution]

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

[DanFromMontreal]

Yessss.  Thank you for your support.  I've learned so much.

[PwerQueryKees]

And your function replacing St Saint (and the rest), would be below.
Again, not tested. 

Note the Comparer.OrdinalIgnoreCase: This causes the matching to be case insensitive, so you don't have to give all upper/lower case variants...

(TextToClean, CleaningList) => List.ReplaceMatchingItems({TextToClean}, CleaningList, Comparer.OrdinalIgnoreCase ){0}

[dufoq3]

Hi @DanFromMontreal, do you really need a function? Maybe we can help you to build a better query, but do not use functions if you are not familiar with power query in general (this is just an advice).

For instance, you can remove diacritics this way:

Try to provide new sample data with expected result and maybe we can help you.