Forum Discussion

DanFromMontreal's avatar
4 years ago
Solved

Using a defined list in function Text.Select

Good afternoon dear Power Query Community In order to clean as much possible my code, I would like to define some list and reuse them later on in my code. Just like we define variable in VBA. Stra...
  • MarkLaf's avatar
    4 years ago

    Cause of your issue: Text.Select is expecting either text or a list of text in its second argument. The second argument you are providing in L2a and L3 are lists of lists.

     

    Solution: Wrap what you have in the second argument with List.Combine to merge your list of lists of text into one list of text.

     

    Also, in L3 you should change the list you are constructing to { {"0".."9"}, ListLetterLower, ListLetterUpper} so it is uniformly a list of lists (as it is now it evaluates to { 0,1,2,3,4, ...., ListLetterLower,ListLetterUpper } - i.e. a mix of text and lists.

     

    Finally, and not sure if these are just copy/paste errors or this is actually in your code, there are two other issues I'll flag:

    1. you should remove the end comma on your L3 line before "in". It will produce an error. FYI let/in syntax is 
      let x = [code] in x​ //1 value where x is output
      let x = [code], y = [code] in y //2 values where y is output​​
    2. L2a through L3 are all transforming L1 over and over rather than passing previous value for a cumulative transformation. It should instead be L2a = Text.Select(L2, ...    L2b = Text.Select(L2a, ...  etc.
    3. Related to let/in comment above, you currently have your output value as L2 rather than the last value, L3
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    DanFromMontreal This is what your code would look like if you corrected the problems MarkLaf explained.

    (TextToClean as text) as text =>
    let
        ListAccent =
        {
        {"à","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"}
        },
        ListNumber = {"0".."9"},
        ListLetterUpper = {"A".."Z"},
        ListLetterLower = {"a".."z"},
        ListSymbol = {"!","#","$","%","&","'","(",")","*","+",",","-",".","/",":",";","<","=",">","?","@","[","\","]","^","_","`","{","|","}","~","€","‚","ƒ","„","…","†","‡","ˆ","‰","‹","‘","’","“","”","•","–","—","˜","™","›","¡","¢","£","¤","¥","¦","§","¨","©","ª","«","¬","­","®","¯","°","±","²","³","´","µ","¶","·","¸","¹","º","»","¼","½","¾","¿","Ð","×","Ø","Þ","ß","ð","÷","ø","þ"},
    
        L0 = Text.Combine(List.ReplaceMatchingItems(Text.ToList(TextToClean), ListAccent)),
        L1 = Text.Proper(L0),
        //L2 = Text.Select(L1, {"0".."9", "a".."z", "A".."Z"}),
        //L2a = Text.Select(L1, List.Combine({ListNumber, ListLetterLower, ListLetterUpper})),
        //L2b = Text.Select(L1, ListLetterLower),
        //L2c = Text.Select(L1, ListSymbol),
        L3 = Text.Select(L1, List.Combine({{"0".."9"}, ListLetterLower, ListLetterUpper}))
    in
        L3

    I've corrected the L2* lines but commented them out since they are redundant.