Forum Discussion

Helper IV

1 year ago

Solved

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

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

Need Help

PwerQueryKees

1 year ago

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

Helper IV

1 year ago

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

Super User

1 year ago

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

Kees Stolker

A big fan of Power Query and Excel

PwerQueryKees
Super User
1 year ago
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}

DanFromMontreal

Helper IV

1 year ago

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

Super User

1 year ago

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

Kees Stolker

A big fan of Power Query and Excel

DanFromMontreal
Helper IV
1 year ago
Yessss. Thank you for your support. I've learned so much.

Forum Discussion

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

Related content

Function selection

Selective replace mcode

Question on Replace Function

Replace NULL

Replacing text , exact