Using a defined list in function Text.Select

[DanFromMontreal]

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.

Strangely, it does work but I have to use only ONE list.

If I'm to use 2 different list, it does nor work.

See L2 and L2a lign in my code.

Any thoughts????

(TextToClean 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)),  //Working
L1 = Text.Proper(L0),   //Working
L2 = Text.Select(L1, {"0".."9", "a".."z", "A".."Z"}), //Working
L2a = Text.Select(L1, {ListNumber, ListLetterLower, ListLetterUpper}), //DOES NOT work
L2b = Text.Select(L1, ListLetterLower), //Working
L2c = Text.Select(L1, ListSymbol)
L3 = Text.Select(L1,{"0".."9",ListLetterLower, ListLetterUpper}), //DOES NOT work
in
L2

[MarkLaf · Accepted solution]

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

[DanFromMontreal]

Thank you @al & @MarkLaf for the solution.  I was almost there.  Thanks for the push...

[MarkLaf · Accepted solution]

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 · Accepted solution]

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