List from table column as selectable items in function

[jdellasala]

Inspired by @smozgur's articles on using Google Translate in Power Query, I created functions to translate to and from English. They're working fine, and now I've discovered that using sl=auto will automatically detect the source language. In a test of translating an English Phrase into the 134 supported languages and then those translations back into English, 54 languages got it nearly perfect, and the other 80 were similar enough to pass.
In using the the function to Auto Detect the source language so that only the source text and target language are needed for the function, only 9 languages failed out of the 134 when compared to the two step translation to another language and back to English. Here's the function:

(SrcTxt as text, LangCode as text ) as text =>
// SrcTxt = Text to be translated, LangCode = the language CODE to translate to.
let
    URL = "https://translate.googleapis.com/translate_a/single?client=gtx&sl=auto&tl=" & LangCode & "&dt=t&q=" & SrcTxt,
    Source = Json.Document( Web.Contents( URL ) ),
    Translation = Source{0}{0}{0}
in
    Translation

Here's my question. I have a table of all the supported languages and their codes. How can I have the function present the list of languages like a Data Validation list when invoking the function? Right now one needs to either manually specify the Language Code or it needs to be in a column. Is that even possible?

 

[JGordon11]

I don't think that functionality is available. If you give your second parameter an insanely long name then you can use the intellisense prompt as a key for what to enter as the code. Kind of a crazy work-around:

(SrcTxt as text, #"af Afrikaans, ak Akan, sq Albanian, am Amharic, ar Arabic, hy Armenian, az Azerbaijani, eu Basque, be Belarusian, bem Bemba, bn Bengali, bh Bihari, bs Bosnian, br Breton, bg Bulgarian, km Cambodian, ca Catalan, chr Cherokee, ny Chichewa, zh-CN Chinese (Simplified), zh-TW Chinese (Traditional), co Corsican, hr Croatian, cs Czech, da Danish, nl Dutch, en English, eo Esperanto, et Estonian, ee Ewe, fo Faroese, tl Filipino, fi Finnish, fr French, fy Frisian, gaa Ga, gl Galician, ka Georgian, de German, el Greek, gn Guarani, gu Gujarati, ht Haitian Creole, ha Hausa, haw Hawaiian, iw Hebrew, hi Hindi, hu Hungarian, is Icelandic, ig Igbo, id Indonesian, ia Interlingua, ga Irish, it Italian, ja Japanese, jw Javanese, kn Kannada, kk Kazakh, rw Kinyarwanda, rn Kirundi, xx-klingon Klingon, kg Kongo, ko Korean, kri Krio (Sierra Leone), ku Kurdish, ckb Kurdish (Soranî), ky Kyrgyz, lo Laothian, la Latin, lv Latvian, ln Lingala, lt Lithuanian, loz Lozi, lg Luganda, ach Luo, mk Macedonian, mg Malagasy, ms Malay, ml Malayalam, mt Maltese, mi Maori, mr Marathi, mfe Mauritian Creole, mo Moldavian, mn Mongolian, sr-ME Montenegrin, ne Nepali, pcm Nigerian Pidgin, nso Northern Sotho, no Norwegian, nn Norwegian (Nynorsk), oc Occitan, or Oriya, om Oromo, ps Pashto, fa Persian, xx-pirate Pirate, pl Polish, pt-BR Portuguese (Brazil), pt-PT Portuguese (Portugal), pa Punjabi, qu Quechua, ro Romanian, rm Romansh, nyn Runyakitara, ru Russian, gd Scots Gaelic, sr Serbian, sh Serbo-Croatian, st Sesotho, tn Setswana, crs Seychellois Creole, sn Shona, sd Sindhi, si Sinhalese, sk Slovak, sl Slovenian, so Somali, es Spanish, es-419 Spanish (Latin American), su Sundanese, sw Swahili, sv Swedish, tg Tajik, ta Tamil, tt Tatar, te Telugu, th Thai, ti Tigrinya, to Tonga, lua Tshiluba, tum Tumbuka, tr Turkish, tk Turkmen, tw Twi, ug Uighur, uk Ukrainian, ur Urdu, uz Uzbek, vi Vietnamese, cy Welsh, wo Wolof, xh Xhosa, yi Yiddish, yo Yoruba, zu Zulu"
 as text ) as text =>
// SrcTxt = Text to be translated, second parameter = the language CODE to translate to.
let
    LangCode = #"af Afrikaans, ak Akan, sq Albanian, am Amharic, ar Arabic, hy Armenian, az Azerbaijani, eu Basque, be Belarusian, bem Bemba, bn Bengali, bh Bihari, bs Bosnian, br Breton, bg Bulgarian, km Cambodian, ca Catalan, chr Cherokee, ny Chichewa, zh-CN Chinese (Simplified), zh-TW Chinese (Traditional), co Corsican, hr Croatian, cs Czech, da Danish, nl Dutch, en English, eo Esperanto, et Estonian, ee Ewe, fo Faroese, tl Filipino, fi Finnish, fr French, fy Frisian, gaa Ga, gl Galician, ka Georgian, de German, el Greek, gn Guarani, gu Gujarati, ht Haitian Creole, ha Hausa, haw Hawaiian, iw Hebrew, hi Hindi, hu Hungarian, is Icelandic, ig Igbo, id Indonesian, ia Interlingua, ga Irish, it Italian, ja Japanese, jw Javanese, kn Kannada, kk Kazakh, rw Kinyarwanda, rn Kirundi, xx-klingon Klingon, kg Kongo, ko Korean, kri Krio (Sierra Leone), ku Kurdish, ckb Kurdish (Soranî), ky Kyrgyz, lo Laothian, la Latin, lv Latvian, ln Lingala, lt Lithuanian, loz Lozi, lg Luganda, ach Luo, mk Macedonian, mg Malagasy, ms Malay, ml Malayalam, mt Maltese, mi Maori, mr Marathi, mfe Mauritian Creole, mo Moldavian, mn Mongolian, sr-ME Montenegrin, ne Nepali, pcm Nigerian Pidgin, nso Northern Sotho, no Norwegian, nn Norwegian (Nynorsk), oc Occitan, or Oriya, om Oromo, ps Pashto, fa Persian, xx-pirate Pirate, pl Polish, pt-BR Portuguese (Brazil), pt-PT Portuguese (Portugal), pa Punjabi, qu Quechua, ro Romanian, rm Romansh, nyn Runyakitara, ru Russian, gd Scots Gaelic, sr Serbian, sh Serbo-Croatian, st Sesotho, tn Setswana, crs Seychellois Creole, sn Shona, sd Sindhi, si Sinhalese, sk Slovak, sl Slovenian, so Somali, es Spanish, es-419 Spanish (Latin American), su Sundanese, sw Swahili, sv Swedish, tg Tajik, ta Tamil, tt Tatar, te Telugu, th Thai, ti Tigrinya, to Tonga, lua Tshiluba, tum Tumbuka, tr Turkish, tk Turkmen, tw Twi, ug Uighur, uk Ukrainian, ur Urdu, uz Uzbek, vi Vietnamese, cy Welsh, wo Wolof, xh Xhosa, yi Yiddish, yo Yoruba, zu Zulu",
    URL = "https://translate.googleapis.com/translate_a/single?client=gtx&sl=auto&tl=" & LangCode & "&dt=t&q=" & SrcTxt,
    Source = Json.Document( Web.Contents( URL ) ),
    Translation = Source{0}{0}{0}
in
    Translation

 

[jdellasala]

I don't think that functionality is available. If you give your second parameter an insanely long name then you can use the intellisense prompt as a key for what to enter as the code. Kind of a crazy work-around:

(SrcTxt as text, #"af Afrikaans, ak Akan, sq Albanian, am Amharic, ar Arabic, hy Armenian, az Azerbaijani, eu Basque, be Belarusian, bem Bemba, bn Bengali, bh Bihari, bs Bosnian, br Breton, bg Bulgarian, km Cambodian, ca Catalan, chr Cherokee, ny Chichewa, zh-CN Chinese (Simplified), zh-TW Chinese (Traditional), co Corsican, hr Croatian, cs Czech, da Danish, nl Dutch, en English, eo Esperanto, et Estonian, ee Ewe, fo Faroese, tl Filipino, fi Finnish, fr French, fy Frisian, gaa Ga, gl Galician, ka Georgian, de German, el Greek, gn Guarani, gu Gujarati, ht Haitian Creole, ha Hausa, haw Hawaiian, iw Hebrew, hi Hindi, hu Hungarian, is Icelandic, ig Igbo, id Indonesian, ia Interlingua, ga Irish, it Italian, ja Japanese, jw Javanese, kn Kannada, kk Kazakh, rw Kinyarwanda, rn Kirundi, xx-klingon Klingon, kg Kongo, ko Korean, kri Krio (Sierra Leone), ku Kurdish, ckb Kurdish (Soranî), ky Kyrgyz, lo Laothian, la Latin, lv Latvian, ln Lingala, lt Lithuanian, loz Lozi, lg Luganda, ach Luo, mk Macedonian, mg Malagasy, ms Malay, ml Malayalam, mt Maltese, mi Maori, mr Marathi, mfe Mauritian Creole, mo Moldavian, mn Mongolian, sr-ME Montenegrin, ne Nepali, pcm Nigerian Pidgin, nso Northern Sotho, no Norwegian, nn Norwegian (Nynorsk), oc Occitan, or Oriya, om Oromo, ps Pashto, fa Persian, xx-pirate Pirate, pl Polish, pt-BR Portuguese (Brazil), pt-PT Portuguese (Portugal), pa Punjabi, qu Quechua, ro Romanian, rm Romansh, nyn Runyakitara, ru Russian, gd Scots Gaelic, sr Serbian, sh Serbo-Croatian, st Sesotho, tn Setswana, crs Seychellois Creole, sn Shona, sd Sindhi, si Sinhalese, sk Slovak, sl Slovenian, so Somali, es Spanish, es-419 Spanish (Latin American), su Sundanese, sw Swahili, sv Swedish, tg Tajik, ta Tamil, tt Tatar, te Telugu, th Thai, ti Tigrinya, to Tonga, lua Tshiluba, tum Tumbuka, tr Turkish, tk Turkmen, tw Twi, ug Uighur, uk Ukrainian, ur Urdu, uz Uzbek, vi Vietnamese, cy Welsh, wo Wolof, xh Xhosa, yi Yiddish, yo Yoruba, zu Zulu"
 as text ) as text =>
// SrcTxt = Text to be translated, second parameter = the language CODE to translate to.
let
    LangCode = #"af Afrikaans, ak Akan, sq Albanian, am Amharic, ar Arabic, hy Armenian, az Azerbaijani, eu Basque, be Belarusian, bem Bemba, bn Bengali, bh Bihari, bs Bosnian, br Breton, bg Bulgarian, km Cambodian, ca Catalan, chr Cherokee, ny Chichewa, zh-CN Chinese (Simplified), zh-TW Chinese (Traditional), co Corsican, hr Croatian, cs Czech, da Danish, nl Dutch, en English, eo Esperanto, et Estonian, ee Ewe, fo Faroese, tl Filipino, fi Finnish, fr French, fy Frisian, gaa Ga, gl Galician, ka Georgian, de German, el Greek, gn Guarani, gu Gujarati, ht Haitian Creole, ha Hausa, haw Hawaiian, iw Hebrew, hi Hindi, hu Hungarian, is Icelandic, ig Igbo, id Indonesian, ia Interlingua, ga Irish, it Italian, ja Japanese, jw Javanese, kn Kannada, kk Kazakh, rw Kinyarwanda, rn Kirundi, xx-klingon Klingon, kg Kongo, ko Korean, kri Krio (Sierra Leone), ku Kurdish, ckb Kurdish (Soranî), ky Kyrgyz, lo Laothian, la Latin, lv Latvian, ln Lingala, lt Lithuanian, loz Lozi, lg Luganda, ach Luo, mk Macedonian, mg Malagasy, ms Malay, ml Malayalam, mt Maltese, mi Maori, mr Marathi, mfe Mauritian Creole, mo Moldavian, mn Mongolian, sr-ME Montenegrin, ne Nepali, pcm Nigerian Pidgin, nso Northern Sotho, no Norwegian, nn Norwegian (Nynorsk), oc Occitan, or Oriya, om Oromo, ps Pashto, fa Persian, xx-pirate Pirate, pl Polish, pt-BR Portuguese (Brazil), pt-PT Portuguese (Portugal), pa Punjabi, qu Quechua, ro Romanian, rm Romansh, nyn Runyakitara, ru Russian, gd Scots Gaelic, sr Serbian, sh Serbo-Croatian, st Sesotho, tn Setswana, crs Seychellois Creole, sn Shona, sd Sindhi, si Sinhalese, sk Slovak, sl Slovenian, so Somali, es Spanish, es-419 Spanish (Latin American), su Sundanese, sw Swahili, sv Swedish, tg Tajik, ta Tamil, tt Tatar, te Telugu, th Thai, ti Tigrinya, to Tonga, lua Tshiluba, tum Tumbuka, tr Turkish, tk Turkmen, tw Twi, ug Uighur, uk Ukrainian, ur Urdu, uz Uzbek, vi Vietnamese, cy Welsh, wo Wolof, xh Xhosa, yi Yiddish, yo Yoruba, zu Zulu",
    URL = "https://translate.googleapis.com/translate_a/single?client=gtx&sl=auto&tl=" & LangCode & "&dt=t&q=" & SrcTxt,
    Source = Json.Document( Web.Contents( URL ) ),
    Translation = Source{0}{0}{0}
in
    Translation

@JGordon11 - you know I have a lot of respect for your M Code chops - I've often given you likes on your contributions. However the list of countries is currently 134 entries, dynamically retrieved from the web, and I understand how to set up a list of options manually, but want to be able to just use the list of countries from another query, and preferably have it appear as a drop down list in the Add Column - Invoke Custom Function dialog like a Data Validation List:

The two column table of Countries and Codes is LanguageCodes, and the list of countries derived from that list is called Languages which is one line of M Code:

Source = List.Distinct(LanguageCodes[Language]).

If it can't be done, so be it, but thanks regardless.