Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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:
@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.
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:
@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.