Forum Discussion
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.
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
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:
- 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 - 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.
- Related to let/in comment above, you currently have your output value as L2 rather than the last value, L3
- you should remove the end comma on your L3 line before "in". It will produce an error. FYI let/in syntax is
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 L3I've corrected the L2* lines but commented them out since they are redundant.
3 Replies
- MarkLafSuper User
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:
- 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 - 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.
- Related to let/in comment above, you currently have your output value as L2 rather than the last value, L3
- AlexisOlsonSuper User
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 L3I've corrected the L2* lines but commented them out since they are redundant.
- you should remove the end comma on your L3 line before "in". It will produce an error. FYI let/in syntax is
- DanFromMontrealHelper IV